Search in a smart field (with polymorphic relation)

JeremyV · October 11, 2021, 9:48am

A few months ago, I have written a search method for a smart field.

    field: 'nom complet',
    type: 'String',
    get: contract => models.identities.findOne({
      where: { id: contract.identityIdKey }
    }).then(identity => identity ? `${identity.lastName ? identity.lastName.toUpperCase() : ''} ${identity.firstName ? identity.firstName : ''}` : ''),
    search: (query, search) => {
      const split = search.split(' ');
      const searchCondition = {
        [Op.or]: [
          { '$identity.last_name$': { [Op.iLike]: `%${split[0]}%` } },
          { '$identity.last_name$': { [Op.iLike]: `%${split[1]}%` } },
          { '$identity.first_name$': { [Op.iLike]: `%${split[0]}%` } },
          { '$identity.first_name$': { [Op.iLike]: `%${split[1]}%` } },
        ],
      };
      query.where[Op.and][0][Op.or].push(searchCondition);
      if (!query.include.find((include) => include.as === 'identity')) {
        query.include.push({
          model: models.identities,
          as: 'identity',
        });
      }
      return query;
    }

Now I need to write a new search method for a quite different field :

    field: 'gestionnaire',
    type: 'String',
    get: integration => models.roles.findOne({
      where: {
        name: 'coowner_manager',
        resource_type: 'Place',
        resource_id: integration.id
      },
      include: [{
        model: models.identities,
        as: 'identitiesThroughIdentitiesRoles'
      }]
    }).then(role => `${role ? role.identitiesThroughIdentitiesRoles[0].firstName : ''} ${role ? role.identitiesThroughIdentitiesRoles[0].lastName.toUpperCase() : ''}`)

I’ve started to write something like that

search: (query, search) => {
      const split = search.split(' ');
      const searchCondition = {
        [Op.or]: [
          { '$role.identitiesThroughIdentitiesRoles[0].last_name$': { [Op.iLike]: `%${split[0]}%` } },
          { '$role.identitiesThroughIdentitiesRoles[0].last_name$': { [Op.iLike]: `%${split[1]}%` } },
          { '$role.identitiesThroughIdentitiesRoles[0].first_name$': { [Op.iLike]: `%${split[0]}%` } },
          { '$role.identitiesThroughIdentitiesRoles[0].first_name$': { [Op.iLike]: `%${split[1]}%` } },
        ],
      };
      query.where[Op.and][0][Op.or].push(searchCondition);

But in this case I feel stuck because there is no relation between Places and Roles
hence I can’t write something such as

       query.include.push({
          model: models.roles,
          as: 'role',
        });

Can you help me for this.

morganperre · October 11, 2021, 11:06am

Hello @JeremyV,

What search do you want to implement exactly ? I didn’t understand why you need Places ?
Can you provide a better description of the relations between your models ?

Hints that could help, You can use multiple include.

 query.include.push({
   model: models.roles,
   as: 'role',
   include: [{ model: models.identities,  as: 'identitiesThroughIdentitiesRoles' }],
 });

Kind regards,
Morgan

JeremyV · October 11, 2021, 12:06pm

This smart field is in the Places collection. I’d like my users to type someone’s first name, or last name, or both, or part of it, and the collection displays the list of places managed by the matching person.

The table Roles is a polymorphic table. Hence the resource_id is not defined as a foreign key because it can be multiple tables’ids.

As a person (identity) you can have several roles in several situations.

coowner for a Contract
coownership board member for a Place
coowmership board president for another Place

So when I look for this particular place manager, I need to specify which role, which table and which id :

In my exemple above, there is a relation between Contracts and Identities

    Contracts.belongsTo(models.identities, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'identity',
    });

so in this case, it makes sense to write something such as

But there is no relation between Places and Roles. I manually write my query for this.
Never the less, I still need to make this field searchable.
So the idea is :
I gather the Place’s manager’s role, its identity, its first name and last name and I check if it matches what has been written by the user in the search field.

Once I have my role, I get the corresponding Identity as so :

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

After that :

