我有两张桌子,
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实例集合,其中related
property.parent_id
为空,或者如果sale_services
表中有一些记录共享同一个parent_id
到properties
表,由该字段区分,并按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/