Query the join several tables once or several queries?

Question

I have to make the bundle of the data from 4 (or more) tables. And as I known there are 2 ways to deal with.

• Join all of them on the database then query once.

• Query once per table then manage/combine them on the backend.

Even the first way seems more reasonable somehow, If they (any rows from join-result) have the same parent (parent-key(s)), the data from joining the tables still need to be grouped together.

For example.

// foos and bars from separately query
foos: [ { foo_id: 1, ...(foo1) }, { foo_id: 2, ...(foo2) } ];
bars: [
 { foo_id: 1, bar_id: 1, ...(bar1) },
 { foo_id: 1, bar_id: 2, ...(bar2) },
 { foo_id: 2, bar_id: 3, ...(bar3) },
 { foo_id: 2, bar_id: 4, ...(bar4) }
];

// foo-bars from the join-result using foo_id as join-key
foo_bar: [
 { foo_id: 1, ...(foo1), bar_id: 1, ...(bar1) },
 { foo_id: 1, ...(foo1), bar_id: 2, ...(bar2) },
 { foo_id: 2, ...(foo2), bar_id: 3, ...(bar3) },
 { foo_id: 2, ...(foo2), bar_id: 4, ...(bar4) }
];

Should be grouped/combined as

foos: [
 {
  foo_id: 1,
  ...(foo1),
  bars: [
   { bar_id: 1, ...(bar1) },
   { bar_id: 2, ...(bar2) },
  ]
 },
 {
  foo_id: 2,
  ...(foo2),
  bars: [
   { bar_id: 3, ...(bar3) },
   { bar_id: 4, ...(bar4) },
  ]
 },
];

and so on for other remains tables...

So, No matter how I query, The data still require to be managed on the backend.

Therefore At present, I have chosen the second way to implement. (but still looking for better ways to deal with, and plan to change if necessary)

I have grouped them with declare new object using the key of parent to be key pair up with itself as value to avoid searching with nested for-loop.

const arrayToObject = (array, key) => {
  const object = {};

  for (let i = 0; i < array.length; i += 1) {
    object[array[i][key]] = array[i];
  }

  return object;
};

So my bundle for-loop looks like..

const bundling = async() => {
 // foo.fetch() & bar.fetch() is models that
 // call db.query('SELECT * FROM foo/bar')

 const [rawFoo, rawBar] = await Promise.all([foo.fetch(), bar.fetch()]);
 const foos = arrayToObject(rawFoo, 'foo_id');

 // create blank child list in each parent
 for (let i = 0; i < rawFoo.lenght; i += 1) {
  rawFoo[i].bars = [];
 }

 // this similar to nested for-loop that
 // outer iteration is pointing to each parent member
 // and inner one is pointing to each child member
 // then check if they have same parent key >> do something

 for (let i = 0; i < rawBar.length; i += 1) {
  foos[ rawBar[i].foo_id ].bars.push( rawBar[i] );
 }

 return rawFoo;
}

According to another question, Which is more expensive? For loop or database call? There is an overhead of connection to the database that will cause my current solution worse than the another.

But the second-top-answer of JOIN queries vs multiple queries made me confused which one better when I think about the join output size compare with sum-size of each query.

Examples for sql code

# join table
# note that child table has parent id

SELECT * FROM foo
 LEFT JOIN bar ON foo.foo_id = bar.foo_id
 LEFT JOIN baz ON bar.bar_id = baz.bar_id
 LEFT JOIN qux IN baz.baz_id = qux.baz_id

# several query for manage on backend

SELECT * FROM foo;
SELECT * FROM bar;
SELECT * FROM baz;
SELECT * FROM qux;

the table structure of each table is similar to each other.

each table consists 3 main parts: parent_id, own_id, ...other_props

The number of other_props

• foo: ~ 15-20 fields
• bar: < 15 fields
• baz: < 10 fields
• qux: < 10 fields