Cannot replicate PostgresQL query with joins in knex.js

Question

I'm working with the following tables/schemas:

Users have id as primary, also email, username, etc.

Studies have a users property: an array of integers (which are all user ids).

Question: What I would like to do is find the id/username/email of all users related to a single study.

My actual working PostgresQL query is as follows (schema/table names shortened for brevity):

SELECT u.id, u.username, u.email
FROM users u
INNER JOIN studies s
  ON u.id = ANY(s.users)
WHERE s.id = 1

This will successfully get me a list of users who are on the study with id of 1.

However, my problem lies with converting this query using knex.js. I have tried the following:

return knex('users')
  .select('id', 'username', 'email')
  .from('users')
  .innerJoin('studies', 'users.id', '=', 'ANY(studies.users)')
  .where('studies.id', 1);

Yet, this returns an error:

42P01 There is an entry for table \\"studies\\", but it cannot be referenced from this part of the query.

I have also tried the following which all have the same error:

// fails
return knex('users')
  .join('studies', 'users.id', 'ANY(studies.users)')
  .select('users.id', 'users.username', 'users.email')
  .where('studies.id', 1);

// fails
return knex('users')
  .select('id', 'username', 'email')
  .from('users')
  .innerJoin('studies', function() {
     this.on('users.id', '=', 'ANY(studies.users)');
  })
  .where('studies.id', 1);

I have seen this question here but I don't see how I'm mixing explicit and implicit joins. Can anyone point me in the right direction?

Update

I've resorted to using knex.raw for now as I don't understand what the issue is with chaining the other methods. I don't find it nearly as reusable as I'd prefer, but it works none-the-less. Here's what I ended up going with:

async function getAllByStudy(studyId) {
  try {
    const results = await knex.raw(`
      SELECT u.id, u.username, u.email
      FROM users u
      INNER JOIN studies s
        ON u.id = ANY(s.users)
      WHERE s.id = ${studyId}
    `);
    return results.rows;
  } catch (err) {
    return new Error(err);
  }
}

The async/await features allowed for some nice structure since knex.raw is asynchronous. I'm settling with it for now but if anyone has a better way to do it please let me know!

I've also tried using array notation and checking against ARRAY['studies.users']::integer[] but that fails just the same. — chazsolo
– chazsolo, Commented May 2, 2017 at 18:50
You could try by using onIN and UNNEST (Note: I'm not good on knex) — McNets
– McNets, Commented May 2, 2017 at 19:03

Fazal Rasel · Accepted Answer · 2017-05-03 14:01:20Z

1

you didn't mention the relation betweens users and studies table (OneToOne or OneToMany).

And it will be easy if you start your query with studies table as

knex('studies')
.leftJoin('users', 'users.id', 'users.user_id')
.where('studies.id', YOUR_QUERY_ID)
.columns([
  'users.id',
  'users.username',
  'users.email'
])
.then((results) => {
  console.log(results) // array
})

Edit 1 Database Table Joins-

edited May 3, 2017 at 14:01

answered May 3, 2017 at 9:16

Fazal Rasel

4,5262 gold badges23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

chazsolo Over a year ago

Could you explain the leftJoin? I don't understand where users.users_id comes from. The relationship between users and studies is ManyToMany: multiple users can be in a study, and users can belong to as many studies as necessary. I very much appreciate your answer, as well - at least it is not throwing any errors! Just returns with an empty object.

Fazal Rasel Over a year ago

I edited my answer with little explaining table joins. And it should return array if your studies tables has proper data.

chazsolo Over a year ago

Thanks for the diagram! I'm continuing to get the same errors, however.

Collectives™ on Stack Overflow

Cannot replicate PostgresQL query with joins in knex.js

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related