Thursday 19 July 2018

Laravel use multiple where and sum in single clause

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);
    }
}



from Laravel use multiple where and sum in single clause

No comments:

Post a Comment