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
– FanoFN, Commented Feb 1, 2021 at 6:44
@fadlikidd the serial field is in bookItem. Others are $bookItem.book.xxx$ because those fields are inside the associated book model. — Azamat Abdullaev
– Azamat Abdullaev, Commented Feb 1, 2021 at 11:09
Mind to share the table structures? Of how many tables there is in the query? — FanoFN
– FanoFN, Commented Feb 2, 2021 at 0:34
@fadlikidd Here is the full DB schema: dbdiagram.io/d/5e08b6aaedf08a25543f79cb — Azamat Abdullaev
– Azamat Abdullaev, Commented Feb 2, 2021 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
– Darvesh, Commented Jul 14, 2021 at 4:47

Rick James · Accepted Answer · 2021-07-14 21:02:09Z

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, 2021 at 21:02

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Azamat Abdullaev Over a year ago

No, bookItem is the table name. Refer to the diagram in the link.

Rick James Over a year ago

@AzamatAbdullaev - Then this does not make sense: bookItem.book.name

Victor Chagas Seibert · Accepted Answer · 2021-07-14 20:08:27Z

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, 2021 at 20:08

Victor Chagas Seibert

211 silver badge4 bronze badges

Comments

Muhammad Tameem Rafay · Accepted Answer · 2023-01-17 10:05:04Z

I have corrected this error on my side. Initially, I am writing this query

but now I have rearranged the query and it works

WRONG QUERY

 let where = {
    [op.and]: [
        { id: partner_id },
        { [op.or]: [
          { '$customers.customer_name$': { [op.like]: '%' + query + '%'} },
          { '$customers.email$': { [op.like]: '%' + query + '%'} },
        ]},
    ],
};
  // const where = {
  //   id: partner_id
  // }
  return await this.deliveryBoys.findOne({

    attributes: [
      ['id', 'partner_id'],
      'delivery_boy_name',
      'email',
      'profile_picture',
      'phone_number',
      'fcm_token',
    ],
    include: [
      {
        model: this.customers,
        as: 'customers',
        attributes: ['id', 'customer_name', 'email', 'profile_picture', 'phone_number'],
        require: true,
        where: {
          customer_name: {
            [op.like]: '%' + query + '%'
          }
        },
        include: [
          {
            model: this.company,
            as: 'company',
          },
          {
            model: this.address,
            as: 'address',
            required: false,
            where: {
              status: 1
            }
          }
        ]
      },

    ],
    where,
  });

FINAL WORKING QUERY

let where = {
      [op.and]: [
          { '$deliveryBoys.id$': partner_id },
          { [op.or]: [
            { '$customers.email$': { [op.like]: '%' + query + '%'} },
            { '$customers.customer_name$': { [op.like]: '%' + query + '%'} },
            { '$customers.phone_number$': { [op.like]: '%' + query + '%'} },
            { '$company.name$': { [op.like]: '%' + query + '%'} },
          ]},
       ],
     };

   return await this.customers.findAll({
      attributes: ['id', 'customer_name', 'email', 'profile_picture', 'phone_number'],
      include:[
        {
          model: this.deliveryBoys,
          as: 'deliveryBoys',
          attributes: ['id','delivery_boy_name','phone_number','email','profile_picture','status',],
          where:{
            id: partner_id
          }
        },
        {
          model: this.company,
          as: 'company',
        },
        {
          model: this.address,
          as: 'address',
          required: false,
          where: {
            status: 1
          }
        }
      ],
      where

    });

yogithesymbian · Accepted Answer · 2024-12-05 01:07:57Z

i have debug with console log to chech the raw query and i found the different here .

await your_table
        .findAndCountAll({
          include: relationDatabase,
          where: {
            // [Op.and]: [newQuery]
            [Op.and]: [
              newQuery,
              ...(querySearchTerms ?? {}), // contains '$relation_table.name_column$'
              ...(queryIncludes ?? {}),
              ...(queryExcludes ?? {})
            ]
          },
          limit,
          offset,
          order: [[Sequelize.literal('`' + field + '`'), type]],
          logging: console.log
        })

my querySearchTerms

const querySearchTerms = [
      {
        [Op.or]: [
          {
            '$relation_table.name_column$': {
              [Op.substring]: searchTerm
            }
          },
          isInvalidDateObj.isValid()
            ? {
                schedule: {
                  [Op.substring]: searchTerm
                  // '2023-07-20'
                }
              }
            : null
        ]
      }
    ]

and here is the core of relation make it different of result the raw query

const relationDatabase = [
  {
    model: user,
    paranoid: false,
    include: [ // here include make raw query different result
      {
        model: ability,
        paranoid: false
      }
    ]
  },

i have a lot of relation has many and others . in here case if i comment relation on ability my raw query result is looks like entire query is on outside . my mean here is example :

SELECT
    `form_boat`.`id`,
     ... a lot of colum
    `user`.`id` AS `user.id`,
     ... a lot of colum
    `military_boat`.`id` AS `military_boat.id`,
     ... a lot of colum
    `asal_kapal`.`id` AS `asal_kapal.id`,
     ... a lot of colum
FROM
WHERE 
  ... // in here where clause is correct to get the `$table.column$` is on working

BUT IF using ability inside users the result raw query become

SELECT
    `form_boat`.*, // just once like these
    `user`.`id` AS `user.id`,
     ... a lot of colum
    `military_boat`.`id` AS `military_boat.id`,
     ... a lot of colum
    `asal_kapal`.`id` AS `asal_kapal.id`,
     ... a lot of colum
FROM
    (
        SELECT
            `form_tug_boat`.`id`, 
    // in the end of this // as form_boat 
// and all of entire where clause will place on here so its return undefined
// database or column not found. thats mean wrong place for query. even you are using `$table.column_name` 
)

i still on debug why my ability make raw query result table.* instead of table.column

my engine

    "sequelize": "6.31.0",
    "sequelize-cli": "6.6.0",

and i know i can define where clause inside relation directly instead of $table.column$ but i prefer to define relation first on separate file to make it clean code my perspective . and to solve the issue you need to check the result of rawquery first if there is an error.

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

FINAL WORKING QUERY

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

FINAL WORKING QUERY

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related