Do associated models where clauses act as an AND between the mod

ghz 8months ago ⋅ 48 views

I am trying to do a bit of a complicated query but wanted to simplify it here for help. Basically, i have mixed data in table coming from different tables associated by one parent table. In here, i wish to allow the idea of putting in a search time and searching all these different models as such

await ModalA.findAll({
    where: whereClauseA,
    include: [
      {
        model: ModalB,
        where: whereClauseB
      },
      {
        model: ModalC,
        where: whereClauseC
      }
    ]
  });

Lets say a table in a web browser shows a row related to the ModelA data, and within that table u can click on a row and expand and show more data that belongs to ModelB/C. If the user wishes to search for some key word, visible to them in the browser, but behind the scenes it means searching for that in the rows for all three tables.

So something like this where main table has an and and others have an or

const whereClauseA = {
    [Op.and]: [
      {
        [Op.or]: [
          {someColumnName1InModelA: {[Op.iLike]: `%${search}%`}},
          {someColumnName2InModelA: {[Op.iLike]: `%${search}%`}},
          {someColumnName3InModelA: {[Op.iLike]: `%${search}%`}},
          // ... can add more columns to search 
        ]
      },
      {
        id: 'SOME ID HERE'
      }
    ]
  };

  const whereClauseB = {
    [Op.or]: [
      {someColumnName1InModelB: {[Op.iLike]: `%${search}%`}},
      // ... can add more columns to search 
    ]
  };

  const whereClauseC = {
      someColumnName1InModelB: {[Op.iLike]: `%${search}%`}
  };

Now the problem i am seeing is, for model b and c, it seems to be ANDing them not ORing, so it appears (and note, im still learning sql and sequelize), it appears it looks for a result from all 3 tables or none at all. so I wish to return data from all 3 if the where matches, despite the results of the other tables. So something like this...

if search query in modela or in modelb or in modelc, return result

but what appears to be happening is,

if search query in modela and in modelb and in modelc, return result

Is there anything i can do about this?

Answers

It seems like you want to perform a search across multiple models (ModalA, ModalB, and ModalC) using Sequelize with OR conditions between columns within each model, but an AND condition between models.

The issue you're encountering is that Sequelize's include option with where clause is applying an AND condition between the included models instead of an OR condition.

To achieve the desired behavior of searching across all models with OR conditions within each model and an AND condition between models, you can use Sequelize's $or and $and operators explicitly.

Here's how you can adjust your query to achieve the desired behavior:

const { Op } = require('sequelize');

// Define your search term
const search = 'your_search_term';

// Define where clauses for each model
const whereClauseA = {
  [Op.and]: [
    {
      [Op.or]: [
        { someColumnName1InModelA: { [Op.iLike]: `%${search}%` } },
        { someColumnName2InModelA: { [Op.iLike]: `%${search}%` } },
        { someColumnName3InModelA: { [Op.iLike]: `%${search}%` } },
        // Add more columns to search in ModelA
      ]
    },
    { id: 'SOME ID HERE' } // Add any additional conditions for ModelA
  ]
};

const whereClauseB = {
  [Op.or]: [
    { someColumnName1InModelB: { [Op.iLike]: `%${search}%` } },
    // Add more columns to search in ModelB
  ]
};

const whereClauseC = {
  [Op.or]: [
    { someColumnName1InModelC: { [Op.iLike]: `%${search}%` } },
    // Add more columns to search in ModelC
  ]
};

// Perform the query with associations
await ModalA.findAll({
  where: whereClauseA,
  include: [
    {
      model: ModalB,
      where: whereClauseB,
      required: false // Ensure this is false to perform LEFT JOIN
    },
    {
      model: ModalC,
      where: whereClauseC,
      required: false // Ensure this is false to perform LEFT JOIN
    }
  ]
});

By explicitly using the $or operator within each where clause and setting required: false for each included model, you'll perform a LEFT JOIN on ModalB and ModalC, allowing results from ModalA to be returned even if there are no matching records in ModalB or ModalC. This will effectively apply an OR condition within each model and an AND condition between models, achieving the behavior you described.

#mysql