更新时间:2023-02-04 23:32:19
另一种为不同类型的动作获取条件总和的方法,你可以在你的 中定义一个
模型如 hasOne()
关系InstagramAccount
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();
采用这种方法将仅执行一个额外的查询,以根据您的条件获得 3 个不同的总和结果
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`
虽然与使用 withCount
的其他方法(这也是一个有效的答案)相比,将为每个操作类型添加 3 个相关的相关子查询,这可能会导致性能开销,生成的查询将看起来有些东西如下图
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
要检查生成的查询,请参阅Laravel 5.3 - 如何记录页面上的所有查询?
To check the generated queries refer to Laravel 5.3 - How to log all queries on a page?