Get list of user with their latest 2 inputs -(TypeORM ,sql)

Question

How do write a sql query to get at least 2 items for distinct UUIDs

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

I want to get a list of users with their last two visited locations

Desired output

[
  {
    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 using Type Orm and the user entity has a one to many relations with the "visited location" table

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

I tried using this query but it returns all the visited locations. But I want only the last 2 visited locations.

If it's hard do in query builder please suggest SQL query for this

Answer 1

You can try dense_rank() to rank your rows and only get the last two rows

SELECT userID,startDate,location 
FROM 
(
     SELECT a.id as userID, 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 user-table a
           INNER JOIN visitedlocation-table b
           ON (a.id = b.userID)
 )T WHERE row_rank <=2 -- fetch only the first two rows

you can take inspiration from the above logic. I'll be posting the JSON based output solution too

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 user_table a
           INNER JOIN visitedlocation_table 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;

output of 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"
            }
        ]
    }
]