For the given schema:
create table foo (a int not null, b int not null, primary key (a, b));
create table bar (a int, b int, c int, foreign key foo_fk (a, b) references foo (a, b));
I want to join a and b, where there may be any number of bs for each a.
I can only come up with three solutions:
1 query, repeated data:
db.select().from('foo').leftJoin('bar', function() {
this.on('foo.a', '=', 'bar.a').on('foo.b', '=', 'foo.b')
}then(foobars => {
/* do somethign with both foos and bars */
});
n+1 queries:
db.select().from('foo').then(foos => {
return Promise.map(foos, foo => db.select().from('bar').where({a: foo.a, b: foo.b}))
.then(bars => { /* do something with both foos and bars */ })
});
one query with raw and group_concat:
db.raw('select foo.a, foo.b, group_concat(bar.c) as c from foo join bar on foo.a=bar.a and foo.b=bar.b').then(foobars => { /* do something with foobars */ })
None of these solutions is very appealing.
In the first solution, I have to examine the same foos multiple times (once for each bar corresponding to a given foo).
In the second solution, I have n+1 queries which is a performance killer.
In the third solution, aliasing of c allows for delimiter injection, and I'm using a raw query.