Latest news:
Sahih al-Bukhari (সহীহ বুখারী) is a free Hadith application for android. This application is advertisement free. Download now https://play.google.com/store/apps/details?id=com.akramhossin.bukharisharif
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);
Views : 2672