Search in a smart field (with polymorphic relation)

[JeremyV]

Can provide several examples but this one should be enough:

sequelize:pool connection released +12ms
GET /forest/integrations?timezone=Europe%2FParis&fields%5Bintegrations%5D=integrationState%2CcreatedAt%2CsergicIdFull%2CdisplayName%2CNb%20de%20copro%20connectes%2CNb%20de%20copro%20ajoutes%2CnumberOfLots%2CNb%20de%20doc%20recus%2CNb%20de%20doc%20valides%2Ccgu%20acceptees%2Cgestionnaire%2Cassistant%20copro&filters=%7B%22aggregator%22%3A%22and%22%2C%22conditions%22%3A%5B%7B%22field%22%3A%22integrationState%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22done%22%7D%2C%7B%22field%22%3A%22integrationState%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22pending_lost%22%7D%2C%7B%22field%22%3A%22integrationState%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22lost%22%7D%5D%7D&page%5Bnumber%5D=1&page%5Bsize%5D=15&search=Richard&searchExtended=0&sort=-id 200 11 - 33.241 ms
  sequelize:sql:pg Executed (default): SELECT places.id FROM places
  sequelize:sql:pg       INNER JOIN roles ON places.id = roles.resource_id
  sequelize:sql:pg       INNER JOIN identities_roles ON identities_roles.role_id = roles.id
  sequelize:sql:pg       INNER JOIN identities ON identities.id = identities_roles.identity_id
  sequelize:sql:pg       WHERE roles.name = 'coowner_assistant'
  sequelize:sql:pg       AND roles.resource_type = 'Place'
  sequelize:sql:pg       AND (identities.first_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%') +3ms
  sequelize:pool connection released +4ms
  sequelize:sql:pg Executed (default): SELECT places.id FROM places
  sequelize:sql:pg       INNER JOIN roles ON places.id = roles.resource_id
  sequelize:sql:pg       INNER JOIN identities_roles ON identities_roles.role_id = roles.id
  sequelize:sql:pg       INNER JOIN identities ON identities.id = identities_roles.identity_id
  sequelize:sql:pg       WHERE roles.name = 'coowner_manager'
  sequelize:sql:pg       AND roles.resource_type = 'Place'
  sequelize:sql:pg       AND (identities.first_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%') +1ms
  sequelize:pool connection released +1ms
  sequelize:pool connection acquired +23ms
  sequelize:pool connection acquired +0ms
  sequelize:pool connection acquired +0ms
  sequelize:sql:pg Executing (default): SELECT places.id FROM places
  sequelize:sql:pg       INNER JOIN roles ON places.id = roles.resource_id
  sequelize:sql:pg       INNER JOIN identities_roles ON identities_roles.role_id = roles.id
  sequelize:sql:pg       INNER JOIN identities ON identities.id = identities_roles.identity_id
  sequelize:sql:pg       WHERE roles.name = 'coowner_manager'
  sequelize:sql:pg       AND roles.resource_type = 'Place'
  sequelize:sql:pg       AND (identities.first_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%') +25ms
  sequelize:sql:pg Executing (default): SELECT places.id FROM places
  sequelize:sql:pg       INNER JOIN roles ON places.id = roles.resource_id
  sequelize:sql:pg       INNER JOIN identities_roles ON identities_roles.role_id = roles.id
  sequelize:sql:pg       INNER JOIN identities ON identities.id = identities_roles.identity_id
  sequelize:sql:pg       WHERE roles.name = 'coowner_assistant'
  sequelize:sql:pg       AND roles.resource_type = 'Place'
  sequelize:sql:pg       AND (identities.first_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%') +0ms
  sequelize:sql:pg Executing (default): SELECT count("integrations"."id") AS "count" FROM "public"."places" AS "integrations" WHERE ((lower("integrations"."display_name")  LIKE  lower('%Richard%') OR lower("integrations"."ics_code")  LIKE  lower('%Richard%') OR lower("integrations"."picture_name")  LIKE  lower('%Richard%') OR lower("integrations"."sergic_id_full")  LIKE  lower('%Richard%') OR lower("integrations"."invite_url")  LIKE  lower('%Richard%') OR lower("integrations"."wp_code")  LIKE  lower('%Richard%') OR lower("integrations"."description")  LIKE  lower('%Richard%') OR lower("integrations"."siret")  LIKE  lower('%Richard%') OR lower("integrations"."registration_number")  LIKE  lower('%Richard%')) AND ("integrations"."integration_state" != 'done' AND "integrations"."integration_state" != 'pending_lost' AND "integrations"."integration_state" != 'lost')); +1ms
sequelize:sql:pg Executed (default): SELECT count("integrations"."id") AS "count" FROM "public"."places" AS "integrations" WHERE ((lower("integrations"."display_name")  LIKE  lower('%Richard%') OR lower("integrations"."ics_code")  LIKE  lower('%Richard%') OR lower("integrations"."picture_name")  LIKE  lower('%Richard%') OR lower("integrations"."sergic_id_full")  LIKE  lower('%Richard%') OR lower("integrations"."invite_url")  LIKE  lower('%Richard%') OR lower("integrations"."wp_code")  LIKE  lower('%Richard%') OR lower("integrations"."description")  LIKE  lower('%Richard%') OR lower("integrations"."siret")  LIKE  lower('%Richard%') OR lower("integrations"."registration_number")  LIKE  lower('%Richard%')) AND ("integrations"."integration_state" != 'done' AND "integrations"."integration_state" != 'pending_lost' AND "integrations"."integration_state" != 'lost')); +7ms
  sequelize:pool connection released +9ms
  sequelize:sql:pg Executed (default): SELECT places.id FROM places
  sequelize:sql:pg       INNER JOIN roles ON places.id = roles.resource_id
  sequelize:sql:pg       INNER JOIN identities_roles ON identities_roles.role_id = roles.id
  sequelize:sql:pg       INNER JOIN identities ON identities.id = identities_roles.identity_id
  sequelize:sql:pg       WHERE roles.name = 'coowner_manager'
  sequelize:sql:pg       AND roles.resource_type = 'Place'
  sequelize:sql:pg       AND (identities.first_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%') +1ms
  sequelize:pool connection released +1ms
GET /forest/integrations/count?fields%5Bintegrations%5D=integrationState%2CcreatedAt%2CsergicIdFull%2CdisplayName%2CNb+de+copro+connectes%2CNb+de+copro+ajoutes%2CnumberOfLots%2CNb+de+doc+recus%2CNb+de+doc+valides%2Ccgu+acceptees%2Cgestionnaire%2Cassistant+copro&search=Richard&filters=%7B%22aggregator%22%3A%22and%22%2C%22conditions%22%3A%5B%7B%22field%22%3A%22integrationState%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22done%22%7D%2C%7B%22field%22%3A%22integrationState%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22pending_lost%22%7D%2C%7B%22field%22%3A%22integrationState%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22lost%22%7D%5D%7D&searchExtended=0&timezone=Europe%2FParis 200 11 - 20.557 ms
  sequelize:sql:pg Executed (default): SELECT places.id FROM places
  sequelize:sql:pg       INNER JOIN roles ON places.id = roles.resource_id
  sequelize:sql:pg       INNER JOIN identities_roles ON identities_roles.role_id = roles.id
  sequelize:sql:pg       INNER JOIN identities ON identities.id = identities_roles.identity_id
  sequelize:sql:pg       WHERE roles.name = 'coowner_assistant'
  sequelize:sql:pg       AND roles.resource_type = 'Place'
  sequelize:sql:pg       AND (identities.first_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%Richard%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%'
  sequelize:sql:pg       OR identities.last_name ILIKE '%undefined%') +1ms
  sequelize:pool connection released +1ms
  sequelize:pool connection destroy +496ms

and the results I expect :

[morganperre]

Thanks for your answers. I’m trying to understand what can cause the issue.

• If I get this right they just can’t search on this specific smart field ?
• But they are able to see it, am I right ?

@JeremyV

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

Thank you.

@JeremyV

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.

Yes, it just blow my eyes.

Kind regards,
Morgan

[morganperre]

Hello again,

@JeremyV

Can provide several examples but this one should be enough:

• So the query is good and returns the right results ?

• What happened on the client side ?

• Can you share the Network response from the call ?

• Looking at the request itself we can see a filter. Do you use scopes or segments to access the data ? They may have an impact on the data to display. Let me know.

filters=%7B%22aggregator%22%3A%22and%22%2C%22conditions%22%3A%5B%7B%22field%22%3A%22integrationState%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22done%22%7D%2C%7B%22field%22%3A%22integrationState%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22pending_lost%22%7D%2C%7B%22field%22%3A%22integrationState%22%2C%22operator%22%3A%22not_equal%22%2C%22value%22%3A%22lost%22%7D%5D%7D

Filter translation: (integrationState != ‘done’ && integrationState != ‘pending_lost’ && integrationState != lost)

Kind regards,
Morgan

[JeremyV]

@morganperre

If I get this right they just can’t search on this specific smart field ?

Yes, there are 2 fields which are used in several collections. These fields search doesn’t work anywhere in production, and everywhere in other environments

@morganperre

But they are able to see it, am I right ?

Indeed.

@morganperre

• So the query is good and returns the right results ?

Yes.

@morganperre

What happened on the client side ?

REMOVED CONTENT

@morganperre

Can you share the Network response from the call ?

@morganperre

Looking at the request itself we can see a filter. Do you use scopes or segments to access the data ?

Yes we’re accessing these collections via segments. But once again, all of this is working perfectly fine in Dev and Staging envs.

[JeremyV]

Found it. There was a npm install missing in prod env. Thank you for your help and advice @morganperre

[morganperre]

Glad that you find the issue.

Have a nice day.

Kind regards,
Morgan