I have one main object that I need query results for, based on the timestamps of multiple HasMany note objects related to the main table. There are up to 3 different types of note objects that can be attached to one main object.
With Sequelize, I need to find the max timestamp for each note, then return a member if all of those timestamps are greater than x days old.
Both attempts below seem like they should work. This first tells me I'm using MAX wrong:
main_object.findAll({
include: [
{
model: this.database.note_type1,
as: 'note_type1',
required: false,
},
{
model: this.database.note_type2,
as: 'note_type2',
required: false,
},
],
where: {
[Sequelize.Op.or]: [
Sequelize.where([Sequelize.fn('max', Sequelize.col('note_type1.updated_at')), 'max_updated_at']),
{
'max_updated_at': {
[Sequelize.Op.lt]: max_date,
},
},
Sequelize.where([Sequelize.fn('max', Sequelize.col('note_type2.updated_at')), 'max_updated_at']),
{
'max_updated_at': {
[Sequelize.Op.lt]: max_date,
},
},
],
},
});
I've also tried ordering the includes by timestamp and limiting to 1, which says "column updated_at doesn't exist":
main_object.findAll({
include: [
{
model: this.database.note_type1,
as: 'note_type1',
required: false,
order: [
['updated_at', 'DESC'],
],
limit: 1,
},
{
model: this.database.note_type2,
as: 'note_type2',
required: false,
order: [
['updated_at', 'DESC'],
],
limit: 1,
},
],
where: {
[Sequelize.Op.or]: [
{
'$note_type1.updated_at$': { [Sequelize.Op.lt]: max_date },
},
{
'$note_type2.created_at$': { [Sequelize.Op.lt]: max_date },
},
],
},
)};
With this last solution, I understand Sequelize may not allow ordering this way - however, the one solution I've found does not let me limit or max, it simply orders.