0

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'] } });

5
  • Is there a reason why id is flapping around in the column like that? Commented Aug 4, 2023 at 15:03
  • For what it's worth, having id mean "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. Commented Aug 4, 2023 at 15:04
  • its not properly formatted I guess Commented Aug 4, 2023 at 15:05
  • I was worried it's a string value with padding being irregular. Commented Aug 4, 2023 at 15:06
  • Its done to keep log of every status change of id from table 1. Better way to keep track of such logs can be suggested Commented Aug 4, 2023 at 15:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.