Problem ordering with including model in sequelize v6 [Solved] - It wasn't sequelize error

Question

I'm trying to add order clause to method function but got this unknown column error.

Error: Unknown column 'Product.createdAt' in 'order clause'

So I looked up the SQL and found that the problem is column name in order clause that sequelize generated.

I'll be very thankful for any idea to solve this problem.

System

• windows 10
• nodejs v13.11.0
• sequelize [ CLI: 6.2.0, ORM: 6.3.4 ]

Router

...

const models = require('./models')
const { Product, ViewItem, Category, ... } = models

...

//Middleware apply the req.filter, generated filter object will be like this

const req = {
   ...req,
   filter : {
      order : [
         [{ model : Product, as : 'Product'}, 'rating', 'desc'],
         ['createdAt', 'desc']
      ],
      ...
   }
}

const products = await ViewItem.listAndApply({
                subquery : false,
                include: [
                    { model : Brand, as : 'Brand', required: true },
                    { 
                        model : Category,
                        as : 'Category', 
                        required: true,
                        attributes : [ 'id', 'level', 'name' ],
                        where : req.filters.toJson({ namespace : 'Category' }) 
                    },
                    { model : ProductImage, as : 'ProductImage', required: false },
                    { model : ProductStatus, as : 'ProductStatus', required: false },
                    { model : ProductType, as : 'ProductType', required: false },
                    { model : ProductRating, as : 'ProductRating', required : true }
                ],
                where : req.filters.toJson(),
                limit : limit,
                offset : offset,
                order : req.filters.order
...

models/ViewItem.js

ViewItem.listAndApply = async function (options) {
    const {
      ProductView,
      Product,
    } = require('.')

    const payload = lodash.cloneDeep(options)
    let {
      include,
    } = payload

    if (!include) include = []
    .
    .
    .
    const items = await ViewItem.findAll({
      subquery: false,
      include: [
        .
        .
        .
        {
          model: Product,
          as: 'Product',
          required: true,
          where: product.where,
          include: include
        }
      ],
      where: where,
      limit: limit,
      order: order
    })

    //Please don't mind the applyView(), this function do nothing with sequelize level.
    
    return items.map(item => applyView(item))
  }

This SQL is broken (What I got from sequelize)

SELECT 
  `ViewItem`.*, 
.
.
.
FROM 
  (
    SELECT 
      .
      .
      .
      `Product`.`rating` AS `Product.rating`, 
      .
      .
      .
    FROM 
      `ViewItems` AS `ViewItem` 
.
.
.
ORDER BY 
  `Product`.`rating` ASC,
  `ViewItem`.`createdAt` DESC;

This SQL works fine (What I want to generate)

SELECT 
  `ViewItem`.*, 
.
.
.
FROM 
  (
    SELECT 
      .
      .
      .
      `Product`.`rating` AS `Product.rating`, 
      .
      .
      .
    FROM 
      `ViewItems` AS `ViewItem` 
.
.
.
ORDER BY 
  `Product.rating` ASC,
  `ViewItem`.`createdAt` DESC;

What I tried to solve this problem.

// SQL broken with syntax error, col name return is " ``. "
req.filter.order.push([sequelize.literal('Product.rating'), 'desc'])

// also broken with same error above
req.filter.order.push([sequelize.col('Product.rating'), 'desc'])

// same

I'm Any idea for resolving this error?

---

Model Code added

models/Product.js

'use strict'

module.exports = (sequelize, DataTypes) => {
  const Product = sequelize.define('Product', {
    name: DataTypes.STRING,
    sku: DataTypes.STRING,
    supplier: DataTypes.STRING,
    manufacturer: DataTypes.STRING,
    thumbnail: DataTypes.STRING,
    wholeSalePrice: DataTypes.INTEGER,
    retailPrice: DataTypes.INTEGER,
    originalPrice: DataTypes.INTEGER,
    discount: DataTypes.FLOAT,
    description: DataTypes.STRING,
    domestic: {
      type: DataTypes.BOOLEAN,
      defaultValue: true
    },
    underAge: {
      type: DataTypes.BOOLEAN,
      defaultValue: true
    },
    display: {
      type: DataTypes.BOOLEAN,
      defaultValue: true
    },
    views: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    reviews : {
      type : DataTypes.INTEGER,
      defaultValue : 0
    },
    jjim : {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    sold: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    rating: {
      type: DataTypes.FLOAT,
      defaultValue: 0
    }
  }, { timestamps: true, paranoid: true })

  Product.associate = function(models) {
    // associations can be defined here
    Product.belongsToMany(models.Category, { as: 'Category', through: 'ProductCategory', onDelete: 'CASCADE' })
    Product.belongsToMany(models.ProductView, { as: 'ProductView', through: 'ProductViewMap', onDelete: 'CASCADE' })
    Product.belongsTo(models.Brand, { as: 'Brand' })
    Product.hasMany(models.ProductImage, { as: 'ProductImage', foreignKey: 'ProductId' , onDelete: 'CASCADE' })
    Product.hasMany(models.ProductItem, { as: 'ProductItem', foreignKey: 'ProductId', onDelete: 'CASCADE' })
    Product.belongsTo(models.ProductStatus, { as: 'ProductStatus' })
    Product.belongsTo(models.ProductType, { as: 'ProductType' })
    Product.hasOne(models.ProductRating, { foreignKey : 'ProductId', as : 'ProductRating' })
    Product.hasMany(models.UserJjim, { as : 'Jjims'})
  }

  Product.afterCreate(async (product, option) =>{
    const {
      sequelize,
      Product,
      ProductViewMap,
      ViewItem,
      ProductRating
    } = require('.')

    const { id, name, thumbnail } = product
    const DEFAULT_VIEW = 1

    // await ProductViewMap.create({ ProductId : id, ProductViewId : DEFAULT_VIEW })
    const item = await ViewItem.create({ ProductId : id, ProductViewId : DEFAULT_VIEW })
    console.log(item)
    await ProductRating.create({ ProductId : id })
  })

  Product.prototype.findActiveItem = async function(){
    const { ViewItem, View } = require('.')

    return ViewItem.findOne({
      subquery : false,
      include : [
        { model : View, as : 'View', required : true }
      ],
      where : {
        display : true,
        ProductId : this.id
      }
    })
  }

  Product.prototype.findActiveView = async function(){
    const { ViewItem, View } = require('.')

    return View.findOne({
      subquery: false,
      include : [
        { 
          model : View, 
          as : 'View', 
          required : true,
          include : [
            {
              model : ViewItem,
              as : 'ViewItem',
              required : true,
              where : {
                display : true
              }
            }
          ]
        }
      ]
    })
  }

  return Product
}

models/ViewItem.js

'use strict'

const lodash = require('lodash')

module.exports = (sequelize, DataTypes) => {
  const ViewItem = sequelize.define('ViewItem', {
    name: DataTypes.STRING,
    thumbnail: DataTypes.STRING,
    retailPrice: DataTypes.INTEGER,
    discount: DataTypes.FLOAT,
    staticDiscount: DataTypes.INTEGER,
    description: DataTypes.STRING,
    promotion: {
      type: DataTypes.JSON,
      defaultValue: null
    },
    position: DataTypes.INTEGER,
    display: DataTypes.BOOLEAN
  }, {
    timestamps: true,
    paranoid: true
  })

  ViewItem.associate = function (models) {
    // associations can be defined here
    ViewItem.belongsTo(models.ProductView, {
      as: 'ProductView',
      foreignKey: 'ProductViewId',
      onDelete: 'CASCADE'
    })
    ViewItem.belongsTo(models.Product, {
      as: 'Product',
      onDelete: 'CASCADE'
    })
  }

  /* Hook */
  ViewItem.afterCreate(async function (item) {
    const {
      Product,
      ProductViewMap
    } = require('.')

    const {
      id,
      ProductId,
      ProductViewId
    } = item

    /* Join table에 row를 추가합니다 */
    await ProductViewMap.create({
      ProductId,
      ProductViewId
    })

    /* View 아이템중에 같은 Product를 가르키는 상품의 노출 설정을 false로 합니다. */
    const product = await Product.findOne({
      where: {
        id: ProductId
      }
    })

    await ViewItem.update({
      display: false,
    }, {
      where: {
        ProductId: ProductId
      }
    })

    /* 생성되는 ViewItem의 초기 display 속성은 Product의 display를 따릅니다. */
    await this.update({
      display: product.display
    }, {
      where: {
        id: id
      }
    })
  })

  ViewItem.listAndApply = async function (options) {
    const {
      ProductView,
      Product,
    } = require('.')

    const payload = lodash.cloneDeep(options)
    let {
      include,
      where,
      order,
      limit,
      offset,
      product
    } = payload

    if (!include) include = []
    if (!product) product = {}

    where = where ? where : null
    order = order ? order : null
    limit = limit ? limit : null
    offset = offset ? offset : null
    product.where = product.where ? product.where : null

    const items = await ViewItem.findAll({
      subquery: false,
      include: [{
          model: ProductView,
          as: 'ProductView',
          required: true
        },
        {
          model: Product,
          as: 'Product',
          required: true,
          where: product.where,
          include: include
        }
      ],
      where: where,
      limit: limit,
      order: order
    })

    return items.map(item => applyView(item))
  }

  return ViewItem
}


/* private function */
const applyView = (item) => {
  if (!item) return null
  if (!item.Product) return null
  if (!item.ProductView) return null

  const product = lodash.cloneDeep(item.Product.toJSON())
  const view = lodash.cloneDeep(item.ProductView.toJSON())
  /* 
    View를 적용합니다.
  
    #1. 가격정보를 수정합니다.
    #2. 스티커와 태그를 추가합니다.
    #3. 상세페이지 url을 추가합니다.
  */

  if (view.additionalDiscount) {
    const discount = product.discount + view.additionalDiscount
    const retailPrice = Math.floor(product.originalPrice * (1 - discount / 100))

    product.discount = discount
    product.retailPrice = retailPrice
  } else if (view.discount) {
    const retailPrice = Math.floor(product.retailPrice * (1 - view.discount / 100))
    const discount = Math.floor((1 - retailPrice / product.originalPrice) * 100)

    product.retailPrice = retailPrice
    product.discount = discount
  }

  if (view.staticDiscount) {
    const retailPrice = product.retailPrice - view.staticDiscount
    const discount = Math.floor((1 - retailPrice / product.originalPrice) * 100)

    product.retailPrice = retailPrice
    product.discount = discount
  }

  if (view.tag) product.tag = view.tag

  if (view.sticker) product.sticker = sticker

  product.detailUrl = view.detailUrl ? `${view.detailUrl}/${item.id}` : `/products/${item.id}`

  /*
    ViewItem 

    #1. 상품정보를 수정합니다.
    #2. 가격정보를 수정합니다.
    #3. retailPrice가 있다면 기존 계산값을 무시하고 이 값을 사용합니다.
  */

  if (item.name) product.name = item.name
  if (item.thumbnail) product.thumbnail = item.thumbnail
  if (item.description) product.description = item.description

  if (item.discount) {}
  if (item.staticDiscount) {}

  if (item.retailPrice) product.retailPrice = item.retailPrice

  return ({
    ...product,
    ProductView: view,
    id: item.id,
    ProductId: item.ProductId,
    ProductViewId: item.ProductViewId
  })
}

Full code is about 500 lines, I reduced some unrelacted part for readability. Other models are not related with order clause.

Answer 1

My problem wasn't in sequelize itself but my instance method. I copied option object before querying to prevent mutation of option object, using lodash.cloneDeep(). But it turns out to that even deep clone can't copy the prototype chain.

const payload = lodash.cloneDeep(options)//I massed up in here

I can demonstrate it by executing code below.

const sequelize = require('sequelize')
const lodash = require('lodash')

const orginal = sequelize.literal('Product.rating')
const copied = lodash.cloneDeep(original)

console.log(original instanceof sequelize.Utils.SequelizeMethod )//true
console.log(copied instanceof sequelize.Utils.SequelizeMethod)//false

That is the reason why I got

Error: Order must be type of array or instance of a valid sequelize method.

Because copied object has no prototype chain pointing SequelizeMethod, inside of sequlieze query generator, the process failed to match my literal query and it leaded for me to getting broken query.

In the meantime, I still can't resolve unknown column name error in order clause when passing array for option.order argument. I took a look in abstract/query-generator.js briefly, and got some insight to resolve this problem. Generating order clause in getQueryOrders(), it would be even better than now, if order query generator refer the selected column name.

SELECT 
  `ViewItem`.*, 
.
.
.
FROM 
  (
    SELECT 
      .
      .
      .
      `Product`.`rating` AS `Product.rating`,
      #this column name should be equal to order clause's column name
      .
      .
      .
    FROM 
      `ViewItems` AS `ViewItem` 
.
.
.
ORDER BY 
  `Product`.`rating` ASC,#otherwise, it invokes unknow column name error.
  `ViewItem`.`createdAt` DESC;

# Then how about referring the selected column name in the where and order clause
# instead of managing each clause loosely?

Answer 2

Can you please try below for order sequelize literal:

req.filter.order.push([sequelize.literal(`"Product.rating"`), 'desc'])
// OR
req.filter.order.push([sequelize.literal("`Product.rating`"), 'desc'])