const Identities = sequelize.define('identities', {
    firstName: {
      type: DataTypes.STRING,
    },
    lastName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
...

morganperre · October 11, 2021, 12:52pm

Hey again,

I’m not sure to get all your relations between objects.

I think you should try to do it in pure SQL at first. If you’re able to do it in SQL you will know if what you’re trying to achieve is possible.

Just a quick question do you some relations between Places and Identities ? (Even multiple relations that can link them together)

Maybe your missing a where clause in a sub include to make your where on the Identities model and use the eager loading (through multiple models) to get all related places.
Thus, you can found more information about eager loading here.

Kind regards,
Morgan

JeremyV · October 11, 2021, 1:47pm

Here’s a working 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 'Jeremy' OR identities.last_name LIKE 'Jeremy')

Here, ‘Jeremy’ is the string that has been typed in by the user

JeremyV · October 11, 2021, 2:32pm

There’s the SQL equivalent up there. But my previous examples do not use pure SQL, that’s why I’m stuck.
I’m not certain whether ig my searchCondition is correct in that context and I can’t figure out how to deal with all the query.include.find and query.include.push.
There shouldn’t be an include, because Places and Roles are not related by a FK.

Like so :
Places => Contracts => Identities
Among these identities are coowners, which are not what I’m looking for.

morganperre · October 12, 2021, 8:07am

Hello @JeremyV,

Perfect, so we need to transfert the query to the sequelize eager loading model.

The include clause need to be feed with the right informations. To create INNER JOIN you need to add required: true to the include clause.

Let’s try to write your code again using your SQL query. (The “context” is that you are on the Places collection)


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

      // Maybe completely override query.include here
      query.include.push({
        model: models.roles,
        required: true,
        where: { resource_type: 'Place', name: 'coowner_manager' },
        include: [{
          model: models.identities_roles,
          required: true
          include: [{
            model: models.identities,
            as: 'relatedIdentitie',
            required: true
            where:  { [Op.or]: [
              { '$relatedIdentitie.last_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$relatedIdentitie.last_name$': { [Op.iLike]: `%${split[1]}%` } },
              { '$relatedIdentitie.first_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$relatedIdentitie.first_name$': { [Op.iLike]: `%${split[1]}%` } },
              ]},
            }],
          }],
      });
}

Tell me if this help. Again you can found more informations here about eager loading.

Kind regards,
Morgan

JeremyV · October 12, 2021, 8:27am

Thank you @morganperre

Although what is happening here is exactly what I expected.

Unexpected error: roles is not associated to integrations!

Integrations here is just an alias for the table Places
So I guess the initial query.include cannot be used here.

Again, there is no relation between Places and Roles. I manually write my query for this.
No place_id FK in the Roles model.

EDIT : I’ve been trying a simple query.push but
[forest] 🌳🌳🌳 Cannot search properly on Smart Field gestionnaire : query.push is not a function

morganperre · October 12, 2021, 8:47am

Hum,

I have missed resource_id: integration.id. Ok for the alias. I’m really missing something here.

I’m wondering how to add the resource_id: integration.id at this stage.

I will ask a colleague to help you. Maybe with a new eye he can spot the error.

Kind regards,
Morgan

morganperre · October 12, 2021, 9:07am

Hey again @JeremyV

Can you please log the query object (just at the beginning of the search function) and paste it here ? (use JSON.stringify() if the object is to heavy to being displayed)

I want to understand what’s inside at this stage.

Also add add DEBUG=sequelize* before the command to start your agent. This will log the queries executed by sequelize.

Thanks in advance.

Morgan

JeremyV · October 12, 2021, 9:31am

Pretty straight forward :

{
    "include": [],
    "where": {}
}

You mean in the .env file ?

Edit : Here is a generated query

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

FROM "public"."places" AS "integrations" 

WHERE (
lower("integrations"."display_name")  LIKE  lower('%tipha%') 
OR lower("integrations"."ics_code")  LIKE  lower('%tipha%') 
OR lower("integrations"."picture_name")  LIKE  lower('%tipha%') 
OR lower("integrations"."sergic_id_full")  LIKE  lower('%tipha%') 
OR lower("integrations"."invite_url")  LIKE  lower('%tipha%') 
OR lower("integrations"."wp_code")  LIKE  lower('%tipha%') 
OR lower("integrations"."description")  LIKE  lower('%tipha%') 
OR lower("integrations"."siret")  LIKE  lower('%tipha%') 
OR lower("integrations"."registration_number")  LIKE  lower('%tipha%') 
OR lower("integrations"."dpe")  LIKE  lower('%tipha%')) 
AND ("integrations"."integration_state" != 'done' 
AND "integrations"."integration_state" != 'pending_lost' 
AND "integrations"."integration_state" != 'lost' 
AND "integrations"."integration_state" != 'awaiting_archives') 
ORDER BY "integrations"."id" DESC LIMIT 15 OFFSET 0; +3ms

