title: Laravel: how to use derived tables / subqueries in the laravel query builder
body: <p><strong>Edit:</strong></p>
  
  <p>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 </p>
  
  <p><strong>Original Question:</strong></p>
  
  <p>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. </p>
  
  <p>This is the query I'm trying to build:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p><strong>Explanation of the query:</strong>
  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.</p>
  
  <p>My try:</p>
  
  <pre><code>    $productIds = [ 1, 2, 3, 4, 5 ];
  
      $subQuery = \DB::table('attribute_options')-&gt;selectRaw('counted.id, counted.attribute_id, counted.value, count(counted.attribute_id) AS product_count')
                      -&gt;from('attribute_options AS counted')
                      -&gt;whereIn('counted.product_id', $productIds)
                      -&gt;groupBy('counted.attribute_option_id')
                      -&gt;mergeBindings($subQuery);
  
      $query = Model::selectRaw('IFNULL(counted.product_count, 0) AS product_count, uncounted.value, uncounted.attribute_id, uncounted.attribute_option_id')
                      -&gt;from(\DB::raw(' ( ' . $subQuery-&gt;toSql() . ' ) AS counted '))
                      -&gt;rightJoin('attribute_options AS uncounted', 'counted.id', '=', 'uncounted.id')
                      -&gt;groupBy('attribute_option_id')
                      -&gt;get();
  </code></pre>
  
  <p>Please help me because I don't like to use a DB::raw() or DB::select() statement. That wouldn't feel "Laravelish" or "Eloquent".</p>