CODEWITHSUNDEEP
javascriptpostgresqlknex.js
chazsolo
chazsolo

Reputation: 8439

Cannot replicate PostgresQL query with joins in knex.js

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!

Upvotes: 0

Views: 974

Answers (1)

Fazal Rasel
Fazal Rasel

Reputation: 4526

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- enter image description here

Upvotes: 1

Related Questions