node.js - Sequelize Error : missing FROM-clause entry for table

Question

I am trying to write sequelize query as shown below. I am getting an error missing FROM-clause entry for table "PIs->Item". When i logged the db queries, i found the issue.It is calling another SELECT query inside the FROM clause which is leading to error. How can i solve this issue?

const filter = {
  where: {},
  include: [{
    model: db.PurchasedItems,
    as: 'PIs',
    include: [{
      model: db.Item,
    }],
}],    

filter.where = {
    [Op.or]: [{
      name: {
        [Op.iLike]: `%${options.searchQuery}%`,
      },
    }, {
      '$PIs.Item.name$': {
        [Op.iLike]: `%${options.searchQuery}%`,
      },
    }],
  };

DB log

SELECT /*All columns from main table and lef joined table*/
FROM(SELECT /*columns of main table only*/
     FROM /*main table*/
     WHERE "MainTable"."name" ILIKE '%w%' OR "PIs->Item"."name" ILIKE '%w%'
) AS MainTable
LEFT OUTER JOIN "PurchasedItems" AS "PIs" ON "MainTable"."id" = "PIs"."purchaseId"
LEFT OUTER JOIN "Items" AS "PIs->Item" ON "PIs"."itemId" = "PIs->Item"."id"

question from:https://stackoverflow.com/questions/65932230/sequelize-error-missing-from-clause-entry-for-table

Answer 1

Waitting for answers