2

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.

Ray Van Eperen
  • 89
  • 2
  • 9
  • This should help: http://stackoverflow.com/questions/39805736/get-join-table-as-array-of-results-with-postgresql-nodejs – vitaly-t May 16 '17 at 10:11
  • Thanks for your answer, but I had already read through that example. The differences are 1) I need to pass in the id for the child table, 2) I need to do a third query to get the associates through the many to many table. 3. I'm looking to have the parent, child, and associates at the top level in the json result, and this example nests the data. In another case, I'll need to also fetch multiple parents through a many to many as I am doing for the associates, and would need this solution to support that as well. I can't find an example that covers this scenario. – Ray Van Eperen May 16 '17 at 17:09
  • That example lays down the base principle for making queries and passing the data into the next one. It is unclear what is the nature of your problem - SQL / JSON / promises / pg-promise, since you are not providing any code example. But that example from the link is quite sufficient for what it does there. – vitaly-t May 16 '17 at 17:50

1 Answers1

2

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
    });
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • What is missing from your example above is that I need the result of the first query "t.one('SELECT * FROM child...) so that I can use the parent_id of that result in order to perform the third query. Your example uses 123 for both ids, but for parent, it is the parent_id of the child returned in the first query that I need to use. I have tried to chain those together, but have not been able to get it working. Sorry, but as I mentioned, I'm very new to all of this, and appreciate the help. – Ray Van Eperen May 16 '17 at 18:38
  • @RayVanEperen so I've updated it, it is a generic promise approach. – vitaly-t May 16 '17 at 18:56
  • The only problem with the solution above is that the child is not contained in 'data'. parent are associate are there as data[0] and data[1], but child is missing. I was actually able to get this far before, but was not able to get all three entities in the result. – Ray Van Eperen May 16 '17 at 22:44
  • @RayVanEperen It is trivial, and best suited for ES6 generators - see the update. – vitaly-t May 16 '17 at 23:19
  • Perfect, that's what I've been looking for. I have not worked at all with ES6 generators, so I guess I need to study up on them. Thank You! – Ray Van Eperen May 17 '17 at 00:13