CODEWITHSUNDEEP
sqlpostgresql
stkvtflw
stkvtflw

Reputation: 13587

How do I retrieve data from multiple related tables in Postgres?

Postgres 9.4
4 tables have many-to-many relations between each other. I've created additional table to implement relations:

CREATE TABLE "public"."relation" (
  "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
  "table1" uuid NOT NULL,
  "table2" uuid,
  "table3" uuid,
  "table4" uuid,
  "approved" bool DEFAULT true,
  CONSTRAINT "relation_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "table1" FOREIGN KEY ("table1") REFERENCES "public"."table1" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "table2" FOREIGN KEY ("table2") REFERENCES "public"."table2" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "table3" FOREIGN KEY ("table3") REFERENCES "public"."table3" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT "table4" FOREIGN KEY ("table4") REFERENCES "public"."table4" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."relation" OWNER TO "postgres";

I need to retrieve a single row from table1 including all the related rows from other tables as JSON object. Here i've retrieved rows from relation table:

SELECT t.*
   FROM ( SELECT table1.id,
            (select row_to_json(relations.*) as array_to_json
              from(select * from relation where table1 = table1.id) relations
            ) as relations,

           from public.table1) t

But I can't figure out how to effectively retrieve rows from related tables through the data from the relation table.

May be this is valuable information:
Each row in relation table contains only two relations. For example, it might contain relation to table1 and table2. The rest of the columns are empty (except id, of course). Each row from tables table1,2,3,4 has less than 10 relations.
I want to retrieve something like this:

{
    id: table1.id,
    name: table1.name,
    related_items: [
        {id: table2.id, name: table2.name},
        {id: table4.id, name: table4.name},
        {id: table3.id, name: table3.name},
        {id: table2.id, name: table2.name},
        {id: table3.id, name: table3.name},
    ]
}

Thank you for your time!

Upvotes: 0

Views: 1319

Answers (1)

Christian
Christian

Reputation: 7320

SELECT ROW_TO_JSON(T)
FROM    (
        SELECT t1.id,
               t1.name,
               array_to_json(array((
                 select case when t2.id is not null then row_to_json(t2) 
                             when t3.id is not null then row_to_json(t3) 
                             when t4.id is not null then row_to_json(t4) 
                        end
                 from   public.relation r 
                 LEFT JOIN public.table1 t2 on r.table2 = t2.id
                 LEFT JOIN public.table1 t3 on r.table3 = t3.id
                 LEFT JOIN public.table1 t4 on r.table4 = t4.id
                 where  r.table1 = t1.id
               ))) related_items
        FROM   public.table1 t1
       ) T

Upvotes: 0

Related Questions