Error finding them

[Benjamin]

Hi everybody,

I would like to set up a search on the last name and first name of my users. In my old version I had deployed this code, which I also deployed on my new version:

forest/user.js

const { collection } = require('forest-express-sequelize');
const models = require('../models/');
const Op = models.objectMapping.Op;

collection('user', {
  actions: [],
  fields: [{
    field: 'firstName',
    type: 'String',
    isFilterable: true,
    get: (user) => {
      return models.userData
          .findOne({ where: { userId: user.id } })
          .then((data) => {
            return data ? data.firstName : '';
          });
    }
  }, {
    field: 'lastName',
    type: 'String',
    isFilterable: true,
    get: (user) => {
      return models.userData
          .findOne({ where: { userId: user.id } })
          .then((data) => {
            return data ? data.lastName : '';
          });
    }
  }, {
    field: 'fullName',
    type: 'String',
    isFilterable: true,
    get: (user) => {
      return models.userData
          .findOne({ where: { userId: user.id } })
          .then((data) => {
            return data ? data.firstName + ' ' + data.lastName : '';
          });
    },
    search: function (query, search) {
      let split = search.split(' ');
      var searchCondition = {
        [Op.or]: [
          { '$userData.firstName$': { [Op.like]: `%${split[0]}%` } },
          { '$userData.lastName$': { [Op.like]: `%${split[1]}%` } },
          { '$userData.firstName$': { [Op.like]: `%${split[1]}%` } },
          { '$userData.lastName$': { [Op.like]: `%${split[0]}%` } },
        ]
      };

      query.where[Op.and][0][Op.or].push(searchCondition);

      return query;
    }
  }],
  segments: [],
});

Only now I get this error :

[forest] 🌳🌳🌳  Unexpected error: Unknown column 'userData.firstName' in 'where clause'
{
  "name": "SequelizeDatabaseError",
  "parent": {
    "code": "ER_BAD_FIELD_ERROR",
    "errno": 1054,
    "sqlState": "42S22",
    "sqlMessage": "Unknown column 'userData.firstName' in 'where clause'",
    "sql": "SELECT count(`user`.`id`) AS `count` FROM `User` AS `user` LEFT OUTER JOIN `UserData` AS `data` ON `user`.`id` = `data`.`userId` LEFT OUTER JOIN `UserIntegration` AS `integration` ON `user`.`id` = `integration`.`userId` WHERE ((lower(`user`.`email`)  LIKE  lower('%wan%') OR lower(`user`.`password`)  LIKE  lower('%wan%') OR lower(`user`.`indicMobile`)  LIKE  lower('%wan%') OR lower(`user`.`mobile`)  LIKE  lower('%wan%') OR lower(`user`.`emailValidated`)  LIKE  lower('%wan%') OR lower(`user`.`resetPassword`)  LIKE  lower('%wan%') OR (`user`.`mobile` LIKE '%wan%') OR (`userData`.`firstName` LIKE '%wan%' OR `userData`.`lastName` LIKE '%undefined%' OR `userData`.`firstName` LIKE '%undefined%' OR `userData`.`lastName` LIKE '%wan%')));"
  },
  "original": {
    "code": "ER_BAD_FIELD_ERROR",
    "errno": 1054,
    "sqlState": "42S22",
    "sqlMessage": "Unknown column 'userData.firstName' in 'where clause'",
    "sql": "SELECT count(`user`.`id`) AS `count` FROM `User` AS `user` LEFT OUTER JOIN `UserData` AS `data` ON `user`.`id` = `data`.`userId` LEFT OUTER JOIN `UserIntegration` AS `integration` ON `user`.`id` = `integration`.`userId` WHERE ((lower(`user`.`email`)  LIKE  lower('%wan%') OR lower(`user`.`password`)  LIKE  lower('%wan%') OR lower(`user`.`indicMobile`)  LIKE  lower('%wan%') OR lower(`user`.`mobile`)  LIKE  lower('%wan%') OR lower(`user`.`emailValidated`)  LIKE  lower('%wan%') OR lower(`user`.`resetPassword`)  LIKE  lower('%wan%') OR (`user`.`mobile` LIKE '%wan%') OR (`userData`.`firstName` LIKE '%wan%' OR `userData`.`lastName` LIKE '%undefined%' OR `userData`.`firstName` LIKE '%undefined%' OR `userData`.`lastName` LIKE '%wan%')));"
  },
  "sql": "SELECT count(`user`.`id`) AS `count` FROM `User` AS `user` LEFT OUTER JOIN `UserData` AS `data` ON `user`.`id` = `data`.`userId` LEFT OUTER JOIN `UserIntegration` AS `integration` ON `user`.`id` = `integration`.`userId` WHERE ((lower(`user`.`email`)  LIKE  lower('%wan%') OR lower(`user`.`password`)  LIKE  lower('%wan%') OR lower(`user`.`indicMobile`)  LIKE  lower('%wan%') OR lower(`user`.`mobile`)  LIKE  lower('%wan%') OR lower(`user`.`emailValidated`)  LIKE  lower('%wan%') OR lower(`user`.`resetPassword`)  LIKE  lower('%wan%') OR (`user`.`mobile` LIKE '%wan%') OR (`userData`.`firstName` LIKE '%wan%' OR `userData`.`lastName` LIKE '%undefined%' OR `userData`.`firstName` LIKE '%undefined%' OR `userData`.`lastName` LIKE '%wan%')));",
  "stack": "SequelizeDatabaseError: Unknown column 'userData.firstName' in 'where clause'\n    at Query.formatError (/Users/bvelluet/Git/Alfred/node_modules/sequelize/lib/dialects/mysql/query.js:243:16)\n    at Query.handler [as onResult] (/Users/bvelluet/Git/Alfred/node_modules/sequelize/lib/dialects/mysql/query.js:50:23)\n    at Query.execute (/Users/bvelluet/Git/Alfred/node_modules/mysql2/lib/commands/command.js:30:14)\n    at Connection.handlePacket (/Users/bvelluet/Git/Alfred/node_modules/mysql2/lib/connection.js:425:32)\n    at PacketParser.onPacket (/Users/bvelluet/Git/Alfred/node_modules/mysql2/lib/connection.js:75:12)\n    at PacketParser.executeStart (/Users/bvelluet/Git/Alfred/node_modules/mysql2/lib/packet_parser.js:75:16)\n    at Socket.<anonymous> (/Users/bvelluet/Git/Alfred/node_modules/mysql2/lib/connection.js:82:25)\n    at Socket.emit (node:events:369:20)\n    at addChunk (node:internal/streams/readable:313:12)\n    at readableAddChunk (node:internal/streams/readable:288:9)\n    at Socket.Readable.push (node:internal/streams/readable:227:10)\n    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)"
}

