Search in a smart field (with polymorphic relation)

JeremyV · October 12, 2021, 12:22pm

morganperre:

query.where[Op.and][0][Op.or].push(
           Sequelize.literal(
`INNER JOIN roles ON integrations.id = roles.resource_id
  INNER JOIN identities_roles ON identities_roles.role_id = roles.id
  INNER JOIN identities ON identities.id = identities_roles.identity_id
  WHERE roles.name = 'coowner_manager' AND roles.resource_type = 'Place'
  AND (identities.first_name LIKE ${split[0]} OR identities.last_name LIKE ${split[0]} OR identities.first_name LIKE ${split[1]} OR identities.last_name LIKE ${split[1]})
`);

It cannot work this way because the where object is supposed to be after the WHERE clause.
It builds something like this:
SELECT ... FORM ... WHERE ... INNER JOIN ... WHERE...
and results as a syntax error, obviously.

JeremyV · October 12, 2021, 12:25pm

I may have found something interesting here, about Sequelize limitations:

Edit : will try something like that

const res = await bank.findAll({
        include: [
            {
                model: account,
                association: new HasMany(bank, account, {/*options*/}),
            },
        ],
    })

morganperre · October 12, 2021, 12:32pm

Sorry, I just put the code snippet as an example. I didn’t write the sub query expected by sequelize…

Ok, we will still investigate this use case on our end. I think we can make it happen on a Smart Collection using raw SQL queries but you will loose the heart of the Places collection since you will have a SmartPlaces with the possibility to create yourself the exact result you want.

Really sorry that we did not have much resources on this.

JeremyV · October 12, 2021, 12:58pm

Moving forward, I think I got something:

search: (query, search) => {
      const split = search.split(' ');

      query.include.push({
        model: models.roles,
        association: models.integrations.hasMany(models.roles, {
          foreignKey: {
            field: 'resource_id',
          },
        }),
        where: {
          resource_type: 'Place',
          name: 'coowner_manager',
        },
        include: [{
          model: models.identities,
          as: 'identitiesThroughIdentitiesRoles',
          required: true,
          where: {
            [Op.or]: [
              { '$identitiesThroughIdentitiesRoles.last_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$identitiesThroughIdentitiesRoles.last_name$': { [Op.iLike]: `%${split[1]}%` } },
              { '$identitiesThroughIdentitiesRoles.first_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$identitiesThroughIdentitiesRoles.first_name$': { [Op.iLike]: `%${split[1]}%` } },
            ]
          },
        }],
      });
      return query;
    }

Now I got:
[forest] 🌳🌳🌳 Unexpected error: missing FROM-clause entry for table "identitiesThroughIdentitiesRoles"

If we can tackle this last bit, I’m pretty sure it’s a win.

Table is called identities, collection is called Identities.
fields are first_name/last_name in the DB, firstName and lastName in the collection.

Relation between roles and identities is defined as so:

  Roles.associate = (models) => {
    Roles.belongsToMany(models.identities, {
      through: 'identitiesRoles',
      foreignKey: 'role_id',
      otherKey: 'identity_id',
      as: 'identitiesThroughIdentitiesRoles',
    });
  };

morganperre · October 12, 2021, 1:30pm

Really glad you come up with something @JeremyV !!!

Ok. First what happen if you remove the last part ? Does it return only the places that match the first where: {resource_type: 'Place', name: 'coowner_manager' } ?

Part to remove just for testing purpose.

include: [{
          model: models.identities,
          as: 'identitiesThroughIdentitiesRoles',
          required: true,
          where: {
            [Op.or]: [
              { '$identitiesThroughIdentitiesRoles.last_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$identitiesThroughIdentitiesRoles.last_name$': { [Op.iLike]: `%${split[1]}%` } },
              { '$identitiesThroughIdentitiesRoles.first_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$identitiesThroughIdentitiesRoles.first_name$': { [Op.iLike]: `%${split[1]}%` } },
            ]
          },

JeremyV · October 12, 2021, 1:32pm

It doesn’t crash anymore but returns no results, whether I put required : true or not

morganperre · October 12, 2021, 1:36pm

That’s a starting point.

Should you have results at this point ?

Also, could you put the DEBUG=sequelize* again and paste the query executed ?

I haven’t the time to recreate your DB/use case today but I will asap in order to try on my side.

JeremyV · October 12, 2021, 1:49pm

I should indeed have results…

Here is the generated query:

SELECT 
"integrations".*, 
"roles"."id" AS "roles.id", 
"roles"."name" AS "roles.name", 
"roles"."resource_type" AS "roles.resourceType", 
"roles"."resource_id" AS "roles.resourceId", 
"roles"."created_at" AS "roles.createdAt", 
"roles"."updated_at" AS "roles.updatedAt", 
"roles"."resource_id" AS "roles.contractIdKey", 
"roles"."resource_id" AS "roles.integrationId" 

FROM (SELECT 
"integrations"."id", 
"integrations"."display_name" AS "displayName", 
"integrations"."score", 
"integrations"."created_at" AS "createdAt", 
"integrations"."updated_at" AS "updatedAt", 
"integrations"."number_of_lots" AS "numberOfLots", 
"integrations"."number_of_buildings" AS "numberOfBuildings", 
"integrations"."sergic_id" AS "sergicId", 
"integrations"."sergic_update_time" AS "sergicUpdateTime", 
"integrations"."open_report_count" AS "openReportCount", 
"integrations"."ics_code" AS "icsCode", 
"integrations"."picture_name" AS "pictureName", 
"integrations"."sergic_offer" AS "sergicOffer", 
"integrations"."sergic_id_full" AS "sergicIdFull", 
"integrations"."individualized", 
"integrations"."separated", 
"integrations"."temporary", 
"integrations"."invite_status" AS "inviteStatus", 
"integrations"."invite_url" AS "inviteUrl", 
"integrations"."wp_code" AS "wpCode", 
"integrations"."synchronized_with_sergic_at" AS "synchronizedWithSergicAt", "integrations"."fiscal_year_month" AS "fiscalYearMonth", 
"integrations"."description", "integrations"."support_24_7" AS "support247", "integrations"."work_advance" AS "workAdvance", 
"integrations"."first_regulation" AS "firstRegulation", "integrations"."siret", "integrations"."service_residence" AS "serviceResidence", 
"integrations"."cooperative_syndicate" AS "cooperativeSyndicate", 
"integrations"."principal_syndicate" AS "principalSyndicate", 
"integrations"."construction_period" AS "constructionPeriod", 
"integrations"."can_talk" AS "canTalk", 
"integrations"."integration_state" AS "integrationState", 
"integrations"."historical_monument" AS "historicalMonument", 
"integrations"."loan", 
"integrations"."registration_number" AS "registrationNumber", 
"integrations"."kind", 
"integrations"."order_peril_start_date" AS "orderPerilStartDate", 
"integrations"."order_peril_end_date" AS "orderPerilEndDate", "integrations"."dtg", "integrations"."dpe", "integrations"."general_meeting_date" AS "generalMeetingDate", "integrations"."difficulty", "integrations"."energy_audit" AS "energyAudit", "integrations"."deleted_at" AS "deletedAt", "integrations"."coownership_registration_date" AS "coownershipRegistrationDate", "integrations"."agency_id" AS "agencyIdKey" FROM "public"."places" AS "integrations" 

WHERE (
lower("integrations"."display_name")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."ics_code")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."picture_name")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."sergic_id_full")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."invite_url")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."wp_code")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."description")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."siret")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."registration_number")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."dpe")  LIKE  lower('%COLLAB%')) AND ("integrations"."integration_state" != 'done' 
AND "integrations"."integration_state" != 'pending_lost' 
AND "integrations"."integration_state" != 'lost' AND "integrations"."integration_state" != 'awaiting_archives') 
AND ( SELECT "resource_id" 

FROM "public"."roles" AS "roles" 

WHERE (("roles"."resource_type" = 'Place' 
AND "roles"."name" = 'coowner_manager') 
AND "roles"."resource_id" = "integrations"."id") LIMIT 1 ) IS NOT NULL 

