Hello @daniel.garcia ! Welcome to our community. 
As discussed yesterday, using raw SQL queries it’s pretty hard to handle all the filtering cases.
Translating filter Using raw SQL (Open to see more details but not recommended option)
Here’s some manual conversion to convert our front filters (example greater_than) to raw SQL partial queries.
Non-exhaustive example…
const formatOperatorValue = (operator, value) => {
switch (operator) {
case 'not':
return ` NOT ${value}`;
case 'greater_than':
case 'after':
return ` > ${value}`;
case 'less_than':
case 'before':
return ` < ${value}`;
case 'contains':
return ` LIKE '%${value}%'`;
case 'starts_with':
return ` LIKE '${value}%'`;
case 'ends_with':
return ` LIKE '%${value}'`;
case 'not_contains':
return ` NOT LIKE '%${value}%'`;
case 'present':
return ` IS NOT NULL`;
case 'not_equal':
return ` != ${value}`;
case 'blank':
return ` = ''`;
case 'equal':
return ` = ${value}`;
case 'includes_all':
return ` @> ${value}`;
case 'in':
return typeof value === 'string'
? ` IN ${value.split(',').map((elem) => elem.trim())}`
: ` IN ${value}`;
default:
throw new Error('NoMatchingOperator');
}
};
Then you will need to modify your Smart relationship route code. But that not optimal and it doesn’t work for multiple filters.
const { Schemas, parseFilter } = require('forest-express-sequelize');
router.get('/products/:product_id/relationships/buyers', (req, res, next) => {
const queryType = models.sequelize.QueryTypes.SELECT;
const limit = parseInt(req.query.page.size) || 10;
const offset = (parseInt(req.query.page.number) - 1) * limit;
const recordsGetter = new RecordsGetter(customers);
let whereSQL = `WHERE product_id = ${req.params.product_id}`;
if (req.query.filter) {
// Explaination req.query.filter = {"field":"product_email","operator":"starts_with","value":"morgan"}
const { value, operator, field} = req.query.filter;
const filterToPartialSQL = formatOperatorValue(operator, value);
whereSQL += ` AND ${field} ${filterToPartialSQL}'`;
// Explaination whereSQL = WHERE product_id = ${req.params.product_id} AND product_email like 'morgan%'
}
let countQuery = `
SELECT COUNT(*)
FROM customers
JOIN orders ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id
${whereSQL};
`;
let dataQuery = `
SELECT customers.*
FROM customers
JOIN orders ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id
${whereSQL}
LIMIT ${limit}
OFFSET ${offset}
`;
return Promise
.all([
models.sequelize.query(countQuery, { type: queryType }),
models.sequelize.query(dataQuery, { type: queryType })
])
.then(([count, customers]) => recordsGetter.serialize(customers, { count: count[0].count }))
.then((customers) => res.send(customers))
.catch((err) => next(err));
});
Another solution is to use parseFilter which already makes all the work and translates it to SQL.
But it won’t work with OR operator. It would requires more work to translate it.
const { Schemas, parseFilter } = require('forest-express-sequelize');
router.get('/products/:product_id/relationships/buyers', (req, res, next) => {
const queryType = models.sequelize.QueryTypes.SELECT;
const limit = parseInt(req.query.page.size) || 10;
const offset = (parseInt(req.query.page.number) - 1) * limit;
const recordsGetter = new RecordsGetter(customers);
let whereSQL = `WHERE product_id = ${req.params.product_id}`;
if (req.query.filter) {
// Compute raw filters in sequelize where queries
const parsedWhere = await parseFilter(
JSON.parse(req.query.filters),
Schemas.schemas.activity,
req.query.timezone,
);
// Explaination req.query.filter = {"field":"user_email","operator":"starts_with","value":"morgan"}
// Explaination parsedWhere = { email: { [Symbol(like)]: 'morgan%' } }
for (const [column, operation] of Object.entries(parsedWhere)) {
const [operator, value] = Object.entries(operation)[0];
whereSQL += ` AND ${column} ${Symbol.keyFor(operator)} '${value}'`;
}
// Explaination whereSQL = WHERE product_id = ${req.params.product_id} AND email like 'morgan%'
}
let countQuery = `
SELECT COUNT(*)
FROM customers
JOIN orders ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id
${whereSQL};
`;
let dataQuery = `
SELECT customers.*
FROM customers
JOIN orders ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id
${whereSQL}
LIMIT ${limit}
OFFSET ${offset}
`;
return Promise
.all([
models.sequelize.query(countQuery, { type: queryType }),
models.sequelize.query(dataQuery, { type: queryType })
])
.then(([count, customers]) => recordsGetter.serialize(customers, { count: count[0].count }))
.then((customers) => res.send(customers))
.catch((err) => next(err));
});
In my humble opinion, the best and simplest solution is to convert your raw SQL queries to Sequelize one and use the proposed solution on this thread.
The following example handles both filtering and searching on HasMany Smart Relationship
const { Schemas, parseFilter, RecordsGetter } = require('forest-express-sequelize');
...
router.get('/products/:recordId/relationships/customers', (request, response, next) => {
try {
const limit = parseInt(request.query.page.size, 10) || 20;
const offset = (parseInt(request.query.page.number, 10) - 1) * limit;
const recordsGetter = new RecordsGetter(models.customers);
// Fake base query for the example
let where = { [Op.or]: [{ someField: recordId }, { anotherField: anotherId }] };
// Handle filtering
const filters = request.query.filters
if (filters) {
const parsedWhere = await parseFilter(JSON.parse(filters), Schemas.schemas.customers, request.query.timezone);
where = { [Op.and]: [parsedWhere, where] };
}
// Handle search
const search = request.query.search;
if (search) {
// Fake seach on firstName or lastName or email match %search%
const { Op } = Sequelize;
const whereSearch = {};
const searchCondition = { [Op.iLike]: `%${search}%` };
whereSearch[Op.or] = [
{ firstName: searchCondition },
{ lastName: searchCondition },
{ email: searchCondition },
];
where = { [Op.and]: [whereSearch, where] };
}
const { count, rows } = await models.customers.findAndCountAll({ where, offset,
limit });
const customersToSend = await recordsGetter.serialize(rows, { count });
response.send(customersToSend);
} catch (e) {
next(e);
}
});
Let me know if it helps.
Kind regards,
Morgan