Sorting/filtering no longer functional in related data

While everything was working just fine so far, some of my collections’ related data’s pagination doesn’t make any sense:

These are the overridden routes:

router.get('/places/:place_id/relationships/maintenanceContracts', (request, response, next) => {
  const place_id = request.params.place_id;
  const limit = parseInt(request.query.page.size, 10) || 20;
  const offset = (parseInt(request.query.page.number, 10) - 1) * limit;
  const recordsGetter = new RecordsGetter(models.maintenanceContracts);
  const where = { place_id };

  // find the maintenanceContracts for the requested page and page size
  const findAll = models.maintenanceContracts.findAll({
    where,
    offset,
    limit,
    include: [{
      model: models.maintenanceContractCategories,
      as: 'maintenanceContractCategory'
    }]
  });

  // count all maintenanceContracts for pagination
  const count = models.maintenanceContracts.count({ where });

  // resolve the two promises and serialize the response
  Promise.all([findAll, count])
    .then(([maintenanceContractsFound, maintenanceContractsCount]) => {
      const recordsWithUser = maintenanceContractsFound.map(record => {
        record.userEmail = request.user.email;
        return record;
      });
      return recordsGetter.serialize(recordsWithUser, { count: maintenanceContractsCount });
    })
    .then((recordsSerialized) => response.send(recordsSerialized))
    .catch(next);
});
router.get('/places/:place_id/relationships/serviceOrders', (request, response, next) => {
  const place_id = request.params.place_id;
  const limit = parseInt(request.query.page.size, 10) || 20;
  const offset = (parseInt(request.query.page.number, 10) - 1) * limit;
  const recordsGetter = new RecordsGetter(models.serviceOrders);
  const where = { place_id };

  // find the serviceOrders for the requested page and page size
  const findAll = models.serviceOrders.findAll({
    where,
    offset,
    limit,
    include: [{
      model: models.places,
      as: 'place'
    }, {
      model: models.identities,
      as: 'identity'
    }]
  });

  // count all serviceOrders for pagination
  const count = models.serviceOrders.count({ where });

  // resolve the two promises and serialize the response
  Promise.all([findAll, count])
    .then(([serviceOrdersFound, serviceOrdersCount]) => {
      const recordsWithUser = serviceOrdersFound.map(record => {
        // record.annualFees = record.annualFees / 100;
        record.userEmail = request.user.email;
        return record;
      });
      return recordsGetter.serialize(recordsWithUser, { count: serviceOrdersCount });
    })
    .then((recordsSerialized) => response.send(recordsSerialized))
    .catch(next);
});

Hello @JeremyV,

There is no smart relationship here, isn’t it?
Can you give more details about why you are overriding routes?
Also, please give the details of your model.

Regards

