doutan5798
2018-07-07 19:37
采纳率: 100%
浏览 1.1k
已采纳

Laravel在单个子句中使用多个where和sum

在我的数据库中有Instagram_Actions_History表,其中包含action_type列,在列中,我的结果与数据(如1、2或3)不同。

我试图获取这些表数据的关联关系,并对存储在列中的值进行求和,例如:

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

顺便说一句,这个代码是不正确的,因为我想得到所有的历史,里面有多个和:

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

例如(比索代码):

$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();

更新

$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();

报错

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

如何实现此解决方案?

更新:

InstagramAccount类:

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 InstagramActionsHistory extends Model
{
    protected $guarded=['id'];

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

User 类:

class User extends Authenticatable
{
    use Notifiable;

    ...

    public function instagramPages()
    {
        return $this->hasMany(InstagramAccount::class);
    }
}
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • dtbam62840 2018-07-21 11:53
    最佳回答

    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?

    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题