TypeORM - Many-to-Many where clause on join table

Question

I'm using [email protected] with MySql driver.
I have an entity, MyEntity, which has a many-to-many relationship with another entity, RelEntity. Therefore Typeorm generates a pivot table of the PKs to create the relationship.

My problem comes from the need to find all MyEntity using a where clause of RelEntity.prop = mySearchKey.

I'm not sure if this is down to me missing some documentation or not fully understanding how to structure this query withing Typeorm.

I believe my SQL statement for this would look something like:

SELECT m.* FROM my_entity_table m 
LEFT JOIN my_rel_pivot_table mrp ON mrp.my_entity_id = m.id 
LEFT JOIN rel_entity_table r ON r.id = mrp.rel_entity_id
WHERE r.id = {MY_VALUE_HERE}

However, I'm not too sure how to translate this into a Typeorm query.

I have tried:

this.entityRepo.find({
    where: {
        rel: myValue
    }
});

I have also tried:

this.entityRepo.find({
    where: {
        rel: { id: myValue}
    }
});

However, I received the following error:
Unknown column 'MyEntity.myEntityId' in 'where clause'

Has anyone encountered this issue before, or have any idea how to solve this?

Answer 1

I've this working for me, between my articles and categories (many-to-many).

To get my articles that are from a specific category:

  this
  .entityRepo
  .createQueryBuilder('article')
  .innerJoin(
       'article.categories', 
       'category', 
       'category.id = :categoryId', 
       { categoryId: XXX }
  ).getMany();

XXX is the id of the specific category.

Hope it will help you.