Retrieve a collection of users along with their most recent two entries using TypeORM and SQL

Question

Retrieve a collection of users along with their most recent two entries using TypeORM and SQL

What is the best approach to retrieve a list of users with their last two visited locations using SQL query?

user-table

id  name    
xxx  a
xyx  b
zzz  e

visitedlocation-table

id   startDate userID  location  
1.      1/2/21     xxx    USA
2.      1/3/21     xxx    UK
3.      1/2/21     xyx    AR
4.      1/3/21     xyx    USA
5.      1/5/21     zzz    USA
6.      1/6/21     xxx    IN

Desired output should be structured like:

[
  {
    id: "xxx",
    name: "a",
    lastVisits: [
      {
        id: "6",
        startDate: "1/6/21",
        location: "IN"
      },
      {
        id: "2",
        startDate: "1/3/21",
        location: "UK"
      }
    ]
  },
  {
    id: "xyx",
    name: "b",
    lastVisits: [
      {
        id: "4",
        startDate: "1/3/21",
        location: "USA"
      },
      {
        id: "3",
        startDate: "1/2/21",
        location: "AR"
      }
    ]
  },
  {
    id: "zzz",
    name: "b",
    lastVisits: [
      {
        id: "5",
        startDate: "1/5/21",
        location: "USA"
      }
    ]
  }
]

I am currently utilizing TypeORM and have established a one-to-many relationship between the user entity and the "visited location" table

