MySQL Sequelize searching two tables with one findAll

Question

I've created 3 tables

Products
|product_id | product_name| 
|-----------|-------------|
|1          | coca cola   |
|2          | pepsi       |
|3          | orange      |

Product_tags
|product_id | tag_id |
|-----------|--------|
|1          | 1      |
|2          | 1      |
|3          | 2      |

Tag_names
|tag_id     | tag_name |
|-----------|----------|
|1          | soda     |
|2          | juice    |

Using:

db.products.belongsToMany(db.tag_names, { through: 'product_tags',  as: "tags", foreignKey: 'product_id'});
db.tag_names.belongsToMany(db.products, {through: 'product_tags',  as: "products", foreignKey: 'tag_id'});

Now I want to search these tables via either product name OR tag name. e.g. product has name "coca cola" and tag "soda" so both a search of "coca" or a search of "sod" would return it and "sod" would also return "pepsi". All I can seem to do it is get it to require both product_name and tag_name to match the query which is not what I want.
Thanks,
Danny

Answer 1

To or two conditions, you'll want to use the [Op.or] operator. And then to access a table in a join clause from a top-level where clause, there's an interesting syntax with dollar signs that comes in handy. I'm not sure if this is exactly what you want, but it does return both coke and pepsi... I tested this query in sequelize version 6 against a postgres database

let result = await Product.findAll({
        include: {
            model: Tag,
            as: 'tags',
            required: true
        },
        where: {
            [Op.or]: [
                { 'product_name': { [Op.like]: '%coca%' } },
                { '$tags.tag_name$': { [Op.like]: '%sod%' } }
            ]
        }
    })

And using the data in the original question, it produced the following results:

[
  {
    "product_id": 1,
    "product_name": "coca cola",
    "tags": [
      {
        "tag_id": 1,
        "tag_name": "soda",
        "product_tags": {
          "createdAt": "2021-09-09T18:36:24.829Z",
          "updatedAt": "2021-09-09T18:36:24.829Z",
          "product_id": 1,
          "tag_id": 1
        }
      }
    ]
  },
  {
    "product_id": 2,
    "product_name": "pepsi",
    "tags": [
      {
        "tag_id": 1,
        "tag_name": "soda",
        "product_tags": {
          "createdAt": "2021-09-09T18:36:24.861Z",
          "updatedAt": "2021-09-09T18:36:24.861Z",
          "product_id": 2,
          "tag_id": 1
        }
      }
    ]
  }
]

Along with this query:

SELECT          "product"."product_id",
                "product"."product_name",
                "tags"."tag_id" AS "tags.tag_id",
                "tags"."tag_name" AS "tags.tag_name",
                "tags->product_tags"."created_at" AS "tags.product_tags.createdAt",
                "tags->product_tags"."updated_at" AS "tags.product_tags.updatedAt",
                "tags->product_tags"."product_id" AS "tags.product_tags.product_id",
                "tags->product_tags"."tag_id" AS "tags.product_tags.tag_id"
FROM            "products" AS "product" INNER JOIN (
                        "product_tags" AS "tags->product_tags" INNER JOIN "tags" AS "tags"
                            ON "tags"."tag_id" = "tags->product_tags"."tag_id"
                    ) ON "product"."product_id" = "tags->product_tags"."product_id"
WHERE           ("product"."product_name" LIKE '%coca%' OR
                "tags"."tag_name" LIKE '%sod%');