morganperre · October 12, 2021, 9:36am

Indeed, it doesn’t help at all.

Just before running your agent.

DEBUG=sequelize* npm start

Then run can the code that lead to Unexpected error: roles is not associated to integrations

JeremyV · October 12, 2021, 9:39am

When I get the [forest] 🌳🌳🌳 Unexpected error: roles is not associated to integrations! error, no SQL query is built.

I’m wondering if I can use something like Sequelize.literal() and adding the INNER JOIN at the same time. Or maybe there is a Sequelize.literal() for the include ?

morganperre · October 12, 2021, 10:16am

We have look for a direct SQL injection but it’s seems not possible.

Is this schema representing correctly the relations of your DB (just the polymorphic part that we need in your case) ?

We are looking at a way to make the polymorphic INNER JOIN explicite (kind of resource_id: ‘$integrations.id$’) ! We will keep you updated as soon as possible.

Kind regards,
Morgan

JeremyV · October 12, 2021, 10:32am

Yes exactly @morganperre , you’ve nailed it.

morganperre · October 12, 2021, 10:35am

Perfect @JeremyV.

We end up with this potential solution.

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

      query.include.push({
        model: models.roles,
        required: true,
        where: {
          resource_type: 'Place',
          name: 'coowner_manager',
          resource_id: sequelize.col('integrations.id'),
        },
        include: [{
          model: models.identities_roles,
          required: true
          include: [{
            model: models.identities,
            as: 'relatedIdentitie',
            required: true
            where:  { [Op.or]: [
              { '$relatedIdentitie.last_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$relatedIdentitie.last_name$': { [Op.iLike]: `%${split[1]}%` } },
              { '$relatedIdentitie.first_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$relatedIdentitie.first_name$': { [Op.iLike]: `%${split[1]}%` } },
              ]},
            }],
          }],
      });
}

The important part is the INNER JOIN on models.roles. On my previous try I was missing the explicite polymorphic resource_id relation.

resource_id: sequelize.col('integrations.id')

Tell me if this one help.

Kind regards,
Morgan

JeremyV · October 12, 2021, 10:43am

Unfortunately it still does :
[forest] 🌳🌳🌳 Unexpected error: roles is not associated to integrations!
Integrations still meaning Places.

morganperre · October 12, 2021, 10:55am

Sad news. I think I understand the problem here. Sequelize is trying to make the INNER JOIN without the right ON clause since it doesn’t know about polymorphic relation.

Let try to make it happen.

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

      query.include.push({
        model: models.roles,
        required: true,
        where: {
          resource_type: 'Place',
          name: 'coowner_manager',
        },
        on:  { // Force INNER JOIN on resource_id
          resource_id: sequelize.col('integrations.id'),
        }
        include: [{
          model: models.identities_roles,
          required: true
          include: [{
            model: models.identities,
            as: 'relatedIdentitie',
            required: true
            where:  { [Op.or]: [
              { '$relatedIdentitie.last_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$relatedIdentitie.last_name$': { [Op.iLike]: `%${split[1]}%` } },
              { '$relatedIdentitie.first_name$': { [Op.iLike]: `%${split[0]}%` } },
              { '$relatedIdentitie.first_name$': { [Op.iLike]: `%${split[1]}%` } },
              ]},
            }],
          }],
      });
}

I just added the on clause the the models.roles include.

  on:  { // Force INNER JOIN on resource_id
          resource_id: sequelize.col('integrations.id'),
        }

JeremyV · October 12, 2021, 11:46am

Same old same old… I honestly think it doesn’t go any further than this :

My models definition :

// This model was generated by Lumber. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models
module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const Integrations = sequelize.define('integrations', {
    displayName: {
      type: DataTypes.STRING,
      defaultValue: "",
      allowNull: false,
    },
    score: {
      type: DataTypes.INTEGER,
    },
    createdAt: {
      type: DataTypes.DATE,
    },
    updatedAt: {
      type: DataTypes.DATE,
    },
    numberOfLots: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      allowNull: false,
    },
    numberOfBuildings: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      allowNull: false,
    },
    sergicId: {
      type: DataTypes.INTEGER,
    },
    sergicUpdateTime: {
      type: DataTypes.DATE,
    },
    openReportCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      allowNull: false,
    },
    icsCode: {
      type: DataTypes.STRING,
    },
    pictureName: {
      type: DataTypes.STRING,
    },
    sergicOffer: {
      type: DataTypes.ENUM(['DIR', 'ESE', 'VSY']),
      defaultValue: "",
    },
    sergicIdFull: {
      type: DataTypes.STRING,
    },
    individualized: {
      type: DataTypes.BOOLEAN,
    },
    separated: {
      type: DataTypes.BOOLEAN,
    },
    temporary: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    inviteStatus: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    inviteUrl: {
      type: DataTypes.STRING,
    },
    wpCode: {
      type: DataTypes.STRING,
    },
    synchronizedWithSergicAt: {
      type: DataTypes.DATE,
    },
    fiscalYearMonth: {
      type: DataTypes.INTEGER
    },
    description: {
      type: DataTypes.STRING,
    },
    support247: {
      type: DataTypes.BOOLEAN,
      field: 'support_24_7',
      defaultValue: false,
    },
    workAdvance: {
      type: DataTypes.INTEGER,
    },
    firstRegulation: {
      type: DataTypes.DATE,
    },
    siret: {
      type: DataTypes.STRING,
    },
    serviceResidence: {
      type: DataTypes.BOOLEAN,
    },
    cooperativeSyndicate: {
      type: DataTypes.BOOLEAN,
    },
    principalSyndicate: {
      type: DataTypes.BOOLEAN,
    },
    constructionPeriod: {
      type: DataTypes.ENUM([
        'before_1949',
        'from_1949_to_1960',
        'from_1961_to_1974',
        'from_1975_to_1993',
        'from_1994_to_2000',
        'from_2001_to_2010',
        'after_2011',
        'unknown'
      ]),
    },
    canTalk: {
      type: DataTypes.BOOLEAN,
      defaultValue: true,
    },
    integrationState: {
      type: DataTypes.ENUM([
        'new',                          // Nouveau : Docs.en attente
        'awaiting_document_validation',	// Documents à valider
        'management_integration',	      // Intégration gestion
        'awaiting_budget_validation',	  // Attente validation budget
        'send_report',	                // Rattachement & PV à envoyer
        'invalid_budget',	              // Budget refusé à revoir
        'sage',	                        // Intégration Comptable - SAGE
        'awaiting_archives',            // Attente archives
        'done',                         // Actif
        'pending_lost',                 // A transferer
        'lost'                          // Immeuble perdu
      ]),
    },
    historicalMonument: {
      type: DataTypes.BOOLEAN,
    },
    loan: {
      type: DataTypes.BOOLEAN,
    },
    registrationNumber: {
      type: DataTypes.STRING,
    },
    kind: {
      type: DataTypes.ENUM([
        'habitation',
        'mixed',
        'asl',
        'parking',
        'commercial'
      ])
    },
    orderPerilStartDate: {
      type: DataTypes.DATEONLY,
    },
    orderPerilEndDate: {
      type: DataTypes.DATEONLY,
    },
    dtg: {
      type: DataTypes.BOOLEAN,
    },
    dpe: {
      type: DataTypes.STRING,
    },
    generalMeetingDate: {
      type: DataTypes.DATEONLY,
    },
    difficulty: {
      type: DataTypes.BOOLEAN,
    },
    energyAudit: {
      type: DataTypes.BOOLEAN,
    },
    deletedAt: {
      type: DataTypes.BOOLEAN,
    },
    coownershipRegistrationDate: {
      type: DataTypes.DATE,
    },
  }, {
    tableName: 'places',
    underscored: true,
    schema: process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Integrations.associate = (models) => {
    Integrations.belongsTo(models.agencies, {
      foreignKey: {
        name: 'agencyIdKey',
        field: 'agency_id',
      },
      as: 'agency',
    });
    Integrations.belongsToMany(models.accountPlaces, {
      through: 'treasuries',
      foreignKey: 'place_id',
      otherKey: 'account_place_id',
      as: 'accountPlacesThroughTreasuries',
    });
    Integrations.hasMany(models.integrationBudgets, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'integrationBudgets',
    });
    Integrations.hasMany(models.parcels, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'parcels',
    });
    Integrations.hasMany(models.contracts, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'contracts',
    });
    Integrations.hasMany(models.entryCodes, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'entryCodes',
    });
    Integrations.hasMany(models.votedWorks, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'votedWorks',
    });
    Integrations.hasMany(models.reports, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'reports',
    });
    Integrations.hasMany(models.conversations, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'conversations',
    });
    Integrations.hasMany(models.maintenanceWorks, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'maintenanceWorks',
    });
    Integrations.hasMany(models.managementMandates, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'managementMandates',
    });
    Integrations.hasMany(models.maintenanceContracts, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'maintenanceContracts',
    });
    Integrations.hasMany(models.managementFees, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'managementFees',
    });
    Integrations.hasMany(models.equipment, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'equipment',
    });
    Integrations.hasMany(models.fiscalYears, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'fiscalYears',
    });
    Integrations.hasMany(models.publicWorks, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'publicWorks',
    });
    Integrations.hasMany(models.propertyRepresentatives, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'propertyRepresentatives',
    });
    Integrations.hasMany(models.alerts, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'alerts',
    });
    Integrations.hasMany(models.accountPlaceEntries, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'accountPlaceEntries',
    });
    Integrations.hasMany(models.allocatedBudgets, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'allocatedBudgets',
    });
    Integrations.hasMany(models.buildings, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'buildings',
    });
    Integrations.hasMany(models.notifications, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'notifications',
    });
    Integrations.hasMany(models.generalMeetings, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'generalMeetings',
    });
    Integrations.hasMany(models.repartitionKeys, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'repartitionKeys',
    });
    Integrations.hasMany(models.topics, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'topics',
    });
    Integrations.hasMany(models.serviceOrders, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'serviceOrders',
    });
    Integrations.hasMany(models.connectedDeviceAlerts, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'connectedDeviceAlerts',
    });
    Integrations.hasMany(models.previousManagementAgents, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'previousManagementAgents',
    });
    Integrations.hasMany(models.defaultIdentities, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'defaultIdentities',
    });
    Integrations.hasMany(models.feedEvents, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'feedEvents',
    });
    Integrations.hasMany(models.workOrders, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'workOrders',
    });
    Integrations.hasMany(models.placeEmployees, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'placeEmployees',
    });
    Integrations.hasMany(models.rssFeeds, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'rssFeeds',
    });
    Integrations.hasMany(models.coownershipFolders, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'coownershipFolders',
    });
    Integrations.hasMany(models.billers, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'billers',
    });
    Integrations.hasMany(models.decisions, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'decisions',
    });
    Integrations.hasMany(models.integrationDocuments, {
      foreignKey: {
        name: 'placeIdKey',
        field: 'place_id',
      },
      as: 'integrationDocuments',
    });
  };
  return Integrations;
};

