jeudi 16 juin 2016

How to assign whole table with column and add active flag in postgresql

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