Hello While implementing the search (filter) function, I have a ques

What should I do if I want to use the field in the join table as a search (or filter) condition? about nestjs-paginate HOT 2 CLOSED

ppetzold commented on September 15, 2024

What should I do if I want to use the field in the join table as a search (or filter) condition?

from nestjs-paginate.

Comments (2)

ppetzold commented on September 15, 2024 1

Hi there :)

You can probably achieve this right now by using the query builder. Something like this should work:

const queryBuilder = this.usersRepository
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.profile', 'profile')
    .where('profile.name LIKE :name', { name: `%${query.search}%` })

paginate(query, queryBuilder, {
    sortableColumns: ['id', 'username'],
    searchableColumns: ['username'],
    defaultSortBy: [
        ['id', 'DESC'],
        ['username', 'ASC'],
    ],
})

Otherwise you may want to tackle #81 - happy to accept proposal / pr :)

from nestjs-paginate.

jeongbin-park commented on September 15, 2024 1

Thank you for your quick and sincere answer :)

I posted a question yesterday and thought about it. It's a similar idea!

The code that I wrote is as follows.

relation: capaTest -- 1 : N --- capaTestType

situation: I want to filter the evaluation questions with tags.

query: {{HOST_URL}}/api/contents/capa-tests?filter.capaTestType=TAG-A,TAG-B

const builder = await this.createQueryBuilder('capaTest')
      .leftJoinAndSelect('capaTest.capaTestType', 'capaTestType')
      .select([
        'capaTest.capaTestId',
        'capaTest.question',
        'capaTest.createdAt',
        'capaTest.updatedAt',
        'capaTestType.capaTestTypeId',
        'capaTestType.name',
      ]);

if (query.filter?.capaTestType) {
      const names = query.filter?.capaTestType as string;
      builder.where('capaTestType.name IN (:nameList)', { nameList: names.split(',') });
}

However, when I set PageNateOption as follows, an error occurred.

const result = await paginate(query, builder, {
      sortableColumns: ['updatedAt'],
      searchableColumns: ['question'],
      defaultSortBy: [['createdAt', 'DESC']],
 });

So this time, I added the settings as follows.

const result = await paginate(query, builder, {
      filterableColumns: {},
      sortableColumns: ['updatedAt'],
      searchableColumns: ['question'],
      defaultSortBy: [['createdAt', 'DESC']],
 });