I have two tables - cards and colors.
CREATE TABLE cards(
id serial PRIMARY KEY,
text text NOT NULL CHECK (text <> ''),
colors integer[] DEFAULT array[]::integer[]
);
CREATE TABLE colors(
id serial PRIMARY KEY,
color text NOT NULL
);
Colors table contains all available colors.
Colors column, in cards table, contain ids of colors, related to particular card.
I need select all cards, where each card should contain array with all available colors and each color should have active
property, which tells has card this color or not
For example if cards and colors tables have these entries:
cards:
id | text | colors
----+--------+--------
1 | card 1 | {1,2}
2 | card 2 | {}
colors:
id | color
----+-------
1 | red
2 | green
3 | blue
Needed result should be:
[{
id: 1,
text: 'card 1',
colors: [{
id: 1,
color: 'red',
active: true,
}, {
id: 2,
color: 'green',
active: true,
}, {
id: 3,
color: 'blue',
active: false,
}]
}, {
id: 2,
text: 'card 2',
colors: [{
id: 1,
color: 'red',
active: false,
}, {
id: 2,
color: 'green',
active: false,
}, {
id: 3,
color: 'blue',
active: false,
}]
}]
To assign all available colors with colors
column in cards table, I used this query:
SELECT c.id, c.text, json_agg(cl) AS colors FROM cards AS c
LEFT JOIN colors AS cl ON (cl.id IS NOT NULL)
GROUP BY c.id
But how I can add active
flag to each color?
I tried to use select
inside of join:
SELECT c.id, c.text, json_agg(cl) AS colors FROM cards AS c
LEFT JOIN (
SELECT cl.id, cl.color, cl.id = ANY(c.colors) AS active
FROM colors AS cl
) AS cl ON (cl.id IS NOT NULL)
GROUP BY c.id
But it returns an error - invalid reference to FROM-clause entry for table "c"
As I understand, I can't have access to c
reference in query inside of join.
So, how to make this code correct? Thanks.
Aucun commentaire:
Enregistrer un commentaire