Search from keys in foreign table - mysql

Question

I am using nestjs and typeorm (Mysql). I have two tables users and categories, and in category table i have a foreign key linked to userid. What i want to do is search firstName from user table while querying the category table.

I have 2 search fields category name and user name. For searching category name what i did is

const query = this.createQueryBuilder('category');
if (categoryName) {
      query.andWhere('category.categoryName LIKE :categoryName', {
        categoryName: `%${categoryName}%`,
      });
    }

And for searching username

if (userName) {
      query.andWhere('category.user.firstName LIKE :userName', {
        userName: `%${userName}%`,
      });
    }

But the above one is giving me error. Any idea how to do it using typeorm and nestjs ? Thanks for the help !

Answer 1

You need to join category with user first. Try something like this:

const query = this.createQueryBuilder('category');

if (categoryName) {
  query.andWhere('category.categoryName LIKE :categoryName', { categoryName: `%${categoryName}%` });
}

if (userName) {
  query
    .leftJoinAndSelect('category.user', 'user')
    .andWhere('user.firstName LIKE :userName', { userName: `%${userName}%` });
}