1

I have a database table as follows:

id lesson_start lesson_end instructor_id student_id
1 2023-06-01 04:00:00.000000 2023-06-01 06:00:00.000000 3 4
2 2023-03-18 11:00:00.000000 2023-03-18 12:30:00.000000 3 4
...
...

I want to fetch the first 7 days in the future where there are lessons scheduled for a specific user, rather than simply adding 7 days to the current date. This means that if there are no lessons scheduled for a user on a particular day within the next 7 days, that day should not be included in the result set, but it should look one further.

Generally, there are multiple lessons planned on a single day for a user, so I want to fetch all those lessons for all those days.

Right now I'm using Java with Spring (with a PostgreSQL database, but I'm willing to switch if that can make the difference) and I'm trying to write the queries on my own by using @Query.

Is there a way how to do this?

I tried to use the built-in features of Spring JPA to get this to work, but to no avail. After that, I searched around and tried to write my own queries by using GROUP BYand LIMIT clauses, but that didn't give me the results I wanted.

It couldn't get it to "see" past the next 7 days, even if they were empty.

Milan Dol
  • 15
  • 6

1 Answers1

1

I want to fetch the first 7 days in the future where there are lessons scheduled for a specific user

An approach uses dense_rank():

select *
from (
    select t.*,
        dense_rank() over(partition by student_id order by lesson_start::date) rn
    from mytable t
    where lesson_start >= current_date + interval '1' day
) t
where rn <= 7
order by student_id, lesson_start

The idea is to assign a rank to each future student lesson, that only increments when the day changes. You can run the subquery first to display the logic.

This does the work for multiple users at once, but you can add a where clause to the subquery to filter on a specific id if you like (in that case, the partition by clause becomes unnecessary).

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
GMB
  • 216,147
  • 25
  • 84
  • 135
  • You guys are lifesavers! Thank you so much! One more question, is it possible to make it pageable (if that is the right word)? So every time I make the request, I can (maybe with parameters) get it to get the next 7 days and the next etc? – Milan Dol Mar 21 '23 at 00:03
  • @MilanDol: `where rn between 8 and 14`? – GMB Mar 21 '23 at 15:20