[FIXED] How correctly map values from resultset to html table with thymeleaf

Issue

I have a program of bikes rent. In database I have table with all bike info(bike table) and table with bike status changes which includes key bike_id. I need to make a table on page which shows all bike status changes during period of time which is always equals calendar week. Status change can be everyday or status can leave the same during whole week.

Table should look like:

bike	Monday	Tuesday	Wednesday
One	status1	status2	status2.

I mean that if some bike have last status change on Tuesday so table should be filled till the end of week with his last status.

I made sql query which returns me data with status change in time period or last change before it

with statusVersion as (
   select
     row_number()
       over (
        partition by bike_id
        order by date desc)
     , *
        from bike_status s
        inner join bike b on b.id = s.bike_id
        inner join rent r on r.id = s.rent_id
        where date < '2022-05-11'
    )
    select row_number, b.id, s.status, s.date, r.driver_name,    r.driver_surname
    from statusVersion
where date between '2022-05-03' and '2022-05-10' OR row_number = 1;

This query I'm mapping to spring data projection interface with values:

interface BikeWeek{
   int getRow();
   long getId();
   String getStatus();
   Date getDate();
   String getName();
   String getSurname();
}

My Controller:

List<BikeWeek> weekList = bikeRepo.weeklyBikes();
List<Bike> bikes = bikeRepo.findAll();
model.addAttribute("week", weekList);
model.addAttribute("bikes", bikes);

It's like my current html code looks like. Where bikes is a list of all bikes from DB.

<tr th:each="bike, bStat:${bikes}">
   <td th:text="${bike.name}+' '+${bike.number}"></td>
</tr>

The problem is that I don't know how to correctly map values from result set to columns in table? And copy last bike status if there is no status for that date. It returns me correct results that I want. So question is in how I can put it on page in format that I showed before?

Solution

Solved problem by adding multiply if statements in thymeleaf and additional iteration through dates from dates range.

Answered By - Vitalii Pshenychniuk
Answer Checked By - David Marino (JavaFixing Volunteer)