title: How can I write this query using the laravel query builder?
body: <p>I'm on laravel 5.1 using postgres as the DB. I have a fiddle here in case it helps understand my issue: <a href="https://www.db-fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/4" rel="nofollow noreferrer">https://www.db-fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/4</a> </p>
  
  <pre><code>with properties as (
      select 
          properties.*, 
          json_agg(property_fields.*) as property_fields 
      from 
          properties 
      left join fields as property_fields 
          on property_fields.parent = 'property' and property_fields.parent_id = properties.id 
      group by properties.id, properties.deal_id, properties.address
  )
  
  select 
      deals.*, 
      json_agg(properties.*) as deal_properties, 
      json_agg(deal_fields.*) as deal_fields
  from deals
  
  left join properties on deals.id = properties.deal_id
  left join fields deal_fields on deal_fields.parent = 'deal' and deal_fields.parent_id = deals.id
  
  group by deals.id, deals.name
  order by deals.id
  </code></pre>
  
  <p>Writing most of this is fairly straight forward. The problem I'm having is with the <code>with properties as (...)</code> block. I've tried something like:</p>
  
  <pre><code>DB::statement('WITH properties AS ( ... )')
      -&gt;table('deals')
      -&gt;select(' deals.*, json_agg(properties.*) as deal_properties,  ')
      ...
      -&gt;get();
  </code></pre>
  
  <p>But I notice the execution stop after <code>DB::statement()</code></p>
  
  <p>Is there a method in the Query Builder that I'm missing? How can I prefix my query with the <code>WITH properties AS (...)</code> statement?</p>
  
  <p>I think it should also be noted that I'm trying to implement a Repository Pattern and I can't just wrap a <code>DB::statement()</code> around the whole query.</p>