I have two tables: tbl_product, tbl_product_category

I want to make an API that take a keyword and return all product and product category that match with the keyword. If the result from tbl_product then it also return that it is product. If the result from tbl_product_category then it also return that it is category.

tbl_product_catagory.findAll({
  raw: true,
  attributes: [[sequelize.literal("catagory_name"), "type"]],
  include: [{
    model: tbl_product,
    attributes: [[sequelize.literal(["product_name"]), "name"]],
    required: false,
    where: {
      product_name: {
        [Op.like]: "%" + data.word + "%"
      }
    }
  }]
})

I have two tables: tbl_product, tbl_product_category

I want to make an API that take a keyword and return all product and product category that match with the keyword. If the result from tbl_product then it also return that it is product. If the result from tbl_product_category then it also return that it is category.

tbl_product_catagory.findAll({
  raw: true,
  attributes: [[sequelize.literal("catagory_name"), "type"]],
  include: [{
    model: tbl_product,
    attributes: [[sequelize.literal(["product_name"]), "name"]],
    required: false,
    where: {
      product_name: {
        [Op.like]: "%" + data.word + "%"
      }
    }
  }]
})

• javascript
• node.js
• sequelize.js

3 Answers 3

I know this is very old, but i like to show a turnaround using nodejs for this issue because sequelize yet do not have union support. Is very simple but sometimes we don't realize this:

Example:

Promise.all([
    Blog.findAll({ include: { model: Author, required: true } }),
    Blog.findAll({ include: { model: AnonymInfo, required: true } }),
]).then((modelReturn) => resolve(modelReturn.flat()))

According to this answer and this answer we can conclude that sequelize don't have good support for unions.

I know it's an old post, but may be helpful in the future. For complex queries with / without UNION (ALL), I'm using raw queries, like:

const sql = "SELECT col1, col2 FROM a WHERE cond1 = :param1
UNION ALL
SELECT col3, col4 FROM B WHERE cond2 =: param2 "

const records = await sequelize.query(sql, {
  replacements: {
    param1: value1,
    param2: value2,
  },
  type: QueryTypes.SELECT
})

Note: this is a dummy example, replace with your own tables / criteria ...