Searching on an other-table based Smart Field

JeremyV · February 5, 2021, 3:05pm

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)

arnaud · February 5, 2021, 5:25pm

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:

gist.github.com

https://gist.github.com/arnaudbesnier/37f9295c7798c34883d40fabc7b142ed

forestSmartFieldWithTwoDepthJoinSearch.js

const Liana = require('forest-express-sequelize');
const models = require('../../models');

const { Op } = models.Sequelize;

Liana.collection('guest', {
  fields: [{
    field: 'googleAccessToken',
    type: 'String',
    get: async (guest) => {

This file has been truncated. show original

I hope it will help.

JeremyV · February 8, 2021, 8:45am

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.

anon94532230 · February 8, 2021, 10:07am

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

anon94532230 · February 8, 2021, 10:10am

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;

JeremyV · February 9, 2021, 2:40pm

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.

JeremyV · February 9, 2021, 3:06pm

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"

vince · February 9, 2021, 3:45pm

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

JeremyV · February 9, 2021, 5:23pm

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.

vince · February 9, 2021, 5:26pm

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

JeremyV · February 9, 2021, 6:25pm

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…

vince · February 10, 2021, 8:03am

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

JeremyV · February 10, 2021, 8:36am

‘maxime’ is working

anything else isn’t

vince · February 10, 2021, 9:49am

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

JeremyV · February 10, 2021, 10:19am

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 :

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;

vince · February 10, 2021, 10:57am

Does it work with DUNET in capital letters ?

JeremyV · February 10, 2021, 1:12pm

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.

vince · February 10, 2021, 1:50pm

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

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;
    }

JeremyV · February 10, 2021, 2:06pm

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

vince · February 10, 2021, 2:15pm

Yes sorry
So did it work ?

Topic		Replies	Views
Search in a smart field (with polymorphic relation) Help me!	45	1641	February 24, 2022
Implement Search on Smart Field Help me! howtos	7	586	June 28, 2021
Error finding them Help me!	5	345	May 26, 2021
Search through a smart field that was calculated from a different table Help me!	11	708	March 3, 2021
Search a collection reference through a smart field value not the _id Help me!	11	708	August 21, 2020

Searching on an other-table based Smart Field

Related topics