ORDER BY "integrations"."id" DESC LIMIT 15 OFFSET 0) AS "integrations" 
INNER JOIN "public"."roles" AS "roles" ON "integrations"."id" = "roles"."resource_id" 
AND "roles"."resource_type" = 'Place' 
AND "roles"."name" = 'coowner_manager' 

ORDER BY "integrations"."id" DESC;

morganperre · October 12, 2021, 2:06pm

JeremyV:

( SELECT "resource_id" 

FROM "public"."roles" AS "roles" 

WHERE (("roles"."resource_type" = 'Place' 
AND "roles"."name" = 'coowner_manager') 
AND "roles"."resource_id" = "integrations"."id") LIMIT 1 ) IS NOT NULL

Could the %COLLAB% bride results ? Did you make you search with a filter ?

The query looks ok for the remaining parts.

JeremyV · October 12, 2021, 2:08pm

At this point, it’s only natural that the query returns no results, because the string doesn’t match any constraint. To make it so, I need to add the identities.first_name clause in order to have actual results.
That bring us back to my previous point above.

morganperre · October 12, 2021, 2:36pm

(
lower("integrations"."display_name")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."ics_code")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."picture_name")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."sergic_id_full")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."invite_url")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."wp_code")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."description")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."siret")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."registration_number")  LIKE  lower('%COLLAB%') 
OR lower("integrations"."dpe")  LIKE  lower('%COLLAB%')) AND ...