// This model was generated by Lumber. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models
module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const MaintenanceContracts = sequelize.define('maintenanceContracts', {
    reference: {
      type: DataTypes.STRING,
    },
    originDate: {
      type: DataTypes.DATEONLY,
    },
    noticeInMonth: {
      type: DataTypes.INTEGER,
    },
    createdAt: {
      type: DataTypes.DATE,
    },
    updatedAt: {
      type: DataTypes.DATE,
    },
    sergicId: {
      type: DataTypes.INTEGER,
    },
    sergicUpdateTime: {
      type: DataTypes.DATE,
    },
    sergicPlaceId: {
      type: DataTypes.INTEGER,
    },
    maintenanceContractCategorySergicSlug: {
      type: DataTypes.STRING,
    },
    repartitionKeyId: {
      type: DataTypes.INTEGER,
    },
    automaticRenewal: {
      type: DataTypes.BOOLEAN,
    },
    state: {
      type: DataTypes.ENUM([
        'active',
        'canceled',
        'pending_cancellation',
        'pending',
        'completed'
      ]),
    },
    comment: {
      type: DataTypes.STRING,
    },
    mriScheduleKind: {
      type: DataTypes.ENUM([
        'monthly',
        'quarterly',
        'yearly'
      ]),
    },
    kind: {
      type: DataTypes.ENUM([
        'maintenance',
        'insurance'
      ]),
    },
    endDate: {
      type: DataTypes.DATEONLY,
    },
    amount: {
      type: DataTypes.INTEGER,
    },
  }, {
    tableName: 'maintenance_contracts',
    underscored: true,
    schema: process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  MaintenanceContracts.associate = (models) => {
    MaintenanceContracts.belongsTo(models.maintenanceContractCategories, {
      foreignKey: {
        name: 'maintenanceContractCategoryIdKey',
        field: 'maintenance_contract_category_id',
      },
      as: 'maintenanceContractCategory',
    });
    MaintenanceContracts.belongsTo(models.providers, {
      foreignKey: {
        name: 'providerIdKey',
        field: 'provider_id',
      },
      as: 'provider',
    });
    MaintenanceContracts.belongsTo(models.places, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'place',
    });
    MaintenanceContracts.belongsTo(models.integrations, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'integration',
    });
    MaintenanceContracts.hasMany(models.mriDeliveryDates, {
      foreignKey: {
        name: 'maintenanceContractIdKey',
        field: 'maintenance_contract_id',
      },
      as: 'maintenanceContractMriDeliveryDates',
    });
    MaintenanceContracts.hasMany(models.maintenanceContractDocuments, {
      foreignKey: {
        name: 'maintenanceContractIdKey',
        field: 'maintenance_contract_id',
      },
      as: 'maintenanceContractMaintenanceContractDocuments',
    });
  };

  return MaintenanceContracts;
};
// This model was generated by Lumber. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models
module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const ServiceOrders = sequelize.define('serviceOrders', {
    kind: {
      type: DataTypes.ENUM([
        'with_quotation',
        'without_quotation',
        'quotation_request'
      ]),
      allowNull: false,
    },
    quotationNumber: {
      type: DataTypes.INTEGER,
    },
    amount: {
      type: DataTypes.INTEGER,
    },
    interventionDate: {
      type: DataTypes.DATE,
      allowNull: false,
    },
    interventionTitle: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    description: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    createdAt: {
      type: DataTypes.DATE,
    },
    updatedAt: {
      type: DataTypes.DATE,
    },
  }, {
    tableName: 'service_orders',
    underscored: true,
    schema: process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  ServiceOrders.associate = (models) => {
    ServiceOrders.belongsTo(models.places, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'place',
    });
    ServiceOrders.belongsTo(models.providers, {
      foreignKey: {
        name: 'providerIdKey',
        field: 'provider_id',
      },
      as: 'provider',
    });
    ServiceOrders.belongsTo(models.identities, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'identity',
    });
  };

  return ServiceOrders;
};

I use overridden routes for theses smart fields :

collection('serviceOrders', {
  actions: [],
  fields: [{
    field: 'role',
    type: 'String',
    get: serviceOrder => axios.get(`${API_URL}/forest_admin/places/${serviceOrder.placeIdKey}/top_place_role/${serviceOrder.identityIdKey}`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': serviceOrder.userEmail
      },
    }).then(res => res.data.display_place_role)
  }, {
    field: 'nom fournisseur',
    type: 'String',
    get: serviceOrder => models.providers.findOne({
      where: { id: serviceOrder.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': serviceOrder.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider.matricule
      }
    }).then(res => res.data[0].raison_sociale))
  }, {
    field: 'email fournisseur',
    type: 'String',
    get: serviceOrder => models.providers.findOne({
      where: { id: serviceOrder.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': serviceOrder.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider.matricule
      }
    }).then(res => res.data[0].email))
  }, {
    field: 'telephone fournisseur',
    type: 'String',
    get: serviceOrder => models.providers.findOne({
      where: { id: serviceOrder.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': serviceOrder.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider.matricule
      }
    }).then(res => res.data[0].telephone))
  }, {
    field: 'adresse',
    type: 'String',
    get: serviceOrder => models.providers.findOne({
      where: { id: serviceOrder.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': serviceOrder.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider.matricule
      }
    }).then(res => res.data[0].site_adresse))
  }]
