Searching on an other-table based Smart Field

Help me!
#1

I’ve been searching through the existing topics but haven’t found anything matching my issue.

Just trying to make my smart field searchable :

{
    field: 'nom complet',
    type: 'String',
    get: contract => models.identities.findOne({
      where: { id: contract.identityIdKey }
    }).then(identity => `${identity.lastName.toUpperCase()} ${identity.firstName}`),
    search: (query, search) => {
      let s = models.sequelize;
      let split = search.split(' ');
      const searchCondition = {...};
      query.where[Op.and][0][Op.or].push(searchCondition);
      return query;
    }
  }

In the documentation the smart field example is a concatenation of this same collection’s fields. But as you can see, my smart field is not based on the collection’s fields, but the identities collection.

I wonder how I can reach out my identities’ fields to match my query terms (the searchCondition object)

#2

Hi @JeremyV

I think you’ll have to insert a join to the query to be able to provide a condition on the identities table.

Here is a pretty old example that shows how you can do that:

I hope it will help.

#3

Thank you @arnaud for your input, unfortunately I haven’t managed to make it work. I have written a join in the get method as in your example :

{
    field: 'nom complet',
    type: 'String',
    get: contract => {
      const identity = models.contracts.findOne({
        where: { id: contract.id },
        include: [{
          model: models.identities,
          as: 'identity',
          required: true
        }]
      }).then(contract => contract && contract.identity && contract.identity.lastName
        ? ` ${contract.identity.lastName.toUpperCase()} ${contract.identity.firstName}`
        : '');
      return identity;
    },
    search: (query, search) => {
      let s = models.sequelize; // what is this for ?
      let split = search.split(' ');
      const searchCondition = {
        [Op.and]: [
          //  { lastName: { [Op.like]: `%${split[0]}%` } },
          //  { firstName: { [Op.like]: `%${split[1]}%` } },
        ]
      };
      query.where[Op.and][0][Op.or].push(searchCondition);
      return query;
    }
  }

In your example, user && user.googleSession && user.googleSession.accessToken is returned, but I don’t exactly know what is displayed in the field.

In the documentation, I don’t know what the line let s = models.sequelize;is for, since it’s not used afterwards.

#4

Hi @JeremyV !
The let s = models.sequelize; is useless here you are right.
In the example @arnaud gave, the important part is the addition of the user model:

 query.include.push({
        model: models.user,
        include: [{
          model: models.googleSession,
        }],
      });

Here we use the include statement to make a join on the user table (just as you did for the get).
From there, you can use the collection in your search statement :slight_smile:

#5

This syntax checks if there is a user, then if there is a googleSession for this user and finally returns the accessToken. it is equivalant to:

if (user) {
  if (user.googleSession) {
    return user.googleSession.accesToken;
  }
  return false;
}
return false;
#6

I’m sorry I’m still stuck on the same line which crashes again and again.

    query.include.push({
        model: models.identities,
        as: 'identity',
        required: true
      });

      let split = search.split(' ');

      const searchCondition = {
        [Op.and]: [
          { 'identity.lastName': { [Op.like]: `%${split[0]}%` } },
          { 'identity.firstName': { [Op.like]: `%${split[1]}%` } },
        ]
      };
      
      query.where[Op.and][0][Op.or].push(searchCondition);
      return query;

and I got Unexpected error: column contracts.identity.lastName does not exist

      const searchCondition = {
        [Op.and]: [
          { 'lastName': { [Op.like]: `%${split[0]}%` } },
          { 'firstName': { [Op.like]: `%${split[1]}%` } },
        ]
      };

Unexpected error: column contracts.lastName does not exist

      const searchCondition = {
        [Op.and]: [
          { '->identity.lastName': { [Op.like]: `%${split[0]}%` } },
          { '->identity.firstName': { [Op.like]: `%${split[1]}%` } },
        ]
      };

Unexpected error: column contracts.->identity.lastName does not exist

I don’t understand how this

const searchCondition = {
        [Op.and]: [
          { 'lastName': { [Op.like]: `%${split[0]}%` } },
          { 'firstName': { [Op.like]: `%${split[1]}%` } },
        ]
      };

in your documentation becomes this

query.where[Op.and][0][Op.or]
        .push(models.sequelize.literal(`"user->googleSession"."accessToken" ILIKE '%${search}%'`));

in your example.

#7

And last but not least :

query.include.push({
        model: models.identities,
        as: 'identity',
        required: true
      });
      query.where[Op.and][0][Op.or].push(models.sequelize.literal(`"contracts->identity"."lastName" ILIKE '%${search}%'`));
      return query;

=> Unexpected error: missing FROM-clause entry for table "contracts->identity"

#8

Hi @JeremyV,

Could you try the following and tell me if it works for you:

let split = search.split(' ');
const searchCondition = {
  [Op.and]: [
    { lastName: { [Op.like]: `%${split[0]}%` } },
    { firstName: { [Op.like]: `%${split[1]}%` } },
   ],
};
query.include.push({
  model: models.identities,
  as: 'identity',
  required: true,
  where: searchCondition,
});
 return query
#9

Way better @vince ! It does not crash anymore even though it does not return any result yet.
Here is an example of the generated query :

SELECT (...)

FROM "public"."contracts" AS "contracts" 
LEFT OUTER JOIN "public"."places" AS "place" 
    ON "contracts"."place_id" = "place"."id" 
INNER JOIN "public"."identities" AS "identity" 
    ON "contracts"."identity_id" = "identity"."id" 
    AND ("identity"."last_name" LIKE '%herbaut%' 
    AND "identity"."first_name" LIKE '%undefined%') 

