Searching on Smart Relationships in Related Data

Help me!
1

Expected Behavior

I have three tables involved in this issue: Customers, Buildings, Zones.

Zones.belongsTo(Buildings)
Buildings.belongsTo(Customers)

When I am selected to a customer record and am viewing related data for building, I am able to properly search and filter as expected.

Expectation is for search and filter to work when viewing zones from customers.

Actual Behavior

When I am viewing related data for zones on a customer record (smart relationship), search and filter is not possible to do.

image
image1783×821 143 KB

*img of zones in customer related data *

image
image1783×804 133 KB

img of zones with search in customer related data

From the logs, no query is fired off with the value in the search bar and everything is returned the same.

Context

Please provide any relevant information about your setup.

  • Package Version:
  • Express Version: ~4.17.1
  • Sequelize Version: ~5.15.1
  • Database Dialect: mysql
  • Database Version: 8.0.11
  • Project Name: Occuspace
2

Hello!

can you share you controller code for the relationship route?

Also, if I understand correctly, are you using a custom relationship so that zones appear twice in the related data tab, and it enables you to display different columns?

Or are the controllers for the two smart relations I’m seeing in the screenshot different? (do they filter different zones?)

3

Here is relationship code:

router.get('/customers/:customerId/relationships/customerZones', (request, response, next) => {
  const {customerId} = request.params;
  const limit = parseInt(request.query.page.size, 10) || 20;
  const offset = (parseInt(request.query.page.number, 10) - 1) * limit;
  const recordsGetter = new RecordsGetter(models.zones);
  const include = [{
    model: models.buildings,
    as: 'building',
    where: { customerIdKey: parseInt(customerId) },
  }];

  const findAll = models.zones.findAll({
    include,  // CHANGED FROM includeFindAll AND WORKS NOW
    offset,
    limit,
  });

  // count all customers for pagination
  const count = models.zones.count({ include });

  // resolve the two promises and serialize the response
  Promise.all([findAll, count])
    .then(([records, recordsCount]) =>
      recordsGetter.serialize(records, { count: recordsCount }))
    .then((recordsSerialized) => response.send(recordsSerialized))
    .catch(next);
});

The second zones relationship that appears is a proxy collection that is used to avoid loading too many columns for both performance and information overload reasons.

4

Well the issue with smart relationships, is that you have full control of the reply…

You can access the frontend filters and search parameters in the query string (request.search, request.searchExtended, and request.filters)

So if you need filters and search to work, you would need to implement them yourself on the controller (like you did with pagination), but it’s far from trivial if you do it from scratch

Forest Admin exposes services (which are largely undocumented… sorry for that) for this purpose
Here is an example which you can adapt from (using books and reviews)

const { RecordsGetter, RecordsCounter } = require('forest-express-sequelize');
const { books, reviews } = require('../models');

router.get('/books/:book_id/relationships/reviewsagain', (request, response, next) => {
  // Make filter that keeps only the reviews of book(id == X)
  let filters = JSON.stringify({
     field: 'book:id',
     operator: 'equal',
     value: request.params.book_id 
  });

  // Merge filter with the filter on the query string, if one is provided
  if (request.query.filters) {
    filters = `{"aggregator":"and","conditions":[${request.query.filters},${filters}]}`
  }

  // Get records
  const recordsGetter = new RecordsGetter(reviews);
  const findAll = recordsGetter.getAll({...request.query, filters});
  
  // Count records
  const recordsCounter = new RecordsCounter(reviews); 
  const count = recordsCounter.count({...request.query, filters});

  // resolve the two promises and serialize the response
  Promise.all([findAll, count])
    .then(([reviewFound, reviewsCount]) =>
      recordsGetter.serialize(reviewFound, { count: reviewsCount }))
    .then((recordsSerialized) => response.send(recordsSerialized))
    .catch(next);
});
1 Like