CODEWITHSUNDEEP
sequelize.js
Shih-Min Lee
Shih-Min Lee

Reputation: 9750

Sequelize query by virtual field

I have a schema that has 2 fields. one records the user and the other one is a virtual field based on the first field.

sequelize.define("Invoice", {
  is_processing_by: { 
    type: DataTypes.INTEGER,
    references: "usertable",
    referencesKey: "id",
    allowNull: false
  },
  is_processing: { 
    type: DataTypes.VIRTUAL,
    get: function() {
      return this.get("is_processing_by")
    }
  }
}

I tried to do 

Invoice.find({where: {is_processing: 123}})

but it gives me an error: 

Unhandled rejection AssertionError: expected {
  message: 'column Invoice. is_processing does not exist',
  name: 'SequelizeDatabaseError',
  original: {
  [error: column Invoice.is_processing does not exist]
....

Does anyone know what is happening? Thanks.

Upvotes: 4

Views: 6863

Answers (1)

9andresc
9andresc

Reputation: 21

For everyone struggling with this (as I use to) you can just use a portion of raw SQL to create a virtual column and use it to sort your rows. Like this:

const { literal } = require('sequelize')
...
const attributes = Object.keys(Users.rawAttributes)
const users = await Users({
  attributes: [
    ...attributes,
    [literal(`
      coalesce(
        nickname,
        concat("firstName", ' ', "lastName")
      )
    `),
    'name']
  ],
  ...
  order: [[literal('"name"'), 'asc']]
})

Hope helps someone ;)

Upvotes: 2

Related Questions