Search code examples
sqlpostgresqlknex.js

knex, join and group

I have the following tables.

users {
  id,
  blog_id,
  deleted,
  ...other fields
}

and

blogs {
  id,
  posts: [],
  name,
  deleted,
}

It is possible within a single knex query to get one user with the blog posts?

I'm trying like this:

db
  .table('users')
  .where('id', id)
  .whereNot('deleted', true)
  .join('blogs', { 'blog_id': 'blogs.id' })
  .select();

But I receive the following error:

error: column reference "deleted" is ambiguous

Is there a better way to do this? (only need posts from blog table)

Solution

  • Just call another function ".columns([''])"-

    db('users')
.leftJoin('blogs', 'blogs.id', 'users.blog_id')
.where('blogs.id', id)
.whereNot('blogs.deleted', true)
.columns(['users.id', 'blogs.name']); // what ever you want