# Error finding them

**URL:** <https://community.forestadmin.com/t/error-finding-them/2561>\
**Category:** Help me!\
**Created:** [May 26, 2021, 9:35am UTC](https://community.forestadmin.com/t/error-finding-them/2561 "2021-05-26T09:35:16Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Benjamin](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/benjamin/32/2127_2.png) [@Benjamin](https://community.forestadmin.com/u/Benjamin)\
**Post date:** [May 26, 2021, 9:35am UTC](https://community.forestadmin.com/t/error-finding-them/2561/1 "2021-05-26T09:35:16Z")

</div>

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_

```javascript
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 :

```javascript
[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](https://community.forestadmin.com/t/searching-on-an-other-table-based-smart-field/1782/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]}%` } }, ], };

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

Thank’s,  
See you,

---

<div class="post-metadata">

**Author:** ![Guillaume\_Cisco](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/guillaume_cisco/32/918_2.png) [@Guillaume\_Cisco](https://community.forestadmin.com/u/Guillaume_Cisco)\
**Post date:** [May 26, 2021, 10:48am UTC](https://community.forestadmin.com/t/error-finding-them/2561/2 "2021-05-26T10:48:45Z")

</div>

Hi Benjamin,

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

Thank, you,

---

<div class="post-metadata">

**Author:** ![Guillaume\_Cisco](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/guillaume_cisco/32/918_2.png) [@Guillaume\_Cisco](https://community.forestadmin.com/u/Guillaume_Cisco)\
**Post date:** [May 26, 2021, 12:04pm UTC](https://community.forestadmin.com/t/error-finding-them/2561/3 "2021-05-26T12:04:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Benjamin](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/benjamin/32/2127_2.png) [@Benjamin](https://community.forestadmin.com/u/Benjamin)\
**Post date:** [May 26, 2021, 12:48pm UTC](https://community.forestadmin.com/t/error-finding-them/2561/4 "2021-05-26T12:48:09Z")

</div>

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_

```javascript
{
  "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_

```javascript
// 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_

```javascript
// 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;
};

```

---

<div class="post-metadata">

**Author:** ![Guillaume\_Cisco](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/guillaume_cisco/32/918_2.png) [@Guillaume\_Cisco](https://community.forestadmin.com/u/Guillaume_Cisco)\
**Post date:** [May 26, 2021, 1:07pm UTC](https://community.forestadmin.com/t/error-finding-them/2561/5 "2021-05-26T13:07:53Z")

</div>

Thank you @Benjamin,

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

```javascript
LEFT OUTER JOIN `UserData` AS `data`

```

Does it works, if you replace by:

```javascript
[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 🙂

---

<div class="post-metadata">

**Author:** ![Benjamin](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/benjamin/32/2127_2.png) [@Benjamin](https://community.forestadmin.com/u/Benjamin)\
**Post date:** [May 26, 2021, 1:14pm UTC](https://community.forestadmin.com/t/error-finding-them/2561/6 "2021-05-26T13:14:43Z")

</div>

It’s perfect !

Thank’s