sql - 消除多个条件并减少到一个(没有 OR 语句)

Question

SELECT  "listings".*, trucks.*, cars.*, trucks.id as truck_id, cars.id as cars_id, listings.id as listing_id
FROM "listings" 
LEFT OUTER JOIN "trucks" ON "trucks"."id" = "listings"."listable_id" AND "listings"."listable_type" = 'Truck' 
LEFT OUTER JOIN "cars" ON "cars"."id" = "listings"."listable_id" AND "listings"."listable_type" = 'Car' 
WHERE (cars.user_id = 119 or trucks.user_id = 119) LIMIT $1  [["LIMIT", 11]]

有没有办法修改上面的 sql 做同样的事情，但只是调用 user_id = 119 而不是使用 or 子句？

我希望能够做类似...

    SELECT  "listings".*, trucks.*, cars.*, trucks.id as truck_id, cars.id as cars_id, listings.id as listing_id
FROM "listings" 
LEFT OUTER JOIN "trucks" ON "trucks"."id" = "listings"."listable_id" AND "listings"."listable_type" = 'Truck' 
LEFT OUTER JOIN "cars" ON "cars"."id" = "listings"."listable_id" AND "listings"."listable_type" = 'Car' 
WHERE (user_id_alias = 119) LIMIT $1  [["LIMIT", 11]]

或者类似的东西

WHERE (alias.user_id = 119) LIMIT $1  [["LIMIT", 11]]

简化代码的目标是我编写了一个只关心 user_id 的搜索查询对象，并且由于卡车和汽车是互斥的，因为您永远不可能同时拥有卡车和汽车的列表，所以我需要获得所有特定用户的列表。

Answer 1

我建议使用 EXISTS 编写此查询:

SELECT l.*
FROM "listings" l
WHERE EXISTS (SELECT 1
              FROM trucks t
              WHERE t.id = l.listable_id and l.listable_type = 'Truck' AND
                    t.user_id = 119
             ) OR
      EXISTS (SELECT 1
              FROM cars c
              WHERE c.id = l.listable_id and l.listable_type = 'Car' AND
                    c.user_id = 119
             )
LIMIT 1;

这可以利用 trucks(id, user_id) 和 cars(id, user_id) 上的索引。我希望它有很好的性能(尽管 left join 版本可能也不错)。

我不确定这是您想要的简化。您可以通过以下方式限制 119 一个位置:

SELECT l.*
FROM "listings" l CROSS JOIN
     (VALUES (119)) v(user_id)
WHERE EXISTS (SELECT 1
              FROM trucks t
              WHERE t.id = l.listable_id and l.listable_type = 'Truck' AND
                    t.user_id = v.user_id
             ) OR
      EXISTS (SELECT 1
              FROM cars c
              WHERE c.id = l.listable_id and c.listable_type = 'Car' AND
                    c.user_id = v.user_id
             )
LIMIT 1;

我认为这不会影响性能。