Eloquent Query - COUNT and SUM on Multiple Columns
I am trying to use knowledge from this question already answered question
In the user_attitudes pivot table I have two columns:
-
importance (users declare their interest (0.3))
-
(mainly for continuing and downvoting, values: "-1", "0" and "1")
What I have now:
I can print a list of Entity sorted by value generated by selectRaw .
What I need:
I want to print different all four values described below, no matter which one is used to sort
I want to create multiple sort switches with AJAX so that users can change the sort.
For each, I have to display up to four scores : Total grade score:
SUM(user_attitudes.importance) AS importance
To show how many users are watching an Entity:
COUNT(user_attitudes.importance) AS observing
Total number of upvotes and downvotes:
SUM(user_attitudes.attitude) AS karma
The number of users who have decreased or increased the number of objects:
COUNT(user_attitudes.importance) AS
How can I expand the query below? Where in this request can I request additional numbers?
$rank_entities = Entity::leftJoin('user_attitudes', function($q){
$q->on('entity_id', '=', 'entities.id');
$q->where('item_type', '=', 'entity');
})
->selectRaw('entities.*, SUM(user_attitudes.importance) AS importance')
->groupBy('entities.id')
->orderBy('importance', 'desc')
->take(6)
->get();
I tried to add more
->selectRaw('entities.*, SUM(user_attitudes.attitude) AS karma')
but it seems that only one selectRaw can create a variable for printing Any ideas? THX.
source to share
Just add them to the current one selectRaw
? Here I am using an array to make it cleaner:
$selects = array(
'entities.*',
'SUM(user_attitudes.importance) AS importance',
'COUNT(user_attitudes.importance) AS observing',
'SUM(user_attitudes.attitude) AS karma'
);
// query ...
->selectRaw(implode(',', $selects))
source to share