I have a Nestjs app that runs on a SQL database using Objection.js as the ORM
I have these tables:
books
id - integer
name - string
tags
id - integer
name - string
And they both have a ManyToMany relationship through this table:
book_tags
id - integer
book_id - integer
tag_id - integer
I have the relationship mapped on the tags model like so:
static relationMappings: RelationMappings | RelationMappingsThunk = {
books: {
relation: Model.ManyToManyRelation,
modelClass: BookModel,
join: {
from: 'tags.id',
through: {
from: 'book_tags.tag_id',
to: 'book_tags.book_id',
},
to: 'books.id'
}
}
}
Now, I am trying to search for books that have some tags using their IDs.
I'm currently only able to fetch the tags while eager loading the books using:
const tagIdArrays = [1, 2, 3, 4, 5]
this.tagModel.query()
.whereIn('id', tagIdArrays)
.withGraphJoined('books')
.execute()
Issue is, I am getting all the tags with their related books and having to do the a sort and unique mapping manually using javascript so as to get only unique books and order the results according to the books that have the highest matching tags.
I am also adding an array of matched tags to the books object in my mapping function.
I am looking for a way to do this on the DB level using ObjectionJS if it's possible at all.