Skip to content

Instantly share code, notes, and snippets.

@hdon
Last active Apr 21, 2017
Embed
What would you like to do?
Anyone have a better solution in knex?

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment