Laravel use multiple where and sum in single clause

Question

in my database i have instagram_actions_histories table which into that i have action_type column, in the column i have unlike data such as 1 or 2 or 3

i'm trying to get this table data in relation ship and summing this values which stored in column, for example

$userAddedPagesList = auth()->user()->instagramPages()->with([
        'history' => function ($query)  {
            $query->select(['action_type as count'])->whereActionType(1)->sum('action_type');
        }
    ]
)->get();

btw, this code is not correct, in that i want to get all history with multiple sum inside that

whereActionType(1)->sum('action_type')
whereActionType(2)->sum('action_type')
whereActionType(3)->sum('action_type')

for example (pesudo code):

$userAddedPagesList = auth()->user()->instagramPages()->with([
        'history' => function ($query)  {
            $query->select(['action_type as like'])->whereActionType(1)->sum('action_type');
            $query->select(['action_type as follow'])->whereActionType(2)->sum('action_type');
            $query->select(['action_type as unfollow'])->whereActionType(3)->sum('action_type');
        }
    ]
)->get();

UPDATE:

$userAddedPagesList = auth()->user()->instagramPages()->with([
        'history' => function ($query) {
            $query->select('*')
                ->selectSub(function ($query) {
                    return $query->selectRaw('SUM(action_type)')
                        ->where('action_type', '=', '1');
                }, 'like')
                ->selectSub(function ($query) {
                    return $query->selectRaw('SUM(action_type)')
                        ->where('action_type', '=', '2');
                }, 'follow')
                ->selectSub(function ($query) {
                    return $query->selectRaw('SUM(action_type)')
                        ->where('action_type', '=', '3');
                }, 'followBack');
        }
    ]
)->get();

ERROR:

Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select *, (select SUM(action_type) where `action_type` = 1) as `like`, (select SUM(action_type) where `action_type` = 2) as `follow`, (select SUM(action_type) where `action_type` = 3) as `followBack` from `instagram_actions_histories` where `instagram_actions_histories`.`account_id` in (1, 2, 3))

how can i implementing this solution?

UPDATE:

InstagramAccount class:

class InstagramAccount extends Model
{
    ...

    public function schedule()
    {
        return $this->hasOne(ScheduleInstagramAccounts::class, 'account_id');
    }

    public function history()
    {
        return $this->hasMany(InstagramActionsHistory::class, 'account_id');
    }
}

InstagramActionsHistory class:

class InstagramActionsHistory extends Model
{
    protected $guarded=['id'];

    public function page(){
        return $this->belongsTo(InstagramAccount::class);
    }
}

User class:

class User extends Authenticatable
{
    use Notifiable;

    ...

    public function instagramPages()
    {
        return $this->hasMany(InstagramAccount::class);
    }
}

is the raw query you need something like : select action_type, count(*) from your table group by action_type ? — koalaok, Jul 07 '18 at 21:46
@koalaok i need to summing action_type when thats 1,summing action_type when thats 2 and then summing action_type when thats 3 — DolDurma, Jul 08 '18 at 04:55
Maybe I miss some additional info....but this is getting strange to me. Looks like you want to sum all records having same values inside.... Normally it's a count..... Or if you really need the sum which is count()*value then use SUM(): SELECT action_type, SUM(action_type) FROM instagram_actions_histories GROUP BY action_type — koalaok, Jul 08 '18 at 09:59
Laravel is right you are mixing aggregation subquery `selectSub()` with normal scaler result `select('*')`.Did you wanted a per user result alongside respective SUMs? — Vinay, Jul 08 '18 at 16:29
@Viney if i understand your mean, for per table with inside relation ship with more sum for per `action_type` — DolDurma, Jul 08 '18 at 16:36

score 6 · Accepted Answer · edited Jul 22 '18 at 07:55

Another approach to get conditional sum for your different types of action, you can define a hasOne() relation in your InstagramAccount model like

public function history_sum()
{
    return $this->hasOne(InstagramActionsHistory::class, 'account_id')
        ->select('account_id',
            DB::raw('sum(case when action_type = 1 then 0 END) as `like`'),
            DB::raw('sum(case when action_type = 2 then 0 END) as `follow`'),
            DB::raw('sum(case when action_type = 3 then 0 END) as `followBack`')
        )->groupBy('account_id');
}

Then you can eager load the related data as

$userAddedPagesList = auth()->user()->instagramPages()->with('history_sum')->get();

Going through with this approach will execute only one extra query to get 3 different sum results based on your criteria

select `account_id`,
sum(case when action_type = 1 then action_type else 0 END) as `like`, 
sum(case when action_type = 2 then action_type else 0 END) as `follow`, 
sum(case when action_type = 3 then action_type else 0 END) as `followBack` 
from `instagram_actions_histories` 
where `instagram_actions_histories`.`account_id` in (?, ?, ?) 
group by `account_id`

While as compare to other approach (which is a valid answer also) using withCount will add 3 dependent correlated sub queries for each action type which may result as a performance overhead, generated query will look something like below

select `instagram_account`.*, 
(select sum(action_type) from `instagram_actions_histories` where `instagram_account`.`id` = `instagram_actions_histories`.`account_id` and `action_type` = ?) as `like`, 
(select sum(action_type) from `instagram_actions_histories` where `instagram_account`.`id` = `instagram_actions_histories`.`account_id` and `action_type` = ?) as `follow`,
(select sum(action_type) from `instagram_actions_histories` where `instagram_account`.`id` = `instagram_actions_histories`.`account_id` and `action_type` = ?) as `followBack`
from `instagram_account` 
where `instagram_account`.`user_id` = ? 
and `instagram_account`.`user_id` is not null

To check the generated queries refer to Laravel 5.3 - How to log all queries on a page?

@M Khalid Junaid your post was cause of i learn new method, thanks — DolDurma, Jul 21 '18 at 12:17

score 0 · Answer 2 · answered Jul 08 '18 at 16:36

selectSub() creates subquery you have used aggregation (SUM) in it but select() is a scaler returns a scaler result; mixing aggregate with scaler query is not allowed at same level unless you use grouping.If you wanted to return a per user result ,try adding groupBy,Here I assume id is the primary key on userstable

$userAddedPagesList = auth()->user()->instagramPages()->with([
        'history' => function ($query) {
            $query->select('*')
                ->selectSub(function ($query) {
                    return $query->selectRaw('SUM(action_type)')
                        ->where('action_type', '=', '1');
                }, 'like')
                ->selectSub(function ($query) {
                    return $query->selectRaw('SUM(action_type)')
                        ->where('action_type', '=', '2');
                }, 'follow')
                ->selectSub(function ($query) {
                    return $query->selectRaw('SUM(action_type)')
                        ->where('action_type', '=', '3');
                }, 'followBack');

            $query->groupBy('users.id');   //<-- add this
        }
    ]
)->get();

relation ship table is `instagram_actions_histories` not `user`, in fact i'm trying to summing `action_type` in `instagram_actions_histories` and when i change your group by to `$query->groupBy('instagram_actions_histories.id')` i get this error: `SQLSTATE[42000]: Syntax error or access violation: 1055 'instacheeta.instagram_actions_histories.account_id' isn't in GROUP BY` — DolDurma, Jul 08 '18 at 16:53
ohh.. I would advise posting all the table structures involved. — Vinay, Jul 08 '18 at 16:56

Laravel use multiple where and sum in single clause

2 Answers2

Linked