"WHERE" clause in Sequelize with nested table

Question

During using Sequelize in my private project, I have one table

const Order= sequelize.define('Order', {
    _id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    value: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    _order_tracker_id: {
        type: DataTypes.INTEGER,
        allowNull: true
    },
    type: {
        type: DataTypes.INTEGER,
        allowNull: false
    },

Order.associate = function(models) {
    const {Order} = models
    
    Order.hasMany(Order, {
      as: 'tempOrder',
      foreignKey: '_order_tracker_id',
      onDelete: 'RESTRICT',
      onUpdate: 'RESTRICT'
    })
    
  }

});

I am using Sequelize for querying data:

const { Order} = sequelize.models
const oSequelize = {
      attributes: [
        '_id',
        'value',
      ],
      raw: false,
      include: [
        {
          model: Order,
          as: 'tempOrder',
          attributes: ['value' as 'tracker_value'],
          where: {
            [Op.or]: [
              {
                value: {
                  [Op.gt]: 1
                }
              },
              {
                '$tempOrder.value$': { [Op.gt]: 1 }
              }
            ]
          },
          required: false
        },
      ],

I expect it will show the same result with this SQL:

SELECT T1._id, T1.value, T1._order_tracker_id, T2.value as tracker_value 
FROM Order as T1 
JOIN Order as T2 ON T1._id = T2._order_tracker_id
WHERE T1.type = 1 AND (T1.value > 1 OR T2.value > 1)

More explanation: For example, I have this table

_id   value     _order_tracker_id  type
1     100        null              1
2     0          null              1
3     300        2                 2

For showing data in the UI, I expected that the result of my query will be like this

_id    value    _order_tracker_id     tracker_value
1      100      null
2      0        null                   300

My stuck is: I don't know how to transfer (T1.value > 1 OR T2.value > 1) to sequelize syntax. How can I change the Sequelize config to achieve my expected result?

Answer 1

I have shown this example here please try this one. I hope this will help you.

Thank you

const { Order} = sequelize.models
const oSequelize = Table1.findAll({
  attributes: [
    '_id',
    'value',
  ],
  raw: false,
  where: {
        [Op.or]: [
          {
            value: {
              [Op.gt]: 1
            }
          },
          {
            '$table2.value$': { [Op.gt]: 1 }
          }
        ]
      },
  include: [
    {
      model: Table2,
      as: 'table2',
      attributes: ['value' as 'tracker_value'],
      required: false
    },
  ]

})