Laravel：如何在 Laravel 查询构建器中使用派生表/子查询

Laravel: how to use derived tables / subqueries in the laravel query builder

提问by Luuk Van Dongen

Edit:

编辑：

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 中使用派生表/子查询相关的问题

Original Question:

原问题：

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.

最近我有点困在 Laravel 查询构建器上。它有一些非常好的功能，但我觉得它不是为更复杂的数据库操作而构建的。

This is the query I'm trying to build:

这是我正在尝试构建的查询：

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: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.

查询说明：我正在 Laravel 中为我的产品目录构建分面搜索。根据用户提供的过滤器/属性缩小产品范围。为了更好的用户体验，我想显示每个过滤器剩余的产品数量，这就是上面的查询所做的：计算某个属性的所有产品，其中 product_id 位于产品 ID 数组中。

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

Please help me because I don't like to use a DB::raw() or DB::select() statement. That wouldn't feel "Laravelish" or "Eloquent".

请帮助我，因为我不喜欢使用 DB::raw() 或 DB::select() 语句。那不会让人觉得“Laravelish”或“Eloquent”。

回答by lukasgeiter

Your first try looks pretty close. Try this:

你的第一次尝试看起来很接近。尝试这个：

I removed the long namespace reference and suggest you add a usestatement to make your code more readable

我删除了长命名空间引用并建议您添加一条use语句以使您的代码更具可读性

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