WHERE ((lower("contracts"."display_name")  LIKE  lower('%herbaut%') 
OR lower("contracts"."customer_reference_number")  LIKE  lower('%herbaut%') 
OR lower("contracts"."customer_key")  LIKE  lower('%herbaut%') 
OR lower("contracts"."sergic_vip_label")  LIKE  lower('%herbaut%'))) 
ORDER BY "contracts"."id" DESC LIMIT 10 OFFSET 0;

It seems to me that the identity fields are not being searched since I don’t find them in the WHERE statement.

#10

They are in the ON so there might be nothing matching your search. Maybe you want to use Op.ilike instead of Op.like to ignore the case

#12

only one search term is working (maxime) if I click ‘Try an extended search’ :

search: (query, search) => {
      const searchCondition = {
        [Op.or]: [
          { lastName: { [Op.iLike]: `%${search}%` } },
          { firstName: { [Op.iLike]: `%${search}%` } },
        ],
      };
      query.include.push({
        model: models.identities,
        as: 'identity',
        required: true,
        where: searchCondition,
      });
      return query;
    }

Any other term doesn’t work… :frowning:

#13

What “term” is working and what is not. Could you show 2 rows in your DB one that works and one that doesn’t ?

#14

‘maxime’ is working

Screenshot 2021-02-10 092316
Screenshot 2021-02-10 0923161512×173 16.9 KB

anything else isn’t

Screenshot 2021-02-10 092643
Screenshot 2021-02-10 0926431496×116 13.3 KB

#15

Okey so what are you searching for. Show use the query and the search value you send in a case it does not work :wink:

#16

Here are some examples

SELECT (...)

LEFT OUTER JOIN "public"."places" AS "place" 
ON "contracts"."place_id" = "place"."id" 
INNER JOIN "public"."identities" AS "identity" 
ON "contracts"."identity_id" = "identity"."id" 
AND ("identity"."last_name" ILIKE '%32146%' OR "identity"."first_name" ILIKE '%32146%') 

WHERE (("contracts"."id" = 32146 
OR lower("contracts"."display_name")  LIKE  lower('%32146%') 
OR "contracts"."amount" = 32146 
OR "contracts"."parcel_sergic_id" = 32146 
OR lower("contracts"."customer_reference_number")  LIKE  lower('%32146%') 
OR lower("contracts"."customer_key")  LIKE  lower('%32146%') 
OR "contracts"."invited_count" = 32146 
OR "contracts"."sergic_vip_code" = 32146 
OR lower("contracts"."sergic_vip_label")  LIKE  lower('%32146%') 
OR "contracts"."report_account_entry_amount" = 32146 
OR "contracts"."coowner_amount" = 32146 
OR "contracts"."pending_amount" = 32146)) 

ORDER BY "contracts"."id" DESC LIMIT 10 OFFSET 0;

Another one :
Screenshot 2021-02-10 111536

SELECT (...) 

FROM "public"."contracts" AS "contracts" 

LEFT OUTER JOIN "public"."places" AS "place" 
ON "contracts"."place_id" = "place"."id" 
INNER JOIN "public"."identities" AS "identity" 
ON "contracts"."identity_id" = "identity"."id" 
AND ("identity"."last_name" ILIKE '%dunet%' OR "identity"."first_name" ILIKE '%dunet%') 

WHERE ((lower("contracts"."display_name")  LIKE  lower('%dunet%') 
OR lower("contracts"."customer_reference_number")  LIKE  lower('%dunet%') 
OR lower("contracts"."customer_key")  LIKE  lower('%dunet%') 
OR lower("contracts"."sergic_vip_label")  LIKE  lower('%dunet%') 
OR lower("place"."display_name")  LIKE  lower('%dunet%') 
OR lower("place"."ics_code")  LIKE  lower('%dunet%') 
OR lower("place"."picture_name")  LIKE  lower('%dunet%') 
OR lower("place"."sergic_offer")  LIKE  lower('%dunet%') 
OR lower("place"."sergic_id_full")  LIKE  lower('%dunet%') 
OR lower("place"."invite_url")  LIKE  lower('%dunet%') 
OR lower("place"."wp_code")  LIKE  lower('%dunet%') 
OR lower("place"."description")  LIKE  lower('%dunet%') 
OR lower("place"."siret")  LIKE  lower('%dunet%') 
OR lower("place"."registration_number")  LIKE  lower('%dunet%') 
OR lower("place"."dpe")  LIKE  lower('%dunet%'))) 

ORDER BY "contracts"."id" DESC LIMIT 10 OFFSET 0;
#17

Does it work with DUNET in capital letters ?

#18

dunet/DUNET or any of its substrings doesn’t work. And CLAUDINE does not work either.

maxime, of any of its substrings, does work, whether it is capital or lower case.
But surprisingly, the string HERBAUT (which is my test data, maxime’s lastname) does NOT.

#19

Oh my bad I just realised my mistake :sweat_smile:. Like you said It’s not in the where :sweat_smile:…

I could make it work with the following code:

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

      const searchCondition = {
        [Op.or]: {
          { '$identity.firstName$': { [Op.iLike]: `%${split[0]}%` } },
          { '$identity.lastName$': { [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;
    }
#20

Thanks @vince

And I guess your

const searchCondition = {
        [Op.or]: {
          { '$identity.firstName$': { [Op.iLike]: `%${split[0]}%` } },
          { '$identity.lastName$': { [Op.iLike]: `%${split[1]}%` } },
        },
     };

should be

const searchCondition = {
        [Op.or]: [
          { '$identity.firstName$': { [Op.iLike]: `%${split[0]}%` } },
          { '$identity.lastName$': { [Op.iLike]: `%${split[1]}%` } },
        ],
     };
1 Like
#21

Yes sorry :sweat_smile:
So did it work :slight_smile: ?