Блог

Я задавался вопросом, возможно ли написать это как один запрос:

 SELECT * FROM
(SELECT  "markets".* 
  FROM "markets" 
  INNER JOIN "positions" ON "positions"."id" = "markets"."position_id" 
  INNER JOIN "players" ON "players"."id" = "positions"."player_id" 
  INNER JOIN "other_players" ON "other_players"."id" = "players"."other_player_id" 
  WHERE (markets.updated_at > '2021-01-10 11:50:14.136015') 
  AND "markets"."on_feed" = true
  AND "markets"."deleted_at" IS NULL

  UNION

  SELECT  "markets".* 
  FROM "markets" 
  WHERE (markets.updated_at > '2021-01-10 11:50:14.136015') 
  AND "markets"."on_feed" = true 
  AND "markets"."deleted_at" IS NOT NULL) results
ORDER BY results.updated_at DESC
 

В этих двух запросах много совпадений. В первом случае мы хотим, чтобы на всех рынках существовали такие ассоциации. Для 2-го запроса нам на самом деле все равно, есть ли ассоциации или нет.

   SELECT * FROM "markets" 
  LEFT JOIN "positions" ON "positions"."id" = "markets"."position_id" 
  LEFT JOIN "players" ON "players"."id" = "positions"."player_id" 
  LEFT JOIN "other_players" ON "other_players"."id" = "players"."other_player_id" 
  WHERE markets.updated_at > '2021-01-10 11:50:14.136015'
  AND "markets"."on_feed" = true
  AND ("markets"."deleted_at" IS NOT NULL 
  OR "positions"."id" IS NOT NULL AND "players"."id" IS NOT NULL 
      AND "other_players"."id" IS NOT NULL)
  ORDER BY markets.updated_at DESC
 

Используйте EXISTS вместо этого:

 SELECT m.* 
FROM "markets"  m
WHERE m.updated_at > '2021-01-10 11:50:14.136015' AND
      markets."on_feed" = true AND
      (m."deleted_at" IS NOT NULL OR
       EXISTS (SELECT 1
               FROM "positions" p JOIN 
                    "players" pl
                    ON pl."id" = p."player_id" JOIN
                    "other_players" op
                    ON op."id" = p."other_player_id" 
               WHERE p."id" = m."position_id" 
              )
      )
ORDER BY m.updated_at DESC