# Filtering & Search a HasMany Smart Relationship

**URL:** <https://community.forestadmin.com/t/filtering-search-a-hasmany-smart-relationship/4940>\
**Category:** Help me!\
**Created:** [July 6, 2022, 8:20am UTC](https://community.forestadmin.com/t/filtering-search-a-hasmany-smart-relationship/4940 "2022-07-06T08:20:35Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![daniel.garcia](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/daniel.garcia/32/4743_2.png) [@daniel.garcia](https://community.forestadmin.com/u/daniel.garcia)\
**Post date:** [July 6, 2022, 8:20am UTC](https://community.forestadmin.com/t/filtering-search-a-hasmany-smart-relationship/4940/1 "2022-07-06T08:20:35Z")

</div>

Hi everyone!  
Could you please help me with this?

## Feature(s) impacted

Smart Relationships Filter & Search

## Observed behavior

When I am in the related data (corresponding to my smart relationship) of my collection, I can view all the results but the search and filtering don’t work.

## Expected behavior

We need to filter and search a specific result in the related data view (corresponding to my smart relationship)

## Context

We would like to use the filters and search in a HasMany Smart Relationship which is in the Related Data of a collection record. The Smart Relationship has been declared as in the case of Option2: using raw SQ: [https://docs.forestadmin.com/documentation/v/v5/reference-guide/relationships/create-a-smart-relationship#option2-using-raw-sql](https://docs.forestadmin.com/documentation/v/v5/reference-guide/relationships/create-a-smart-relationship#option2-using-raw-sql)

Thanks in advance!

---

<div class="post-metadata">

**Author:** ![morganperre](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/morganperre/32/1296_2.png) [@morganperre](https://community.forestadmin.com/u/morganperre)\
**Post date:** [July 6, 2022, 10:01am UTC](https://community.forestadmin.com/t/filtering-search-a-hasmany-smart-relationship/4940/2 "2022-07-06T10:01:29Z")

</div>

Hello @daniel.garcia ! Welcome to our community. 🎊

> [@daniel.garcia](#):
>
> We would like to use the filters and search in a HasMany Smart Relationship which is in the Related Data of a collection record. The Smart Relationship has been declared as in the case of Option2: using raw SQL

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…**
> 
> ```javascript
> 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.
> 
> ```javascript
> 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.
> 
> ```javascript
> 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](https://docs.forestadmin.com/documentation/v/v5/reference-guide/relationships/create-a-smart-relationship#option-1-using-sequelize-orm) and [use the proposed solution on this thread](https://community.forestadmin.com/t/filtering-a-hasmany-smart-relationship/3747).

The following example handles both **filtering** and **searching** on HasMany Smart Relationship

```javascript
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