Monday, 15 May 2017

How to combine separate pg-promise queries into one result in pg-promise

I'm new to node and pg-promise, and have not been able to figure out how to get the result of three related queries into one json result.

Given three related tables:

A parent entity

create table parent ( 
id bigint,
name character varying
);

A child entity

create table entity_2 ( 
id bigint,
parent_id bigint,
name character varying
);

A many to many table between the child and associate

create table entity_2_entity_3 (
id bigint,
child_id bigint, 
associate_id bigint
);

An associate table to the child table

create associate (
id bigint,
name character varying
);

And my service url is /api/family/1 (where 1 is the child id)

First query is (returns one result):

SELECT * 
FROM child 
WHERE id = $1 

(uses the child id param)

Second query is (returns 0 to many results):

SELECT a.* 
FROM associate a 
JOIN child_associate ca ON ca.associate_id = a.id 
JOIN child c ON c.id = b.child_id 
WHERE c.id = $1 

(uses the id param)

Third query is (returns the parent for the child

SELECT *
FROM parent
where id = $1 

(uses the parent_id from the child record in previous query)

The resulting JSON should contain one entry for 'parent', one entry for 'child', and one entry of an array of 'associates'.

What is the best way to do this? I have come close, but have not been able to get it right.

Thanks in advance for the help. Any by the way - love pg-promise! Glad I decided to write the entire back-en app in node and pg-promise.



via Ray Van Eperen

No comments:

Post a Comment