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