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

Could it be `'$bookItem.book.serial$'` instead? Because the other two is like that `'$bookItem.book.xxx$'` — FanoFN, Feb 01 '21 at 06:44
@fadlikidd the `serial` field is in bookItem. Others are `$bookItem.book.xxx$` because those fields are inside the associated `book` model. — Azamat Abdullaev, Feb 01 '21 at 11:09
Mind to share the table structures? Of how many tables there is in the query? — FanoFN, Feb 02 '21 at 00:34
@fadlikidd Here is the full DB schema: https://dbdiagram.io/d/5e08b6aaedf08a25543f79cb — Azamat Abdullaev, Feb 02 '21 at 10:07
It seems adding `subQuery: false` works when there is no `required: true` in the `include`. It would be nice if someone could explain why it's not working when `required: true` — Darvesh, Jul 14 '21 at 04:47
Is `$` being used as a letter? Or does it have some special significance in sequelize.js? — Rick James, Jul 14 '21 at 16:11
@RickJames Yes, it has a special meaning, you can read it here: https://sequelize.org/master/manual/eager-loading.html — Azamat Abdullaev, Jul 14 '21 at 18:29
can you also provide the `BookItem`, `Borrow` and `Book` sequelize models? — tam.teixeira, Jul 19 '21 at 13:58
its working fine https://stackoverflow.com/questions/61762127/sequelize-adding-a-limit-to-a-query-with-an-include-fails-to-properly-limit-r — atul Kumar gautam, Apr 07 '22 at 05:59

score 1 · Answer 1 · answered Jul 14 '21 at 21:02

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.

answered Jul 14 '21 at 21:02

Rick James

122,779
10
116
195

No, `bookItem` is the table name. Refer to the diagram in the link. – Azamat Abdullaev Jul 15 '21 at 09:11
1

@AzamatAbdullaev - Then this does not make sense: bookItem.book.name – Rick James Jul 15 '21 at 16:56

score 0 · Answer 2 · answered Jul 14 '21 at 20:08

0

In your linked documentation books has no name column, change $bookItem.book.name$ to $bookItem.book.title$ , and try adding right: true below required: false to create an inner join.

answered Jul 14 '21 at 20:08

Victor Chagas Seibert

11
1
4

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

2 Answers2