The problem is that the SELECT * FROM "public"."places" AS "integrations" will never return any result with the actual WHERE clause… Because it will never hit a condition from the OR clause.

Thank you for all the tests you have done today. I will try to setup all this tomorrow. We are just missing some bits of sequelize.

Kind regards,
Morgan

JeremyV · October 13, 2021, 2:20pm

Hello @morganperre, did you have the opportunity to setup my models today ? Thank you.

morganperre · October 14, 2021, 8:30am

Hello @JeremyV,

I didn’t had the opportunity yesterday but this morning yes.

I have a solution that can overcome the problem using Smart Collection (but is has some downsides). I still have nothing with the traditional way (SmartField on normal collection).

I will do another try this afternoon.

Kind regards,
Morgan

morganperre · October 20, 2021, 3:16pm

Hey @JeremyV,

I’m pleased to announce that we released a fix on a new version of forest-express-sequelize v7

Thanks to you, this fix allow to use async search function for Smart Fields.

So the following code should now works for you.

search: async (query, search) => {
      const { QueryTypes, Op } = models.objectMapping;
      const split = search.split(' ');
      
      // NOTICE: complexe raw query to gather all ids from the places collection that match your business needs
      const placesIds = await models.connections.default.query(`
  SELECT places.id
  FROM places
  INNER JOIN roles ON places.id = roles.resource_id
  INNER JOIN identities_roles ON identities_roles.role_id = roles.id
  INNER JOIN identities ON identities.id = identities_roles.identity_id
  WHERE roles.name = 'coowner_manager' AND roles.resource_type = 'Place'
  AND (identities.first_name LIKE '%${split[0]}%'
       OR identities.last_name LIKE '%${split[0]}%'
       OR identities.last_name LIKE '%${split[1]}%'
       OR identities.last_name LIKE '%${split[1]}%')
        `, { type: QueryTypes.SELECT }); 

      // NOTICE: fill the where condition with a simple `id in (ArrayOfIds)` to match ids returned by the complexe query making the polymorphic joins
      const searchCondition = { id: { [Op.in]: placesIds.map(a => a.id) } };

      query.where[Op.and][0][Op.or].push(searchCondition);
}

Tell me if it works for you.

Kind regards,
Morgan

JeremyV · October 21, 2021, 8:21am

hallelujah-bigbangtheory

Hallelujah !

Bless you @morganperre and @Steve_Bunlon

(although I just added a small “return query” at the end)

morganperre · October 21, 2021, 9:08am

@JeremyV it was a pleasure. Thank for all your investigation time.

Kind regards,
Morgan

PS: The return is not needed on the smart field search function, I’ve triple check that one.

JeremyV · February 18, 2022, 11:14am

Hi @morganperre and @Steve_Bunlon

I’m kind of reopening this issue because what is working like a charm in Dev and Staging environment does NOT in Production…

