How to apply filter on join table in many-to-many relation

Question

I have many-to-many relationships between class and adventures.

So, I have 3 tables named class, adventure and class_has_adventure.

Class Table:

Adventure Table:

class_has_adventure Table:

as you can see in the class_has_adventure table I have 2 additional fields named date_start and date_stop. I want to add a filter on date_start and date-stop in the join table (class_has_adventure). I am using Knex and Objection in my Nodejs app. I tried some methods but I didn't get results. I have used withGraphJoined, withGraphFetch and joinRelation.

Is there any way to add the filter on the join table (class_has_adventure) while querying the data from adventure or class?

Answer 1

We can use Knex join, in your case:

await knex.select('*')
        .from('class')
        .join('class_has_adventure', 'class.id', 'class_has_adventure.id_class')
        .where('class_has_adventure.date_start', '>', new Date('2023-02-10'))