'sequelize subquery as field column in where clause nodejs

I have endpoint which have search and description as get params.

And I need to make query like this in sequelize but got problems.

Query:

SELECT brands.*, (SELECT COUNT(id) FROM products WHERE brand_id = brands.id) as productCount
    FROM product_brands as brands
    WHERE 
        (brands.name LIKE '%$search%' OR (SELECT COUNT(id) FROM products WHERE brand_id = brands.id) LIKE '%$search%')
        AND
        description LIKE '%$description%';

My Sequelize:

    const where = {
      [Op.and]: [],
    };

    if (query.search) {
      where[Op.and].push({
        [Op.or]: [
          { name: { [Op.like]: `%${query.search}%` } },
          { [Sequelize.literal('(SELECT COUNT(*) FROM products WHERE products.brand_id = ProductBrands.id)')]: { [Op.like]: `%${query.search}%` } },
        ],
      });
    }

    const brands = await ProductBrands.findAndCountAll({
      where,
      attributes: {
        include: [
          [Sequelize.literal('SELECT COUNT(*) FROM products WHERE products.brand_id = ProductBrands.id'), 'productCount'],
        ],
      },
      limit,
      offset,
      raw: true,
    });

And got error like this:

Unknown column 'ProductBrands.[object Object]' in 'where clause'
node.jssequelize.js


Solution 1:[1]

You need to use Sequelize.where in order to use Sequelize.literal in where conditions:
replace

{ [Sequelize.literal('(SELECT COUNT(*) FROM products WHERE products.brand_id = ProductBrands.id)')]: { [Op.like]: `%${query.search}%` } }

with

Sequelize.where(Sequelize.literal('(SELECT COUNT(*) FROM products WHERE products.brand_id = ProductBrands.id)'), Op.like, `%${query.search}%`)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Anatoly

Related Questions