Question 280650 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL how you would rewrite this query without crossjoin?

I have heard I should avoid cross join so my question is how you would rewrite this query without cross join ?

    SELECT p.*, po.*, pt.*
    FROM product p
    CROSS JOIN LATERAL (
        SELECT json_agg(name) AS product_options
        FROM product_options po
        WHERE po.p_id = p.id
    ) po
    CROSS JOIN LATERAL (
        SELECT json_agg(json_build_object('tag', tag)) AS tags
        FROM product_tags pt
        WHERE pt.p_id = p.id
    ) pt
    WHERE p.id = 1

Here is a fiddle example

http://sqlfiddle.com/#!17/be10d/35

the important thing is that I need an array of objects for product options and tags but you can see this on the fiddle the example so I need exact this row with these fields but without cross join

Tags:

2

Answers


  1. 0

    Here is an equivalent of your query using LEFT JOIN :

    SELECT p.*, po.product_options, pt.tags
FROM product p
LEFT JOIN  (
    SELECT p_id, json_agg(name) AS product_options
    FROM product_options po
    GROUP BY p_id
) po ON po.p_id = p.id
LEFT JOIN  (
    SELECT p_id, json_agg(json_build_object('tag', tag)) AS tags
    FROM product_tags pt
    GROUP BY p_id
) pt ON pt.p_id = p.id
WHERE p.id = 1;

    Demo here

    Login or Signup to reply.
  2. 0

    If you’re dealing with someone allergic to joins, you don’t need to join at all: demo

    SELECT p.*,(SELECT json_agg(name) AS product_options
            FROM product_options po
            WHERE po.p_id = p.id), 
           (SELECT json_agg(json_build_object('tag', tag)) AS tags
            FROM product_tags pt
            WHERE pt.p_id = p.id)
FROM product p
WHERE p.id = 1

    These are scalar subqueries.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search