Sequelize.js - How do I use a column from a left joined table in the where clause?

Question

I have two tables in an SQL database with a one-to-many relationship. I'm modeling this with sequelize.js. Each table has a date field. I'd like to write a single findAll query that can do a LEFT JOIN and filter for any row with a date in a certain range. I can write the query in SQL, but I can't figure out how to write an options object to pass to findAll to reproduce the same query.

Here is the setup:

class Customer extends Model {}

Customer.init( {
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  date_of_registration: DataTypes.DATE
} );

class Purchase extends Model {}

Purchase.init( {
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  date_of_purchase: DataTypes.DATE
} );

Customer.hasMany( Purchase, {
  foreignKey: { allowNull: false, name: 'customer_id' }
} );
Purchase.belongsTo( Customer, {
  foreignKey: { allowNull: false, name: 'customer_id' }
} );

Here is the query I'd like to run:

SELECT Customer.date_of_registration,
       Purchase.date_of_purchase
FROM Customer
       LEFT JOIN Purchase
                 ON Customer.id = Purchase.customer_id
WHERE Customer.date_of_registration > '2023-10-24'
   OR Purchase.date_of_purchase > '2023-10-24'
GROUP BY Customer.id;

I've tried many incorrect things, but the closest I can get is putting the query for the date_of_purchase in an ON clause in the LEFT JOIN instead in an OR clause under the WHERE clause. Here is what I've tried:

Customer.findAll( {
  where: {
    date_of_registration: {
      [ Op.gt ]: new Date( '2023-10-24' )
    }
  },
  include: [ {
    model: Purchase,
    where: {
      date_of_purchase: {
        [ Op.gt ]: new Date( '2023-10-24' )
      }
    },
    attributes: [],
    required: false
  } ]
} );

This query outputs this SQL:

SELECT "Customer"."id", "Customer"."date_of_registration", "Purchase"."date_of_purchase"
FROM "customer" AS "Customer"
  LEFT OUTER JOIN "purchase" AS "Purchase"
    ON "Customer"."id" = "Purchase"."customer_id"
      AND "Purchase"."date_of_purchase" > '2023-10-24 00:00:00.000 +00:00'
WHERE "Customer"."date_of_registration" > '2023-10-24 00:00:00.000 +00:00'
GROUP BY "Customer"."id";

How do I modify the Customer.findAll call to output the first query without using a Raw Query?

Answer 1

I'm not sure about representing the whole original SQL query as a Sequelize query via a model's findAll (because it's an aggregation query and it's almost always hard to write such queries w/o literals or as a plain SQL query in Sequelize) but as for representing the given OR condition it could look like this:

Customer.findAll( {
  where: {
    [Op.or]: [{
    date_of_registration: {
      [ Op.gt ]: new Date( '2023-10-24' )
    }
  }, {
      '$purchase.date_of_purchase$': {
        [ Op.gt ]: new Date( '2023-10-24' )
      }
  }],
  include: [ {
    model: Purchase,
    attributes: [],
    required: false
  } ]
} );