0

My question is based on Combine nested loop queries to parent array result - pg-promise. I'm having a similar scenario but have multiple queries to be combined to get my final results. Following is my code with which I tried to implement my requirement. But I was not able to get the results from second query combined with the main query. I'm a beginner and would like to know the correct way of implementation.

  db.task(t => {
    const a = studies => t.any ('SELECT facility_contacts.name, facility_contacts.email, facility_contacts.phone FROM facility_contacts WHERE nct_id = $1', studies.nct_id).then(facility_contacts => {
    studies.facility_contacts = facility_contacts;
    return studies
      });
    const b = studies => t.any ('SELECT eligibilities.criteria, eligibilities.gender FROM eligibilities WHERE nct_id = $1', studies.nct_id).then(eligibilities => {
    studies.eligibilities = eligibilities;
    return studies;
      });
  return t.map('SELECT studies.nct_id, studies.official_title, studies.phase, facilities.country FROM studies, facilities WHERE facilities.country LIKE \'%Ireland%\' LIMIT 10',[], a, b).then(t.batch);
}).then(studies => {
  console.log(studies);
  res.send(studies);
}).catch(error => {
  console.log(error);
});

Thanks in advance.

Naveen George Thoppan
  • 551
  • 1
  • 5
  • 27
  • You should post the code of what you have tried so far, and explain which part didn't work. Simply asking someone to write your code for you isn't going well with the StackOverflow community. Also, the question title is meaningless in this context. – vitaly-t Jul 23 '18 at 10:37
  • @vitaly-t Sorry for the inconvenience. I've added my code. – Naveen George Thoppan Jul 23 '18 at 10:52

2 Answers2

1

Here's one possibility:

db.task(t => {
    const a = studies => t.any('SELECT facility_contacts.name, facility_contacts.email, facility_contacts.phone FROM facility_contacts WHERE nct_id = $1', studies.nct_id)
        .then(facility_contacts => {
            studies.facility_contacts = facility_contacts;
            return studies
        });
    const b = studies => t.any('SELECT eligibilities.criteria, eligibilities.gender FROM eligibilities WHERE nct_id = $1', studies.nct_id)
        .then(eligibilities => {
            studies.eligibilities = eligibilities;
            return studies;
        });
    const c = studies => t.batch([a(studies), b(studies)]);
    return t.map('SELECT studies.nct_id, studies.official_title, studies.phase, facilities.country FROM studies, facilities WHERE facilities.country LIKE \'%Ireland%\' LIMIT 10', [], c)
        .then(t.batch);
})
    .then(studies => {
        console.log(studies);
        res.send(studies);
    })
    .catch(error => {
        console.log(error);
    });

There can be many different approaches, some way better performing, like JSON-based.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
0

You can use Promise.all to get all result

db.task(t => {
    const queries = [
        t.any('SELECT facility_contacts.name, facility_contacts.email, facility_contacts.phone FROM facility_contacts WHERE nct_id = $1', studies.nct_id),
        t.any('SELECT eligibilities.criteria, eligibilities.gender FROM eligibilities WHERE nct_id = $1', studies.nct_id),
        t.map('SELECT studies.nct_id, studies.official_title, studies.phase, facilities.country FROM studies, facilities WHERE facilities.country LIKE \'%Ireland%\' LIMIT 10', [], a, b)
    ];
    return Promise.all(queries);
}).then((ResultArrayOfQueries) => {
    console.log(ResultArrayOfQueries);
    // Combine as per your requirement
    res.send(ResultArrayOfQueries);
}).catch(error => {
    console.log(error);
});
Rahul Sharma
  • 9,534
  • 1
  • 15
  • 37