对于带有多个动态过滤器的子查询,我需要使用laravel查询构建器。
代码是这样的:
$totalRechargesPerUserQuery = DB::table('transactions as T2')
->select([DB::raw('COUNT(T2.user_id)')])
->join('users as U2','U2.id','=','T2.user_id')
->whereRaw('U2.id = U1.id')
->groupBy('T2.user_id');
$this->applyPromotionFilter($totalRechargesPerUserQuery,$selectedWeeks,'T2');
$query = DB::table('transactions as T1')
->select([
'U1.name','U1.email','U1.role','T1.amount',DB::raw('COUNT(T1.user_id) as count'),DB::raw("({$totalRechargesPerUserQuery->toSql()}) as total")
])
->join('users as U1','U1.id','T1.user_id');
$this->applyPromotionFilter($query,'T1');
$query->addBinding($totalRechargesPerUserQuery->getBindings());
过滤功能:
private function applyPromotionFilter(\Illuminate\Database\Query\Builder $query,array $selectedWeeks,$tableAlias = 'transactions'): void
{
$query->where(function ($query) use ($selectedWeeks,$tableAlias) {
foreach ($selectedWeeks as $selectedWeek) {
$query->orWhere(function ($query) use ($selectedWeek,$tableAlias) {
$query->whereBetween("$tableAlias.created_at",[$selectedWeek['start'],$selectedWeek['end']]);
});
}
});
}
到目前为止,我可以通过将我的位置更改为whereRaw来使其工作:
$query->whereRaw("T1.type = '" . Transaction::TYPE_RECHARGE . "'")
但是我需要应用用户在applyPromotionFilter函数中选择的过滤器,并且当我使用where或Where等时。执行$ query-> get()时生成的查询包含不适当的参数。 / p>
例如:
and ((`T1`.`created_at` between 'CLIENTE' and '2019-12-30 00:00:00') or (`T1`.`created_at` between '2020-01-05 23:59:59' and '2020-01-06 00:00:00')) and `U1`.`role` = '2020-01-12 23:59:59'
。 在该生成的查询片段中,日期值由用户角色更改。
由于必须应用动态过滤器,因此使用查询生成器而不是原始SQL进行操作将非常有帮助。
任何帮助将不胜感激! 预先感谢。