To filter relational table in TypeORM using findAndCount there is a workaround. To filtering based on relation fields for findAndCount.

We need to use ObjectLiteral-style where using this style conditions are works perfectly.

Assume that we have Three entities – products,attributeSet and woodType. products belongs to one attributeSet and one woodType, 

attributeSet and woodType has many products. Below is complete example of a findAndCount query in TypeORM

filter_likes:

At first we will declare likes array, this array will be used for SQL like operation

const filter_likes = [
 {
  name: 'nameEn',
  value: name_en,
 },
 {
  name: 'nameAr',
  value: name_ar,
 },
 {
  name: 'sku',
  value: sku,
 },
 {
  name: 'barcode',
  value: barcode,
 },
 {
  name: 'costPrice',
  value: costPrice,
 },
 {
  name: 'salePrice',
  value: salePrice,
 },
 {
  name: 'quantity',
  value: quantity,
 },
];

relation_filter:

Now we will declare relation filter for other tables

const relation_filter = [
 {
  name: 'attributeSet.nameEn',
  value: attributeSetNameEn,
 },
 {
  name: 'woodType.nameEn',
  value: woodTypeNameEn,
 },
];

sorting:

Declare sorting for query

const sorting = {
 sort: sort,
 order: order,
};

Main query:

const query: any = {};
query.join = {
 alias: 'products',
 leftJoinAndSelect: {
  attributeSet: 'products.attributeSet',
  woodType: 'products.woodType',
 }
};
query.where = (qb: any) => {
 qb.where({
  isDeleted: 0,
 });
 if (filter_likes && filter_likes.length > 0) {
  filter_likes.forEach((columns: any) => {
   if (columns.value !== undefined) {
    let column_name = columns.name;
    let column_value = columns.value;
    qb.andWhere({
     [column_name]: Like('%' + [column_value] + '%')
    });
   }
  })
 }
 if (relation_filter && relation_filter.length > 0) {
  relation_filter.forEach((columns: any) => {
   if (columns.value !== undefined) {
    let column_name = columns.name;
    let column_value = columns.value;
    qb.andWhere([column_name] + ' LIKE :fieldName', {
     fieldName: '%' +[column_value]+ '%'
    });
   }
  })
 }
};

skip:

Offset number specifies the number of rows to skip

query.skip = skip;

take:

limit the number of item returned by SQL

query.take = take;

sorting:

query.order = {};
if (sort_attributes.sort !== undefined) {
 let field_name = sort_attributes.sort;
 let order_by = sort_attributes.order;
 query.order[field_name] = order_by;
} else {
 query.order = {
  sortOrder: "ASC",
  productId: "DESC",
 };
}

return this.productRepository.findAndCount(query);

Tags : javascript, MySQL, Nodejs, Typeorm,