Eloquent query - COUNT and SUM on multiple columns

Question

I am trying to use knowledge from this question question already answered

In my pivot table user_attitudes I have two columns:

• importance (the users are declaring their interest (0,3))

• attitude (basically this one is for upvoting and downvoting, values are '-1', '0' and '1')

What I have now:

I can print a list of Entity ordered by the value created by selectRaw.

What I need:

I wish to print a different all four values described below, unregardedly which one is used for sorting

I wish to create several sorting switches with AJAX, so that users can change sorting.

For every I need to output up to four counts: Total importance score:

SUM(user_attitudes.importance) AS importance

To show how many users are observing the Entity:

COUNT(user_attitudes.importance) AS observing

Total count of upvotes and downvotes:

SUM(user_attitudes.attitude) AS karma

Number of users who down- or upvoted a iven Entity:

COUNT(user_attitudes.importance) AS 

How I can expand the below query? Where in this query I can ask for the above extra 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 another

->selectRaw('entities.*, SUM(user_attitudes.attitude) AS karma')

but it seems only one selectRaw can create a printable variable Any ideas? thx.

Answer 1

Just add them to the current selectRaw? Here I'm using an array to make it a bit 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))