// This model was generated by Lumber. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models
module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const Roles = sequelize.define('roles', {
    name: {
      type: DataTypes.ENUM([
        'director',
        'director_assistant',
        'coowner_accountant',
        'recovery_responsible',
        'claims_manager',
        'property_developer',
        'coowner_manager',
        'coowner_assistant',
        'sergic_partner_admin',
        'sergic_partner',
        'biller_reviewer',
        'president_board_member',
        'coownership_board_member',
        'coowner',
        'coowner_secondary',
        'lessor',
        'tenant',
        'caretaker',
        'employee',
        'representative'
      ]),
    },
    resourceType: {
      type: DataTypes.STRING,
    },
    resourceId: {
      type: DataTypes.BIGINT,
    },
    createdAt: {
      type: DataTypes.DATE,
    },
    updatedAt: {
      type: DataTypes.DATE,
    },
  }, {
    tableName: 'roles',
    underscored: true,
    schema: process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Roles.associate = (models) => {
    Roles.belongsToMany(models.identities, {
      through: 'identitiesRoles',
      foreignKey: 'role_id',
      otherKey: 'identity_id',
      as: 'identitiesThroughIdentitiesRoles',
    });
  };
  return Roles;
};

// This model was generated by Lumber. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models
module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const IdentitiesRoles = sequelize.define('identitiesRoles', {
  }, {
    tableName: 'identities_roles',
    underscored: true,
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });
  IdentitiesRoles.removeAttribute('id');
  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  IdentitiesRoles.associate = (models) => {
    IdentitiesRoles.belongsTo(models.roles, {
      foreignKey: {
        name: 'roleIdKey',
        field: 'role_id',
      },
      as: 'role',
    });
    IdentitiesRoles.belongsTo(models.identities, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'identity',
    });
  };
  return IdentitiesRoles;
};

