Sequelize include nested column: Unknown column in 'where clause'

Question

I am trying to use the Sequelize ORM's feature that allows referring the nested column from the included Models (See Sequelize Docs: Complex where clauses at the top-level). In the docs it states that, I can use $nested.column$ syntax.

The following is what I was trying to do:

let where = { memberId };
if (req.query.search) {
  const like = { [Op.like]: `%${req.query.search}%` };
  where = {
    ...where,
    [Op.or]: [
      { '$bookItem.serial$': like },
      { '$bookItem.book.name$': like },
      { '$bookItem.book.ISBNCode$': like },
    ],
  };
}

const options = {
  where,
  include: [
    {
      model: models.BookItem,
      as: 'bookItem',
      required: false,
      include: [
        {
          model: models.Book,
          as: 'book',
          attributes,
          required: false,
        },
      ],
    },
  ],
});

const transactions = await models.Borrow.findAll(options);

However, for the code above, I am getting the following error:

"Unknown column 'bookItem.serial' in 'where clause'"

What am I missing?

Full DB Schema: https://dbdiagram.io/d/5e08b6aaedf08a25543f79cb

Answer 1

Is bookitem a Table? Or a Database?

bookItem.serial either represents db.tbl or tbl.column

bookItem.book.name can only represent db.tbl.column

Since bookItem seems to be a database name, then serial must be a table name. At that point, "tablename LIKE ..." is a syntax error.