Let's assume that we have 3 tables: product, organization, advert
product.sql
CREATE TABLE product (
name VARCHAR NOT NULL,
id BIGSERIAL NOT NULL PRIMARY KEY,
advert_id BIGINT NOT NULL REFERENCES advert(id),
organization_id BIGINT NOT NULL REFERENCES organization(id)
);
organization.sql
CREATE TABLE organization (
name VARCHAR NOT NULL,
id BIGSERIAL NOT NULL PRIMARY KEY,
);
advert.sql
CREATE TABLE advert (
title VARCHAR,
id BIGSERIAL NOT NULL PRIMARY KEY,
);
In the end I want to have JSON structure like this
{
id: "ADVERT_ID",
title: "ADVERT_TITLE",
products: [
{
id: "PRODUCT_ID",
advert_id: "ADVERT_ID",
organization: {
id: "ORGANIZATION_ID",
name: "ORGANIZATION_NAME"
},
product: {
id: "PRODUCT_ID",
name: "PRODUCT_NAME"
},
},
{
id: "PRODUCT_ID",
advert_id: "ADVERT_ID",
organization: {
id: "ORGANIZATION_ID",
name: "ORGANIZATION_NAME"
},
product: {
id: "PRODUCT_ID",
name: "PRODUCT_NAME"
}
}
]
}
And let's say when the offers array is empty, I don't want to get a JSON array like this:
[null]
Thank you very much!
EDIT
After doing some more research and trying to make a prototype in Sequelize, I was able to achieve this:
{
id: 1,
name: 'Buy from Hot Fries Inc. today!',
createdAt: '2021-07-07T18:17:39.723Z',
updatedAt: '2021-07-07T18:17:39.723Z',
Products: [
{
id: 1,
name: 'French Fries',
org_id: 1,
adv_id: 1,
createdAt: '2021-07-07T18:17:39.726Z',
updatedAt: '2021-07-07T18:17:39.726Z',
Organization: {
id: 1,
name: 'Hot Fries Inc.',
createdAt: '2021-07-07T18:17:39.713Z',
updatedAt: '2021-07-07T18:17:39.713Z'
}
}
]
}
I have chosen Sequelize to quickly see the logs of the queries that were going on undercover and here's what I discovered:
SELECT "Advert".*,
"products"."id" AS "Products.id",
"products"."name" AS "Products.name",
"products"."org_id" AS "Products.org_id",
"products"."adv_id" AS "Products.adv_id",
"products"."createdat" AS "Products.createdAt",
"products"."updatedat" AS "Products.updatedAt",
"Products->Organization"."id" AS "Products.Organization.id",
"Products->Organization"."name" AS "Products.Organization.name",
"Products->Organization"."createdat" AS "Products.Organization.createdAt"
,
"Products->Organization"."updatedat" AS
"Products.Organization.updatedAt"
FROM (SELECT "Advert"."id",
"Advert"."name",
"Advert"."createdat",
"Advert"."updatedat"
FROM "adverts" AS "Advert"
LIMIT 1) AS "Advert"
LEFT OUTER JOIN "products" AS "Products"
ON "Advert"."id" = "products"."adv_id"
LEFT OUTER JOIN "organizations" AS "Products->Organization"
ON "products"."org_id" = "Products->Organization"."id";
This is the way that Sequelize joined sub-tables, if it's called that way.