Search in a smart field (with polymorphic relation)

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.

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*/}),
            },
        ],
    })

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.

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',
    });
  };
1 Like

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]}%` } },
            ]
          },

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

That’s a starting point. :slight_smile:

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.

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;

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

The query looks ok for the remaining parts.

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.

(
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

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

Hello @JeremyV,

I did 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