// This model was generated by Lumber. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models
module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const Identities = sequelize.define('identities', {
    sergicId: {
      type: DataTypes.STRING,
    },
    phoneNumber: {
      type: DataTypes.STRING,
    },
    phoneChecked: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    firstName: {
      type: DataTypes.STRING,
    },
    lastName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    email: {
      type: DataTypes.STRING,
    },
    title: {
      type: DataTypes.ENUM([
        'ADM',
        'ASL',
        'ASS.',
        'BQE',
        'CAB',
        'CIE',
        'CLUB',
        'DOCT',
        'EIRL',
        'ETS',
        'EURL',
        'GIE',
        'GRPE',
        'INDI',
        'M&ME',
        'M/ME',
        'MLE',
        'MME',
        'MMES',
        'MMM',
        'MR',
        'MRS',
        'MTRE',
        'NOT.',
        'SA',
        'SARL',
        'SAS',
        'SC',
        'SCCV',
        'SCI',
        'SCM',
        'SCP',
        'SCPI',
        'SEM',
        'SNC',
        'SPE',
        'STE',
        'SUCC',
        'SYND'
      ]),
    },
    sergicUpdateTime: {
      type: DataTypes.DATE,
    },
    birthDate: {
      type: DataTypes.DATE,
    },
    invited: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    createdAt: {
      type: DataTypes.DATE,
    },
    updatedAt: {
      type: DataTypes.DATE,
    },
    realName: {
      type: DataTypes.STRING,
    },
    homePhone: {
      type: DataTypes.STRING,
    },
    representative: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    legalKind: {
      type: DataTypes.ENUM(['natural_person', 'legal_entity']),
      defaultValue: "natural_person",
    },
  }, {
    tableName: 'identities',
    underscored: true,
    schema: process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Identities.associate = (models) => {
    Identities.belongsTo(models.users, {
      foreignKey: {
        name: 'userIdKey',
        field: 'user_id',
      },
      as: 'user',
    });
    Identities.belongsToMany(models.agencies, {
      through: 'identityAgencies',
      foreignKey: 'identity_id',
      otherKey: 'agency_id',
      as: 'agenciesThroughIdentityAgencies',
    });
    Identities.belongsToMany(models.roles, {
      through: 'identitiesRoles',
      foreignKey: 'identity_id',
      otherKey: 'role_id',
      as: 'rolesThroughIdentitiesRoles',
    });
    Identities.hasMany(models.contracts, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'contracts',
    });
    Identities.hasMany(models.accountPlaceEntries, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'accountPlaceEntries',
    });
    Identities.hasMany(models.attendances, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'attendances',
    });
    Identities.hasMany(models.votes, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'votes',
    });
    Identities.hasMany(models.generalMeetingEvents, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'generalMeetingEvents',
    });
    Identities.hasMany(models.generalMeetingDocuments, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'generalMeetingDocuments',
    });
    Identities.hasMany(models.documentSignatures, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'documentSignatures',
    });
    Identities.hasMany(models.serviceOrders, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'serviceOrders',
    });
    Identities.hasMany(models.previousManagementAgents, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'previousManagementAgents',
    });
    Identities.hasMany(models.identityRepartitionKeys, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'identityRepartitionKeys',
    });
    Identities.hasMany(models.defaultIdentities, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'identity_id',
      },
      as: 'defaultIdentities',
    });
    Identities.hasMany(models.addresses, {
      foreignKey: {
        name: 'identityIdKey',
        field: 'addressable_id',
      },
      as: 'addresses',
    });
  };
  return Identities;
};

morganperre · October 12, 2021, 11:56am

Okay @JeremyV,

That was worth the try, we know that this solution is not working. You can maybe create a ticket on sequelize to express your problem. (I don’t understand why we can’t specified the ON clause)

I think we can give a try to pure SQL injection with Sequelize.literal as you mention early. Remove all the include part. I never used it before…

I’m wondering how this will work.

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]})
`);

And thanks for the schema. If this one didn’t work I will share them to a colleague, so we e able to test this edge case on our side.

Kind regards,
Morgan

PS: Under the Polymorphic eager loading categories there is some hints, but you need to use additional hooks to make it work

Topic		Replies	Views
Searching on an other-table based Smart Field Help me!	23	705	February 10, 2021
Smart fields filters Help me!	1	346	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