field: 'gestionnaire',
    type: 'String',
    get: place => models.roles.findOne({
      where: {
        name: 'coowner_manager',
        resource_type: 'Place',
        resource_id: place.id
      },
      include: [{
        model: models.identities,
        as: 'identitiesThroughIdentitiesRoles'
      }]
    }).then(role => `${role ? role.identitiesThroughIdentitiesRoles[0].firstName : ''} ${role ? role.identitiesThroughIdentitiesRoles[0].lastName.toUpperCase() : ''}`),
    search: async (query, search) => {
      const { QueryTypes, Op } = models.objectMapping;
      const split = search.split(' ');

      // NOTICE: complexe raw query to gather all ids from the places collection that match your business needs
      const placesIds = await models.connections.default.query(`
      SELECT places.id FROM places
      INNER JOIN roles ON places.id = roles.resource_id
      INNER JOIN identities_roles ON identities_roles.role_id = roles.id
      INNER JOIN identities ON identities.id = identities_roles.identity_id
      WHERE roles.name = 'coowner_manager'
      AND roles.resource_type = 'Place'
      AND (identities.first_name ILIKE '%${split[0]}%'
      OR identities.last_name ILIKE '%${split[0]}%'
      OR identities.last_name ILIKE '%${split[1]}%'
      OR identities.last_name ILIKE '%${split[1]}%')`,
        { type: QueryTypes.SELECT });

      // NOTICE: fill the where condition with a simple `id in (ArrayOfIds)` to match ids returned by the complexe query making the polymorphic joins
      const searchCondition = { id: { [Op.in]: placesIds.map(a => a.id) } };
      query.where[Op.and][0][Op.or].push(searchCondition);
    }

Although the query in itself return results in DBeaver:

(yes this is our Production Database)

NB: We have this ‘gestionnaire’ smart field on several collections, for which search is working EVERYWHERE on Dev/Staging env and NOWHERE in Production.

morganperre · February 23, 2022, 1:54pm

Hello @JeremyV,

Sorry for the delay, I was in vacation. It’s really confusing indeed.

First thing first, I see a typo in your code (I made the typo in my example…)

JeremyV:

      AND (identities.first_name ILIKE '%${split[0]}%'
      OR identities.last_name ILIKE '%${split[0]}%'
      OR identities.last_name ILIKE '%${split[1]}%'
      OR identities.last_name ILIKE '%${split[1]}%')`

      AND (identities.first_name ILIKE '%${split[0]}%'
      OR identities.last_name ILIKE '%${split[0]}%'
      OR identities.first_name ILIKE '%${split[1]}%'
      OR identities.last_name ILIKE '%${split[1]}%')`

So let’s try to find the culprit:

Do you have the same db schema between your dev/staging environments and the production one ?
Do you have the same version of forest-express-sequelize ?
Can you provide some logs from the production environment ?
Can you add some log to see what the raw query return (in the production environment) ?

Kind regards,
Morgan

JeremyV · February 23, 2022, 2:11pm

I have no reason to think otherwise.

My prod code is merged from my dev/staging environment, so yes.

I can but without switching to verbose log there is not much to say.

I’ll do that after working hours so I will not disturb users’ activity.

JeremyV · February 23, 2022, 2:24pm

morganperre:

First thing first, I see a typo in your code (I made the typo in my example…)

JeremyV:

>       AND (identities.first_name ILIKE '%${split[0]}%'
      OR identities.last_name ILIKE '%${split[0]}%'
      OR identities.last_name ILIKE '%${split[1]}%'
      OR identities.last_name ILIKE '%${split[1]}%')`
> ```

      AND (identities.first_name ILIKE '%${split[0]}%'
      OR identities.last_name ILIKE '%${split[0]}%'
      OR identities.first_name ILIKE '%${split[1]}%'
      OR identities.last_name ILIKE '%${split[1]}%')`

This has been fixed and isn’t related. The search would have returned no result if :

the first search term ${split[0]} was incorrectly spelled
the second search term was the first_name

It’s a very seldom case.

Topic		Replies	Views
Searching on an other-table based Smart Field Help me!	23	709	February 10, 2021
Smart fields filters Help me!	1	349	October 8, 2021
How do I perform a search on a smart field? Help me! setup	4	463	August 5, 2021
I can't do a search in a smart field Help me!	1	317	December 1, 2021
Search in a reference field which is a smart field Help me!	1	289	November 5, 2021

Search in a smart field (with polymorphic relation)

Related topics