Create "rotated" non-buffered grid (Vaadin)

Question

I am thinking about the solution of this problem, but I have no idea how to do this:

I have entity SchoolBook with this properties:

Long id;
String studentName;
Long studentId;
LocalDate day;
String points;

SchoolBook using data from database, and they looks like:

id | studentName |  studentId |   day     | points
----------------------------------------------------
1  |    Adam     |   1000001  | 1.9.2022  |   1
2  |    Adam     |   1000001  | 3.9.2022  |   2
3  |    Adam     |   1000001  | 6.9.2022  |   3
4  |    Marc     |   1000002  | 2.9.2022  |   2
5  |    Marc     |   1000002  | 5.9.2022  |   A
6  |    Marc     |   1000002  | 30.9.2022 |   3
7  |    Fred     |   1000003  | 2.9.2022  |   C
8  |    Fred     |   1000003  | 4.9.2022  |   1
9  |    Fred     |   1000003  | 4.8.2022  |   1
10 |    Adam     |   1000001  | 6.8.2022  |   F

The alphabetical characters can be represented in numeric value as

A (10 points), B (9 points), C (8 points), D (7 points), E (6 points), F (5 points)

Now I would like to do something like this:

• column names in grid will be the names of students
• first column will have automaticly generated days (dates) for selected month
• "rows" will have the points from SchoolBook table - each for corresponding day

The last row will be something like "sum row" showing the sum for each student during the actual month.

So I have created the SyntheticRow class with these properties:

Long syntheticRowId;
LocalDate date;
Map<Long, String> map;

After this I created the SyntheticRowService which creates items for grid with these functions:

public int getNumberOfDaysInMonth(int month, int year) {
    YearMonth yearMonthObject = YearMonth.of(year, month);
    return yearMonthObject.lengthOfMonth();
}

public List<SyntheticRow> getDataForSpecificMonth(PageRequest of, int month, int year) {

    List<SyntheticRow> syntheticRows = new ArrayList<>();

    int syntheticRowId = 1;

    for (int i = 1; i <= getNumberOfDaysInMonth(month, year); i++) {
        List<SchoolBook> schoolBooks = repository.findByDay(LocalDate.of(year, month, i));

        for (SchoolBook schoolBook : schoolBooks) {
            SyntheticRow syntheticRow = new SyntheticRow();
            syntheticRow.setDate(LocalDate.of(year, month, i));
            syntheticRow.setSyntheticRowId(Long.valueOf(syntheticRowId));

            Map<Long, String> map = new HashMap<>();
            map.put(schoolBook.getStudentId(), schoolBook.getPoints());

            syntheticRow.setMap(map);
            syntheticRows.add(syntheticRow);
            syntheticRowId++;
        }

    }

    return syntheticRows;
}

Now I have grid with loaded data:

List<Student> students = userService.getAllStudentsFromClass(classId); //function returns all students from class (it contains also students without records in SchoolBook)

grid.setItems(query -> syntheticRowService.getDataForSpecificMonth(PageRequest.of(query.getPage(), query.getPageSize(), VaadinSpringDataHelpers.toSpringDataSort(query)),9,2022).stream());
grid.addColumn(SyntheticRow::getDate).setHeader("Date");

for (Student student : students) {
    grid.addColumn(syntheticRow -> syntheticRow.getMap().get(student.getStudentId())).setHeader(student.getStudentName() + " (" + student.getStudentId() + ")");
}

Now I get this result:

The problem now is, that I have data in multiple rows, not only in one, for specific day.

E.g. date 2022-09-23, or date 2022-09-19.

So the data should be in one row and divided into the specified column for studentName/studentId. And some dates are missing - like 2022-09-14, 2022-09-15, 2022-09-16.

Solution:

public List<SyntheticRow> getDataForSpecificMonth(PageRequest of, int month, int year) {

    List<SyntheticRow> syntheticRows = new ArrayList<>();
    for (int i = 1; i <= getNumberOfDaysInMonth(month, year); i++) {
        List<SchoolBook> schoolBooks = repository.findByDay(LocalDate.of(year, month, i));

        SyntheticRow syntheticRow = new SyntheticRow();
        Map<Long, String> map = new HashMap<>();

        schoolBooks.setDate(LocalDate.of(year, month, i));

        if(!schoolBooks.isEmpty()) {
            for (SchoolBook schoolBook : schoolBooks) {
                map.put(schoolBook.getStudentId(), schoolBook.getPoints());
            }
        }

        syntheticRow.setMap(map);
        syntheticRows.add(syntheticRow);
    }

    return syntheticRows;
}

Answer 1

Group your data by date, then by student. Create a synthetic row class, that has a date and a map from student to points. Make the grid using this row class. Add the date column and then add for each student a column, that extracts the score by student from the map in the row.

If you don't know all students beforehand, build a (sorted) set from the original data or the union of the key-sets of all row's student maps.

Assuming, that your data is most likely sparse and roughly 30x30, you can do this eagerly. If your database can already provide a pivot view of the data, use that directly.