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

Question

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.

====== UPDATE ======

Based on another post, I decided to try a different approach - using a single query and have Postgres return the JSON. Here is the function:

    var serverFamilyQuery = `SELECT json_build_object( 'id', s.id, 'name', s.name, 'server_environment', ( 
                SELECT json_agg(json_build_object('id', se.id, 'name', se.name)) 
                FROM salt.server_environment se 
                WHERE se.id = s.id ), 'applications', (
                  SELECT json_agg(json_build_object('id', ap.id, 'name', ap.name)) 
                  FROM salt.application ap
                  JOIN salt.server_application sa ON sa.application_id = ap.id
                  WHERE sa.server_id = s.id )
               ) result
              FROM salt.server s
              WHERE s.id = $1`

function getServerFamily(req, res, next) {
  var serverID = parseInt(req.params.id);
  db.one(serverFamilyQuery, [serverID])
    .then(data => {
      debug('data: %s', data);
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved Application Successfully'
        });
    })
    .catch(function (err) {
      return next(err);
    });
};

And here is the result:

{
  "status": "success",
  "data": {
    "result": {
      "id": 1,
      "name": "app01",
      "server_environment": [
        {
          "id": 1,
          "name": "Via West"
        }
      ],
      "applications": [
        {
          "id": 1,
          "name": "SnapApp"
        }
      ]
    }
  },
  "message": "Retrieved Application Successfully"
}

As I mentioned earlier, I would prefer that the server_environment, server, and application be separate entries in the json, but at least this works.

Answer 1

Something this simple:

db.task(async t => {
    const child = await t.one('SELECT * FROM child WHERE id = $1', 123);
    const parent = await t.one('SELECT * FROM parent WHERE id = $1', child.id);
    const associates = await t.any('SELECT * FROM associate...');
    return {child, parent, associates};
})
    .then(data => {
        // success, data = {child, parent, associates}
    })
    .catch(error => {
        // error
    });