Cannot Search on a smart field with two joints

Expected behavior

I’m trying to replicate a search on Smart Field with two joints, with the following db relation: Event belonging to Geolocation, belonging to City, trying to search on city name

Events.belongsTo(models.geolocations, {
      foreignKey: {
        name: "geolocationIdKey",
        field: "geolocation_id",
      as: "geolocation",

Geolocations.belongsTo(models.cities, {
      foreignKey: {
        name: "cityIdKey",
        field: "city_id",
      as: "city",

// search on the belongsTo Smart Relationship
      search(query, search) {
        // ⚠️ I had to add this, as I were receiving an error about query.include being undefined
        query.include = query.include || [];
          model: geolocations,
          as: "geolocation",
          include: [
              model: cities,
              as: "city",
          literal(`"geolocation->city"."name" ILIKE '%${search}%'`),

Actual behavior

Getting an error Unexpected error: missing FROM-clause entry for table "geolocation->city"

Failure Logs


Please provide any relevant information about your setup.

  • Package Version: forest-express-sequelize@7.8.0
  • Express Version: 4.17.1
  • Sequelize Version: 5.15.2
  • Database Dialect: PG
  • Database Version: 11
  • Project Name: Shotgun Admin

Hey @Lucas_Gerard,

Thanks for this very detailed report. I just need a few more informations.
Where to you create the query object passed as a parameter of your search function ?

Also, can you paste the SQL query error form the console ?

Thank in advance.

I come back on the subject.

I think you follow this documentation.

I presume you try to use the include from Sequelize eager loading. You might be able to found more information on Sequelize documentation I think.



I have the same issue on my environment since I upgraded to forest-express-sequelize v7.9.3.

Here is my smart field search function (simplified, but still buggy)

search: (query, search) => {
  query.include = [
    { association: 'contact' },
    { association: 'estate', include: [{ association: 'address'}] }
    Sequelize.literal(`"estate->address"."street" ILIKE '%${search}%'`)
  return query

It raises the same error as Lucas_Gerard’s: ERROR: missing FROM-clause entry for table "estate->address" at character 5705

Note that the same piece of code used to work with the forest-express-sequelize v6.

It appears that the generated SQL doesn’t have the 2nd level join (address), and has an unwanted join on the employees table, not mentioned in the code…

STATEMENT:  SELECT count("leads"."id") AS "count" FROM "leads" AS "leads" LEFT OUTER JOIN "contacts" AS "contact" ON "leads"."contact_id" = "contact"."id" LEFT OUTER JOIN "estates" AS "estate" ON "leads"."estate_id" = "estate"."id" LEFT OUTER JOIN "employees" AS "owner" ON "leads"."owner_id" = "owner"."id" WHERE (lower("leads"."reference")  LIKE  lower('%test%') OR "estate->address"."street" ILIKE '%test%');

A helping hand would be appreciated.

Arthur — from Homeloop

Hey @arthur.derouge @Lucas_Gerard,

I’m able to reproduce your issue. I’ve open ticket on our end.
Rollbacking to forest-express-sequelize@7.7.0 should do the trick if this is blocking on your end, while we are working on a fix. If you are still experiencing this issue with 7.7.0, just let me know.

We will update the thread once the fix is released.

Sorry for the inconvenience.

Glad that you could reproduce the issue.
Rolling back to forest-express-sequelize@7.7.0 DOES fix the issue.

Hey @arthur.derouge @Lucas_Gerard :wave:

We’ve just released a new forest-express-sequelize@7.9.4 that include the fix for this search issue.

Let me know if everything is ok on your end after the update.

1 Like