How can I write this query using the laravel query

问题:

I'm on laravel 5.1 using postgres as the DB. I have a fiddle here in case it helps understand my issue: https://www.db-fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/4

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.

回答1:

I've created a package for common table expressions: https://github.com/staudenmeir/laravel-cte

$query = 'select properties.*, [...]';

DB::table('deals')
    ->withExpression('properties', $query)
    ->leftJoin([...])
    ->[...]

You can also provide a query builder instance:

$query = DB::table('properties')
    ->select('properties.*', [...])
    ->leftJoin([...])
    ->[...]

DB::table('deals')
    ->withExpression('properties', $query)
    ->leftJoin([...])
    ->[...]

回答2:

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

https://laravel.com/docs/5.0/queries

回答3:

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

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

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