Edit:
虽然这个问题最初是针对我下面描述的查询的,但我得到的答案几乎适用于所有与在Laravel中使用派生表/子查询相关的问题
Original Question:
最近我对laravel查询生成器有点着迷.它有一些非常好的特性,但我觉得它不是为更复杂的数据库操作而构建的.
这就是我试图构建的查询:
select
'IFNULL(counted.product_count, 0) AS product_count',
'uncounted.value',
'uncounted.attribute_id',
'uncounted.attribute_option_id'
from (
select
'counted.id',
'counted.attribute_id',
'counted.value',
'count(counted.attribute_id) AS product_count'
from `attribute_options` as `counted`
where `counted.product_id` in (?, ?, ?, ?, ?)
group by `counted.attribute_option_id`
) as 'counted'
right join 'attribute_options' as 'uncounted'
on 'counted.id' = 'uncounted.id'
group by 'attribute_option_id'
Explanation of the query:
我的try :
$productIds = [ 1, 2, 3, 4, 5 ];
$subQuery = \DB::table('attribute_options')->selectRaw('counted.id, counted.attribute_id, counted.value, count(counted.attribute_id) AS product_count')
->from('attribute_options AS counted')
->whereIn('counted.product_id', $productIds)
->groupBy('counted.attribute_option_id')
->mergeBindings($subQuery);
$query = Model::selectRaw('IFNULL(counted.product_count, 0) AS product_count, uncounted.value, uncounted.attribute_id, uncounted.attribute_option_id')
->from(\DB::raw(' ( ' . $subQuery->toSql() . ' ) AS counted '))
->rightJoin('attribute_options AS uncounted', 'counted.id', '=', 'uncounted.id')
->groupBy('attribute_option_id')
->get();
请帮助我,因为我不喜欢使用DB::raw()或DB::select()语句.这不会让人觉得"拉拉维英语"或"Eloquent ".