Question:

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">https://www.db-fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/4</a>

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

Writing most of this is fairly straight forward. The problem I'm having is with the with properties as (...) block. I've tried something like:

DB::statement('WITH properties AS ( ... )') ->table('deals') ->select(' deals.*, json_agg(properties.*) as deal_properties, ') ... ->get();

But I notice the execution stop after DB::statement()

Is there a method in the Query Builder that I'm missing? How can I prefix my query with the WITH properties AS (...) statement?

I think it should also be noted that I'm trying to implement a Repository Pattern and I can't just wrap a DB::statement() around the whole query.

if you want some data fetch from a table you can use this type of code

$user = DB::table('table name')->where('name', 'John')->where('height','!>',"7")->select('table fields which you want to fetch')->get();

Or try using the larevel Eloquent ORM which will make things easier with the database.

for more example or reference

<a href="https://laravel.com/docs/5.0/queries" rel="nofollow">https://laravel.com/docs/5.0/queries</a>

I think you can actually do this with eager loading, assuming that the relationships are set up correctly. (More reading here: <a href="https://laravel.com/docs/5.4/eloquent-relationships#constraining-eager-loads" rel="nofollow">https://laravel.com/docs/5.4/eloquent-relationships#constraining-eager-loads</a>)

So I think you'd be able to add something like

->with(['properties' => function ($query) { $query->select('field') ->leftJoin('join statement') ->groupBy('field1', 'field2'); }])