Laravel:如何在Laravel查询构建器中使用派生表/子查询 [英] Laravel: how to use derived tables / subqueries in the laravel query builder
问题描述
尽管此问题最初是针对我在下面描述的查询所特有的,但我得到的答案几乎适用于与在Laravel中使用派生表/子查询有关的所有问题
Though this question originally was specific for the query I'm describing underneath, the answer I got applies to almost all questions related to using derived tables / subqueries in Laravel
原始问题:
最近,我在laravel查询构建器上有些困惑.它具有一些非常好的功能,但是我觉得它并不是为更复杂的数据库操作而构建的.
Lately I'm a bit stuck on the laravel query builder. It has some really nice features but I feel like it just isn't build for more complex database operations.
这是我要构建的查询:
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'
查询说明: 我正在laravel中对我的产品目录进行多面搜索.根据用户提供的过滤器/属性来缩小产品范围.为了获得更好的用户体验,我想显示每个过滤器剩余的产品数量,这就是上面的查询:对某个属性的所有产品进行计数,其中product_id位于产品ID的数组中.
Explanation of the query: I'm building a faceted search for my product catalog in laravel. Products are narrowed down based on the filters/attributes users provide. For better user experience I want to show the amount of products left for each filter, that's what the query above does: counting all the products for a certain attribute WHERE the product_id is IN an array of product id's.
我的尝试:
$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()语句.那不会感觉到粗鲁"或雄辩".
Please help me because I don't like to use a DB::raw() or DB::select() statement. That wouldn't feel "Laravelish" or "Eloquent".
推荐答案
您的第一次尝试看上去非常接近.试试这个:
Your first try looks pretty close. Try this:
我删除了较长的名称空间引用,建议您添加use
语句以使您的代码更具可读性
I removed the long namespace reference and suggest you add a use
statement to make your code more readable
$productIds = [ 1, 2, 3, 4, 5 ];
$subQuery = DB::table('attribute_options AS counted')->selectRaw('counted.id, counted.attribute_id, counted.value, count(counted.attribute_id) AS product_count')
->whereIn('counted.product_id', $productIds)
->groupBy('counted.attribute_option_id')
$query = AttributeOption::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 '))
->mergeBindings($subQuery->getQuery())
->rightJoin('attribute_options AS uncounted', 'counted.id', '=', 'uncounted.id')
->groupBy('attribute_option_id')
->get();
这篇关于Laravel:如何在Laravel查询构建器中使用派生表/子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!