Cannot Search on a smart field with two joints

Help me!
1

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 || [];
        query.include.push({
          model: geolocations,
          as: "geolocation",
          include: [
            {
              model: cities,
              as: "city",
            },
          ],
        });
        query.where[Op.and][0][Op.or].push(
          literal(`"geolocation->city"."name" ILIKE '%${search}%'`),
        );
      },

Actual behavior

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

Failure Logs

image
image2880×720 750 KB

Context

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
2

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.
Morgan

3

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.

Regards,
Morgan

4

Hello,

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'}] }
  ]
  query.where[Op.and][0][Op.or].push(
    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

5

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.

6

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

7

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