How to query .where() using references instead of literal string values in Objection/Knex

Question

I'm trying to generate this postgres SQL with Knex and Objection:

select *
from "WorkoutEntry" e
where EXISTS (
   select *
   from "WorkoutCategory" c
   INNER JOIN "StandardWorkoutDefinition" d on d."workoutCategoryId" = c.id
   where c.id = 2 and e."standardWorkoutDefinitionId" = d.id
);

I'm almost there - but the StandardWorkoutDefinition.id line is always converting to a literal string instead of referencing the proper column in the DB.

 return await WorkoutEntry.query()
    .whereExists(
      WorkoutCategory.query()
        .innerJoin(
          'StandardWorkoutDefinition',
          'StandardWorkoutDefinition.workoutCategoryId',
          'WorkoutCategory.id',
        )
        .where('WorkoutCategory.id', workoutCategoryId)
        .where(
          'WorkoutEntry.standardWorkoutDefinitionId',
          'StandardWorkoutDefinition.id',
        ),
    )
    .andWhere({
      deletedAt: null,
      memberId,
    });

How can I get e."standardWorkoutDefinitionId" to output instead to make the query right?

Freewalker · Answer 1 · 2017-10-05T00:54:17.190

This is a case for ref(). Where() will always accept a value as the second comparison point; whereRef() will accept a reference.

To compare two ids then:

    .whereRef(
      'WorkoutEntry.standardWorkoutDefinitionId',
      '=',
      'StandardWorkoutDefinition.id',
    ),

In Objection.js 1.0, whereRef will be deprecated and the call will be:

.where('WorkoutEntry.standardWorkoutDefinitionId', ref('StandardWorkoutDefinition.id'))

Pretty sure I'm still missing an easier Objection way to do this, will look for that and prob update here.

Update: WOW this is easy in Objection.js!

return await WorkoutEntry.query()
    .joinRelation('standardWorkoutDefinition.workoutCategory', {
      alias: 'category',
    })
    .where('category.id', workoutCategoryId);

How to query .where() using references instead of literal string values in Objection/Knex

1 Answers1