repository
.createQueryBuilder('user)
.leftJoinAndSelect(
  'user.visitedLocation',
  'visitedLocation',
  'visitedLocation.userId = user.id'
)
.getRawMany();

Although I attempted the provided query, it retrieves all visited locations instead of just the last 2. If achieving this in query builder proves challenging, kindly suggest an alternative SQL query.

sql node.js postgresql typeorm

Answer 1

Answer №1

If you want to rank your rows and only retrieve the last two of them, consider using dense_rank() function.

SELECT userID, startDate, location 
FROM 
(
     SELECT a.id as userID, b.startDate, b.location,
      --this will group the rows by user_id and then rank them based on startDate   
      DENSE_RANK() OVER(PARTITION BY b.userID ORDER BY b.startDate DESC) as 
                      row_rank 
      FROM users_table a
           INNER JOIN locations_visited b
           ON (a.id = b.userID)
 )T WHERE row_rank <=2 -- fetch only the first two rows

You can find inspiration from the aforementioned logic. Additionally, I will share a solution that provides JSON-based output.

Edit

WITH user_visits AS
(
SELECT userID, name, id, startDate, location 
FROM 
(
     SELECT a.id as userID,a.name,b.id, b.startDate, b.location,
      --this will group your rows by user_id and then rank them based on startDate   
      DENSE_RANK() OVER(PARTITION BY b.userID ORDER BY b.startDate DESC) as 
                      row_rank 
      FROM users_table a
           INNER JOIN locations_visited b
           ON (a.id = b.userID)
 )T WHERE row_rank <=2 -- fetch only the first two rows
)
SELECT jsonb_pretty(array_to_json(array_agg(row_to_json(t)))::jsonb)
FROM(
SELECT userid as id, name, 
            (
                    SELECT array_to_json(array_agg(row_to_json(d))) 
                        FROM(
                                SELECT id,startdate,location
                                    FROM user_visits b
                                WHERE b.userid = u.userid
                        )d
            ) as lastVisits
FROM user_visits u
GROUP BY userid,name
ORDER BY userid
)t;

Result of the above query:

[
    {
        "id": "xxx",
        "name": "a",
        "lastvisits": [
            {
                "id": 6,
                "location": "IN",
                "startdate": "2021-06-01"
            },
            {
                "id": 2,
                "location": "UK",
                "startdate": "2021-03-01"
            }
        ]
    },
    {
        "id": "xyz",
        "name": "b",
        "lastvisits": [
            {
                "id": 4,
                "location": "USA",
                "startdate": "2021-03-01"
            },
            {
                "id": 3,
                "location": "AR",
                "startdate": "2021-02-01"
            }
        ]
    },
    {
        "id": "zzz",
        "name": "e",
        "lastvisits": [
            {
                "id": 5,
                "location": "USA",
                "startdate": "2021-05-01"
            }
        ]
    }
]

Answer 2

If you want to rank your rows and only retrieve the last two of them, consider using dense_rank() function.

SELECT userID, startDate, location 
FROM 
(
     SELECT a.id as userID, b.startDate, b.location,
      --this will group the rows by user_id and then rank them based on startDate   
      DENSE_RANK() OVER(PARTITION BY b.userID ORDER BY b.startDate DESC) as 
                      row_rank 
      FROM users_table a
           INNER JOIN locations_visited b
           ON (a.id = b.userID)
 )T WHERE row_rank <=2 -- fetch only the first two rows

You can find inspiration from the aforementioned logic. Additionally, I will share a solution that provides JSON-based output.

Edit

WITH user_visits AS
(
SELECT userID, name, id, startDate, location 
FROM 
(
     SELECT a.id as userID,a.name,b.id, b.startDate, b.location,
      --this will group your rows by user_id and then rank them based on startDate   
      DENSE_RANK() OVER(PARTITION BY b.userID ORDER BY b.startDate DESC) as 
                      row_rank 
      FROM users_table a
           INNER JOIN locations_visited b
           ON (a.id = b.userID)
 )T WHERE row_rank <=2 -- fetch only the first two rows
)
SELECT jsonb_pretty(array_to_json(array_agg(row_to_json(t)))::jsonb)
FROM(
SELECT userid as id, name, 
            (
                    SELECT array_to_json(array_agg(row_to_json(d))) 
                        FROM(
                                SELECT id,startdate,location
                                    FROM user_visits b
                                WHERE b.userid = u.userid
                        )d
            ) as lastVisits
FROM user_visits u
GROUP BY userid,name
ORDER BY userid
)t;

Result of the above query:

[
    {
        "id": "xxx",
        "name": "a",
        "lastvisits": [
            {
                "id": 6,
                "location": "IN",
                "startdate": "2021-06-01"
            },
            {
                "id": 2,
                "location": "UK",
                "startdate": "2021-03-01"
            }
        ]
    },
    {
        "id": "xyz",
        "name": "b",
        "lastvisits": [
            {
                "id": 4,
                "location": "USA",
                "startdate": "2021-03-01"
            },
            {
                "id": 3,
                "location": "AR",
                "startdate": "2021-02-01"
            }
        ]
    },
    {
        "id": "zzz",
        "name": "e",
        "lastvisits": [
            {
                "id": 5,
                "location": "USA",
                "startdate": "2021-05-01"
            }
        ]
    }
]

Retrieve a collection of users along with their most recent two entries using TypeORM and SQL

Answer №1

Similar questions

Challenges with Comparing Dates in MongoDB

Installing npm on tiny core linux can be accomplished by following a few simple steps

Retrieve the latest inserted ID in a Node.js application and use it as a parameter in a subsequent query

Can asynchronous programming lead to memory leakage?

Why isn't the transparency feature in graphicsmagick working?

MongoDB document queries that do not consider timezones

What is the best way to check a configuration setting in vue.config.js from srcmain.ts?

Node.js local storage/cookie functionality

How to effectively handle asynchronous calls in Node.js using readdir and stat functions

Securing Node function parameters in an asynchronous environment

The request in C++ is failing to reach the intended computer

Combining JWT authentication with access control lists: a comprehensive guide

Having trouble deploying my Node.js application on Microsoft Azure

Node app experiencing issues with passport authentication request route only in production mode

Streamline Jasmine-Node and express.js with automated processes

Problem with Angular2, NodeJS, and Passport Integration

Node.js provides a simple and efficient solution for utilizing the same form code for both adding and updating functionality

How can I execute route-specific middleware in advance of param middleware in Express v4?

When the page is finally displayed, the call back for `node express aws s3.listBuckets` function

What is the best way to show the user's name on every page of my website?