I have a table "store" and a table "product". Each store has multiple products but a product only has one store ( one to many relationship ). The tables might look something like:

store: id, name
product: id, name, store_id

when querying the store and its products using supabase I simply do:

    .from("store")
    .select(
        id
        name,
        product(name)
    )

which would return

    id: "some_id",
    name: "some_name",
    products: [
        {...},
        {...}
     ] 
}

or something along those lines depending on what data I want. However, I need to run this query in pure SQL and I can't seem to figure it out.

I tried to JOIN the tables together but it leads to a lot of duplicated data since the store's data is in all the rows

CodePudding user response：

This is not an Answer.

In MySql you can use query like that :

SELECT 
  CONCAT("{id:'", s.id, "', Name:'" , s.`Name`, "', products: [", 
  GROUP_CONCAT(CONCAT("{id:'",p.id,"', Name:'", p.`Name`, "'} "))
  ,"]}")
FROM
  store AS s, product AS p
WHERE
  (s.id = p.store_id) AND (s.id=1)
GROUP BY
  s.Id

If you inserted data like :

INSERT INTO store VALUES 
(1, 'Store1'), 
(2, 'Store2');

INSERT INTO product VALUES 
(1, 'P1', 1), 
(2, 'P2', 1), 
(3, 'P3', 1), 
(4, 'P4', 2);

you will get :

{id:'1', Name:'Store1', products: [{id:'1', Name:'P1'},{id:'2', Name:'P2'},{id:'3', Name:'P3'}]}

Notes:

Data filtered for store 1. You can remove AND (s.id=1) or modify it.
Tested on MySql4 (you included MySql tag).

In PostgreSQL you use ARRAY_TO_STRING() and ARRAY_AGG() functions instead of GROUP_CONCAT() in MySql. I don't have PostgreSQL to test.

SELECT 
  CONCAT(
    "{id:'", s.id, "', Name:'" , s.`Name`, "', products: [", 
    ARRAY_TO_STRING(
      ARRAY_AGG("{id:'",p.id,"', Name:'", p.`Name`, "'} ")
    )
    ,"]}"
  )
FROM
  store AS s, product AS p
WHERE
  (s.id = p.store_id) AND (s.id=1)
GROUP BY
  s.Id

Prior to version 8.4, you would have had to define the function ARRAY_AGG before using it:

CREATE AGGREGATE ARRAY_AGG (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

I found this on this Site

CodePudding user response：

This will generate one JSON value per store:

select to_jsonb(s)||jsonb_build_object('products', p.products)
from store s
  join (
     select p.store_id, jsonb_agg(to_jsonb(p) - 'store_id' order by p.store_id) products
     from product p
     group by p.store_id
  ) p on p.store_id = s.id
;

Online example