如何使用laravel查询生成器编写此查询? [英] How can I write this query using the laravel query builder?

问题描述

我在laravel 5.1上使用postgres作为数据库.如果有帮助，可以帮助我理解我的问题: https://www.db -fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/4

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

写大部分的内容是很直接的.我遇到的问题是with properties as (...)块.我尝试过类似的事情:

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

但是我注意到DB::statement()

查询生成器中是否缺少一种方法?如何在查询中添加WITH properties AS (...)语句?

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

我认为还应该指出的是，我正在尝试实现存储库模式，而不能只将DB::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.

推荐答案

我为常见的表表达式创建了一个程序包:

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([...])
    ->[...]

这篇关于如何使用laravel查询生成器编写此查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！