I have added smart fields which are fetch data by joining multiple tables . I would like to implement Search functionality on each smart fields.two ways to implement search functionality on smart field .
Apply filter method on smartfield in forest/file
Override route
Please help me to resolve this issue.
As you can see in this image. I have search on id it seems works.
If we will search based on Customer name . It wan’t work because it’s smart field.
Please connect for more information.
Hello @Mayur_Malviya ,
Could you share with me the code you use for the smart field search?
You can find an example in the documentation .
@Guillaume_Cisco I have done this after looked at example. Can you help me how should I implement it or where I am doing mistakes?
router.get('/orders', permissionMiddlewareCreator.list(), (request, response, next) => {
const recordsGetter = new RecordsGetter(orders);
const params = request.query;
const searchValue = params.search;
console.log(searchValue);
if(searchValue) {
console.log("==========Inside the searchValue==================");
const queryData = `
SELECT o.id, u.full_name
FROM orders o LEFT JOIN users u ON u.id = o.user_id
WHERE (lower(u.full_name) LIKE lower('%${searchValue}%'))`;
const queryCount = `
SELECT COUNT(*) as count
FROM orders o LEFT JOIN users u ON u.id = o.user_id
WHERE (lower(u.full_name) LIKE lower('%${searchValue}%'))`;
const queryType = sequelize.QueryTypes.SELECT;
Promise.all([
sequelize.query(queryData, {type: queryType}),
sequelize.query(queryCount, {type: queryType})
])
.then(async ([orderStatsList, orderStatsCount]) => {
recordsGetter.getAll(params)
.then(records => {
recordsGetter.serialize(records)
})
.then(recordsSerialized => {
console.log("============recordsSerialized==============");
console.log(recordsSerialized);
response.send(recordsSerialized)})
.catch(next);
// const orderStatsSerializer = new RecordSerializer({ name: 'orders'});
// const orderStats = await orderStatsSerializer.serialize(orderStatsList);
// const count = (orderStatsCount[0].count) - 1;
// //console.log("===============================ORDERSTATS=============");
// //console.log(orderStats);
recordsGetter.serialize(orderStatsList);
})
.then(record => {
response.send({...orderStats, meta:{ count: count}});
})
.catch(next);
}
next();
});
Hello @Mayur_Malviya ,
Can you share with me your model definition and your smart field definition please?full_name a real table column in your database?
Thank you,
@Guillaume_Cisco Yes, full_name is real table column in my database.
const { collection } = require('forest-express-sequelize');
const models = require('../models');
//const orders = require('../models/orders');
const { Op, QueryTypes } = models.objectMapping;
// This file allows you to add to your Forest UI:
// - Smart actions: https://docs.forestadmin.com/documentation/reference-guide/actions/create-and-manage-smart-actions
// - Smart fields: https://docs.forestadmin.com/documentation/reference-guide/fields/create-and-manage-smart-fields
// - Smart relationships: https://docs.forestadmin.com/documentation/reference-guide/relationships/create-a-smart-relationship
// - Smart segments: https://docs.forestadmin.com/documentation/reference-guide/segments/smart-segments
collection('orders', {
actions: [],
fields: [
{
field: 'Restaurant Name',
type: 'String',
get: (order) => {
return models.orderProducts
.findOne({where:{ orderIdKey: order.id}})
.then((orderProducts) => {
return models.menuItems
.findOne({where: {id: orderProducts.menuItemId}})
.then((menuItem) => {
return models.chefs
.findOne({where: {id: menuItem.chefId}})
.then((chef) => {
return chef.restaurantName;
})
})
});
}
},
{
field: 'Chef Name',
type: 'String',
get: (order) => {
return models.orderProducts
.findOne({where:{ orderIdKey: order.id}})
.then((orderProducts) => {
return models.menuItems
.findOne({where: {id: orderProducts.menuItemId}})
.then((menuItem) => {
return models.chefs
.findOne({where: {id: menuItem.chefId}})
.then((chef) => {
return chef.fullName;
})
})
});
},
// search: function(query, search) {
// console.log("query :::::: ");
// console.log(query);
// console.log("search:::::: " + search);
// query.include.push({
// model: models.orderProducts,
// as : 'OrderProducts',
// include: [{
// model: models.menuItems,
// as: 'MenuItems',
// include: [{
// model: models.chefs,
// as: 'Chef',
// }]
// }]
// });
// console.log("query :::::: 1111 ");
// console.log(query);
// query.where[Op.and][0][Op.or].push(models.sequelize.literal(`"MenuItems->Chef"."fullName" ILIKE '%${search}%'`));
// return query;
// }
},
{
field: 'Customer Name',
type: 'String',
get: (order) => {
return models.users
.findOne({where:{ id: order.userIdKey}})
.then((user) => {
return user.fullName
});
},
// search: function(query, search) {
// console.log(search);
// query.include.push({
// model: models.users,
// as: 'users',
// });
// query.where[Op.and][0][Op.or]
// .push(models.objectMapping.literal(`"users->orders"."fullName" ILIKE '%${search}%'`));
// }
},
],
segments: [{
name: 'Delivered Order',
where: (order) => {
return models.orders.findAll({where :{ orderStatus: {[Op.or]: ['DELIVERED']}}})
.then((orders) => {
let orderIds = orders.map((order) => order.id);
return { id: { [Op.in]: orderIds }};
});
}
}, {
name: 'Pickup Order',
where: (order) => {
return models.orders.findAll({where :{ orderStatus: {[Op.or]: ['PICKEDUP']}}})
.then((orders) => {
let orderIds = orders.map((order) => order.id);
return { id: { [Op.in]: orderIds }};
});
}
}],
});
Note : Above code is applied as a smart field. Even I tried to implement search method on smart field but I couldn’t do that as well. I expect it would be better if you can help me to implement search method or help me to solve this research function.
If I understand correctly, you want to make the Chef Name and the Customer Name smart fields searchable from the Orders collection?
The search method for Chef Name is:
Mayur_Malviya:
search: function(query, search) {
console.log("query :::::: ");
console.log(query);
console.log("search:::::: " + search);
query.include.push({
model: models.orderProducts,
as : 'OrderProducts',
include: [{
model: models.menuItems,
as: 'MenuItems',
include: [{
model: models.chefs,
as: 'Chef',
}]
}]
});
console.log("query :::::: 1111 ");
console.log(query);
query.where[Op.and][0][Op.or].push(models.sequelize.literal(`"MenuItems->Chef"."fullName" ILIKE '%${search}%'`));
return query;
}
},
I see you are including the Chef table thanks to 2 others tables OrderProducts and MenuItems.
Yes, you understand right. I would like to implement search method for chef. Can you tell me what should I do?
Have you been able to look at the query executed there?
