How to query an one to one to many relationship using TypeORM query builder?

Question

So right now I have four tables

**Users**
user_id;

**Categories**

category_id;
user_id;

**Todos**

todo_id;

**Categories_Todos** (joinTable)

category_id;

todo_id;

so there relationship are

1. A user can have a lot of categories ( one to many)

2. Categories can have a lot of Todos and Todos can also have a lot Categories(many to many)

I accomplish get's certain categories's todo's by following query buillder

const response = await getRepository(TodoItem)
      .createQueryBuilder("todoItem")
      .leftJoin('todoItem.categories', 'categories')
      .where('categories.id=:id',{id:categoryId})
      .getMany();

But what I really want to do is to get a user's todos by userID, I think it's like an one to many to many relationship , how can I do that ?

Answer 1

You should also join user table and then get all distinct values:

const response = await getRepository(TodoItem)
  .createQueryBuilder("todoItem")
  .leftJoin('todoItem.categories', 'categories')
  .leftJoin('categories.user', 'user')
  .where('user.id=:id',{id: userId})
  .distinct(true)
  .getMany();