I have two tables "Table 1" and "Table 2". I am using nodejs and DB is postgres
Table 1
| id | createdOn |
| -------- | --------------------------|
| 1 | 2023-08-04T14:48:54+00:00 |
| 2 | 2023-08-04T14:48:54+00:00 |
Table 2
| logId | createdOn | statusID | id |
| -------- | --------------------------|----------|----|
| 1 | 2023-08-04T14:48:54+00:00 | 1 |1 |
| 2 | 2023-08-04T14:48:54+00:00 | 2 |1 |
| 3 | 2023-08-04T14:48:54+00:00 | 1 |2 |
| 4 | 2023-08-04T14:48:54+00:00 | 1 |1 |
| 5 | 2023-08-04T14:48:54+00:00 | 2 |2 |
| 6 | 2023-08-04T14:48:54+00:00 | 3 |2 |
| 7 | 2023-08-04T14:48:54+00:00 | 3 |1 |
| 8 | 2023-08-04T14:48:54+00:00 | 4 |2 |
id in Table 2 is foreign key.
I need to use only sequelize model query to fetch all columns from Table 1 and only statusID column from Table 2 where the logId is max for the id (i.e most recently updated status).
I have used subquery as literal where I have done order by logID desc limit 1. But is there any way where I dont use subquery. Also when I use MAX function in subquery, it gives me all the statusID in desc order, it doesn't give single result.
using logID desc limit 1 will affect the perfomance? Please suggest the best way to do this.
Also if I dont use subquery, I get statusID inside another object. for example :
{
id: 1,
createdOn: 2023-08-04T14:48:54+00:00,
Table2: {
statusID : 3
}
}
Can I get statusID in same heirarchy as other two columns as id, createdOn?
Also if possible, can I get the createdOn field as UNIX timestamp i.e in miliseconds?
Currently I am using this query
await model.table1.findAll({ include: { model: model.table2, required: true, order: [['logId', 'DESC']], limit: 1, attributes: ['statusID', 'status'] } });
idis flapping around in the column like that?idmean "primary key" in one table and "totally not that, actually a foreign key to some other unspecified thing" in another is really annoying to anyone using this schema.