我有两张桌子,

properties
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 | null      |
|  2 | 1         |
|  3 | null      |
|  4 | 3         |
|  5 | 3         |
|  6 | null      |
+----+-----------+


sale_services
+----+-------------+------+
| id | property_id | rank |
+----+-------------+------+
|  1 |           2 |    5 |
|  2 |           4 |    4 |
|  3 |           5 |    6 |
|  4 |           6 |    7 |
+----+-------------+------+

以及相应的雄辩模型(sale service和Property)通过关系(sale-service.Property=Property.id)相互链接。属性可以链接到同一表中的另一个属性。
我需要获取一个SaleService实例集合,其中relatedproperty.parent_id为空,或者如果sale_services表中有一些记录共享同一个parent_idproperties表,由该字段区分,并按rank排序。
结果应该是
+----+-------------+------+
| id | property_id | rank |
+----+-------------+------+
|  1 |           2 |    5 |
|  3 |           5 |    6 |
|  4 |           6 |    7 |
+----+-------------+------+

-sale_services表中除(sale_service.id=2)之外的所有项目,因为它的属性与项目(sale_service.id=3)和项目(sale_service.id=3)共享parent_id,所以其值最高
我想出了SQL代码来得到想要的结果,
 SELECT *
    FROM
      (SELECT DISTINCT ON (properties.parent_id) *
       FROM "sale_services"
       INNER JOIN "properties" ON "sale_services"."property_id" = "properties"."id"
       WHERE ("properties"."parent_id") IS NOT NULL
       ORDER BY "properties"."parent_id", "sale_services"."rank" DESC) AS sub
    UNION
    SELECT *
    FROM "sale_services"
    INNER JOIN "properties" ON "sale_services"."property_id" = "properties"."id"
    WHERE ("properties"."parent_id") IS NULL

但我无法与能言善辩的建设者达成同样的目标。
我试过这样的东西
$queryWithParent = SaleService::query()
    ->select(\DB::raw('DISTINCT ON (properties.parent_id) *'))
    ->whereNotNull('properties.parent_id')
    ->join('properties', 'sale_services.property_id', '=', 'properties.id')
    ->orderBy('parent_id')
    ->orderBy('sale_services.index_range', 'desc');

$queryWithoutParent = SaleService::query()
    ->join('properties', 'sale_services.property_id', '=', 'properties.id')
    ->whereNull('properties.parent_id');

$query = $queryWithParent->union($queryWithoutParent);

但有个错误
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "union" LINE 1: ...perties.type <> 'hotel') order by "parent_id" asc union sele... ^ (SQL: select DISTINCT ON (properties.parent_id) * from "sale_services" inner join "properties" on "sale_services"."property_id" = "properties"."id" where ("properties"."parent_id") is not null and ("sale_services"."deleted_at") is null and "published" = 1 and exists (select 1 from "properties" where properties.id = sale_services.property_id AND properties.type <> 'hotel') order by "parent_id" asc union select * from "sale_services" inner join "properties" on "sale_services"."property_id" = "properties"."id" where ("properties"."parent_id") is null and ("sale_services"."deleted_at") is null and "published" = 1 and exists (select 1 from "properties" where properties.id = sale_services.property_id AND properties.type <> 'hotel') order by "index_range" desc limit 12 offset 0)

如果我从第一个查询($queryWithParent)中删除排序,它似乎可以工作,但在不同的查询中选择了随机项。
有没有其他方法可以达到同样的效果,或者我做错了什么?

最佳答案

“Distinct”的laravel查询创建者是Distinct(),如下所示:

$queryWithParent = SaleService::query()
->distinct('properties.parent_id')
->whereNotNull('properties.parent_id')
->join('properties', 'sale_services.property_id', '=', 'properties.id')
->orderBy('parent_id')
->orderBy('sale_services.index_range', 'desc');

这样行吗?

关于laravel - 在Laravel Eloquent查询构建器中将UNION与DISTINCT配合使用,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/54986299/

10-09 18:12
查看更多