collection('maintenanceContracts', {
  actions: [],
  fields: [{
    field: 'titre',
    type: 'String',
    get: maintenanceContract => models.providers.findOne({
      where: { id: maintenanceContract.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider ? provider.matricule : ''
      }
    }).then(res => {
      return `${res.data[0].raison_sociale} - ${maintenanceContract.reference}`;
    }))
  }, {
    field: 'Fournisseur',
    type: 'String',
    get: maintenanceContract => models.providers.findOne({
      where: { id: maintenanceContract.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider ? provider.matricule : ''
      }
    }).then(res => {
      return `${res.data[0].raison_sociale} - ${res.data[0].site_ville}`;
    }))
  }, {
    field: 'raison sociale',
    type: 'String',
    get: maintenanceContract => models.providers.findOne({
      where: { id: maintenanceContract.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider ? provider.matricule : ''
      }
    }).then(res => res.data[0].raison_sociale))
  
  }, {
    field: 'code postal',
    type: 'String',
    get: maintenanceContract => models.providers.findOne({
      where: { id: maintenanceContract.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider ? provider.matricule : ''
      }
    }).then(res => res.data[0].site_codepostal))
  }, {
    field: 'ville',
    type: 'String',
    get: maintenanceContract => models.providers.findOne({
      where: { id: maintenanceContract.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider ? provider.matricule : ''
      }
    }).then(res => res.data[0].site_ville))
  }, {
    field: 'telephone',
    type: 'String',
    get: maintenanceContract => models.providers.findOne({
      where: { id: maintenanceContract.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider ? provider.matricule : ''
      }
    }).then(res => res.data[0].telephone))
  }, {
    field: 'email',
    type: 'String',
    get: maintenanceContract => models.providers.findOne({
      where: { id: maintenanceContract.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider ? provider.matricule : ''
      }
    }).then(res => res.data[0].email))
  }, {
    field: 'iban',
    type: 'String',
    get: maintenanceContract => models.providers.findOne({
      where: { id: maintenanceContract.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider ? provider.matricule : ''
      }
    }).then(res => res.data[0].iban))
  }, {
    field: 'bic',
    type: 'String',
    get: maintenanceContract => models.providers.findOne({
      where: { id: maintenanceContract.providerIdKey }
    }).then(provider => axios.get(`${API_URL}/forest_admin/sage_providers`, {
      headers: {
        'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
        'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
      },
      params: {
        provider_name: '',
        siret: '',
        matricule: provider ? provider.matricule : ''
      }
    }).then(res => res.data[0].bic))
  }, {
    field: 'Contrat',
    type: 'File',
    get: maintenanceContract => models.attachmentables.findOne({
      where: {
        attachmentableId: maintenanceContract.id,
        attachmentable_type: 'MaintenanceContract'
      },
      include: [{
        model: models.attachments,
        as: 'attachment'
      }]
    }).then(attachmentable => {
      if (attachmentable && attachmentable.attachment && attachmentable.attachment.id) {
        const id = attachmentable.attachment.id;
        return axios.get(`${API_URL}/forest_admin/attachments/${id}`, {
          headers: {
            'Authorization': `Bearer ${process.env.ACCESS_TOKEN}`,
            'X-CURRENT-USER-EMAIL': maintenanceContract.userEmail
          },
        }).then(res => {
          return res.data.url;
        });
      }
    })   
  }],
  segments: [],
});

Each time i need to retrieve the userEmail from the record.

And no smart relationship here.

Hello @JeremyV,

Could you please check the network tab of you developper tools for me?

When displaying the related data, there should be a call to get the data (something like /forest/places/42/relationships/maintenanceContracts) and another one for the count (like /forst/places/42/relationships/maintenanceContracts/count).

Can you confirm that you see both these calls?

Could you also check the response for the count request? Are its parameters coherent with the other call?
As you are not using a Smart Collection, it is the one returning the actual count.

I got both requests


I also noticed that sorting is not working anymore

Filters don’t work anymore neither

Juste to let you know, I just realized that because my related data is called 8 - Contrats, it’s perfectly natural that 41 8 - Contrats is displayed on the page. This is not an issue and a mistake on my part.

HOWEVER, sorting and filtering no longer working definitively are.

Hello @JeremyV,

Good catch on the collection name, sorry I missed that.

For the sorting and filtering issue, I think it comes from the way you handle the request via Sequelize. The sort and filters from the request are not passed to the findAll call.

You can either handle it manually in your routes, or you can try to use our RecordsGetter.
The code bellow is a bit hacky to add filtering on the source collection but works with my test.

It might not be enough for you as you are also using “include” clauses in you Sequelize findAll call.
A possibility is to add an intermediate step, between records.map and recordsGetter.serialize to fetch extra data and add it to the records.

router.get('/places/:place_id/relationships/serviceOrders', (request, response, next) => {
  const client_id = request.params.client_id;
  const { query, user } = request;

  const userFilter = JSON.stringify({ field: 'place:id', operator: 'equal', value: client_id });
  let filters = userFilter;
  if (query.filters) {
    filters = `{"aggregator":"and","conditions":[${query.filters},${userFilter}]}`;
  }

  const recordsGetter = new RecordsGetter(models.serviceOrders, user, { ...query, filters });

  recordsGetter.getAll()
    .then(records => records.map(
      (record) => { record.userEmail = request.user.email; return record; }),
    )
    .then(records => recordsGetter.serialize(records))
    .then(recordsSerialized => response.send(recordsSerialized))
    .catch(next);
});

Hope this helps.

We’ve decided to refacto the smart fields, using a service email constant instead of the connected user email, thus we can get rid of all overridden routes made in the unique purpose of adding userEmail in the record. All this will allow us to get rid of a lot of code, and increase maintainability.

I’ll test this and will keep you informed.

Sorting and Filtering are now fully functional. Thanks @Guillaume_Deslandes @Sliman_Medini

1 Like