The userData table does exist, there is no problem in displaying smart field information.
I used this research method here:

Searching on an other-table based Smart Field

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

Can you tell me if it is no longer current or if I did not implement it correctly ?

Thank’s,
See you,

[Guillaume_Cisco]

Hi Benjamin,

Thank you for this report.
Can you tell me which version of forest-express-sequelize you are using please?

Thank, you,

[Guillaume_Cisco]

Also did you try with last_name and first_name instead?
How is defined your database schema for User ?

[Benjamin]

Hi @Guillaume_Cisco,

I have try with last_name and first_name but it does not work. In db is lastName and firstName.

package.json

{
  "name": "dev-v2-alfred",
  "version": "0.0.1",
  "private": true,
  "scripts": {
    "start": "node ./server.js"
  },
  "dependencies": {
    "axios": "^0.21.1",
    "body-parser": "1.19.0",
    "chalk": "~1.1.3",
    "cookie-parser": "1.4.4",
    "cors": "2.8.5",
    "debug": "~4.0.1",
    "dotenv": "~6.1.0",
    "express": "~4.17.1",
    "express-jwt": "5.3.1",
    "forest-express-sequelize": "^7.0.0",
    "morgan": "1.9.1",
    "mysql2": "~2.2.5",
    "node-crisp-api": "^1.12.2",
    "require-all": "^3.0.0",
    "sequelize": "~5.15.1"
  }
}

models/user.js

// 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 User = sequelize.define('user', {
    id: {
      type: DataTypes.BIGINT,
      autoIncrement: true,
      primaryKey: true
    },
    companyId: {
      type: DataTypes.BIGINT,
      allowNull: false,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    password: {
      type: DataTypes.STRING,
      allowNull: true,
      defaultValue: null
    },
    indicMobile: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    mobile: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    emailValidated: {
      type: DataTypes.STRING,
      allowNull: true,
      defaultValue: null
    },
    resetPassword: {
      type: DataTypes.STRING,
      allowNull: true,
      defaultValue: null
    },
    resetPasswordExp: {
      type: DataTypes.DATE,
      allowNull: true,
      defaultValue: null
    },
    lastLogin: {
      type: DataTypes.DATE,
      allowNull: true,
      defaultValue: null
    },
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
      onUpdate: Sequelize.literal('CURRENT_TIMESTAMP')
    },
  }, {
    tableName: 'User',
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  User.associate = (models) => {
    User.hasOne(models.userData, {
      foreignKey: 'userId',
      as: 'data',
    });

    User.hasOne(models.userIntegration, {
      foreignKey: 'userId',
      as: 'integration',
    });

    User.hasMany(models.order, {
      foreignKey: 'userId',
      as: 'order',
    });
  };

  return User;
};

models/user-data.js

// 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 UserData = sequelize.define('userData', {
    userId: {
      type: DataTypes.BIGINT,
      primaryKey: true,
      allowNull: false,
    },
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    lastName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    dateOfBirth: {
      type: DataTypes.DATEONLY,
      allowNull: true,
      defaultValue: null
    },
    gender: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    language: {
      type: DataTypes.STRING,
      defaultValue: "fr_FR",
      allowNull: false,
    },
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
      onUpdate: Sequelize.literal('CURRENT_TIMESTAMP')
    },
  }, {
    tableName: 'UserData',
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  UserData.associate = (models) => {
    UserData.belongsTo(models.user, {
      foreignKey: 'userId',
    });
  };

  return UserData;
};

[Guillaume_Cisco]

Thank you @Benjamin,

I can see in your query an alias on UserData towards data

LEFT OUTER JOIN `UserData` AS `data`

Does it works, if you replace by:

[Op.or]: [
          { '$data.firstName$': { [Op.like]: `%${split[0]}%` } },
          { '$data.lastName$': { [Op.like]: `%${split[1]}%` } },
          { '$data.firstName$': { [Op.like]: `%${split[1]}%` } },
          { '$data.lastName$': { [Op.like]: `%${split[0]}%` } },
        ]

Let me know

[Benjamin]

It’s perfect !

Thank’s