CODEWITHSUNDEEP
javascriptmysqlnode.jssequelize.js
Rohit Kaushal
Rohit Kaushal

Reputation: 558

How to apply single SQL OR operator to a table and its associated tables in Sequelize?

I want to to find products with a certain sub string in it (Searching for products) i.e., where the search string matches product.name OR product.description OR { in an associated table (category.name OR category.description) } OR { in other associated (hasMany) table ( sku.description) i.e, if anywhere in all the SKU's description}

I have a tables with following structure

products with fields id | name | description | categoryId
categories with fields id | name | description
sku with fields id | productId | description

with relations

db.product.belongsTo(db.category);
db.category.hasMany(db.product);

db.sku.belongsTo(db.product);
db.product.hasMany(db.sku);

ultimately I want products where search string matches anywhere for a product.

db.product.findAll({
   include: [
      {
         // hasOne relation
         model: db.category,
      },
      {
         // hasMany relation
         model: db.sku,
      },
   ],
   where: {
      [Op.or]: [
         {
            name: {
               [Op.like]: `%${search}%`
            }
         },
         {
            description: {
               [Op.like]: `%${search}%`
            }
         },
         // this doesn't work need some way to achieve something like this
         // {
         //    category:{
         //       name: {
         //          [Op.like]: `%${search}%`
         //       }
         //       description: {
         //          [Op.like]: `%${search}%`
         //       }
         //    }
         // }

      ]
   }
})

I can apply the where condition inside the include section of category but that won't work like an overall OR like what I need for Search.

this won't give expected results

   include: [
      {
         model: db.category,
         where : {
            name: {
                [Op.like]: `%${search}%`
            }
            description: {
                [Op.like]: `%${search}%`
            }
         }
      }
   ],

Upvotes: 0

Views: 147

Answers (1)

bedeljani
bedeljani

Reputation: 61

Clauses using joined table columns that don't work out of the box can be wrapped in $.

To obtain top-level WHERE clauses that involve nested columns, Sequelize provides a way to reference nested columns: the '$nested.column$' syntax.

It can be used, for example, to move the where conditions from an included model from the ON condition to a top-level WHERE clause.

User.findAll({
  where: {
    '$Instruments.size$': { [Op.ne]: 'small' }
  },
  include: [{
    model: Tool,
    as: 'Instruments'
  }]
});

Generated in SQL:

SELECT
  `user`.`id`,
  `user`.`name`,
  `Instruments`.`id` AS `Instruments.id`,
  `Instruments`.`name` AS `Instruments.name`,
  `Instruments`.`size` AS `Instruments.size`,
  `Instruments`.`userId` AS `Instruments.userId`
FROM `users` AS `user`
LEFT OUTER JOIN `tools` AS `Instruments` ON
  `user`.`id` = `Instruments`.`userId`
WHERE `Instruments`.`size` != 'small';

for documentation you can read it here: https://sequelize.org/master/manual/eager-loading.html

Upvotes: 1

Related Questions