Search into the ManyToMany of relations

[manishkhalde91]

Below is my entity

@Entity()
export class User extends Audit {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ name: 'first_name', type: 'varchar', length: '255' })
  firstName: string;

  @Column({ name: 'last_name', type: 'varchar', length: '255' })
  lastName: string;

  @ManyToOne(() => Organization, (organization) => organization.id, { nullable: true })
  organization?: Promise<Organization>;

  @ManyToMany(() => User)
  @JoinTable({
    name: 'user_approvers',
    joinColumn: { name: 'user_id', referencedColumnName: 'id' },
    inverseJoinColumn: { name: 'approver_id', referencedColumnName: 'id' },
  })
  approvers?: Promise<User[]>; // Approvers relationship

  @ManyToMany(() => Role)
  @JoinTable({
    name: 'user_role',
    joinColumn: { name: 'user_id', referencedColumnName: 'id' },
    inverseJoinColumn: { name: 'role_id', referencedColumnName: 'id' },
  })
  roles: Promise<Role[]>;
}

I want to search in all approval on the field of firstName and lastName
Below is config code

      const config: PaginateConfig<User> = {
        relations: { approvers: {}, manager: {}, roles: {} },
        where: { organization: { id: orgId } },
        sortableColumns,
        nullSort: 'last',
        searchableColumns: [
          'id',
          'firstName',
          'lastName',
          'gender',
          'contactNumber',
          'alternativeContactNumber',
          'email',
          'loginEmailId',
          'department',
          'group',
          'createdAt',
          'updatedAt',
          'manager.firstName',
          'manager.lastName',
        ],
      };
      return await paginate<User>(query, this.userRepository, config);

[Helveg]

I would think that you can do this by adding approvers.firstName and approvers.lastName to the searchableColumns?

[manishkhalde91]

I have added the column in the searchableColumns, But now I want to search with a combination of first name and last name, like "Manish Khalde"
If I enter "Manish" and "Khalde" individually, then it works, but I want the full name search.

[ppetzold]

That's because the query used looks like firstName ILIKE '%foo%' OR lastName ILIKE '%foo%'. If you need a solution with the current version, then your only choice is to create a virtual column which combines the two. Otherwise a feature which let's you customise the search statement might be useful in general - so you could switch it up to something like CONCAT(firstName, ' ', lastName) ILIKE '%foo%'.