[question] How to group data rows by day from datetime column?

[germandiagogomez]

I have this query:

    auto &mt = matchesTab;
    auto &mt2 = secondRoundMatchesTab;
    auto selectAllMatches =
        sqlpp::select(mt.matchToken, mt.userIdsOrderedByPlayerNumber,
                      mt2.endTime.as(end_time_second_round),
                      mt.endTime,
                      mt.startTime,
                      mt.scoreTeam1, mt.scoreTeam2,
                      mt2.scoreTeam1.as(score_team1_second_round),
                      mt2.scoreTeam2.as(score_team2_second_round))
        .from(mt.left_outer_join(mt2).on(mt.matchToken == mt2.prevRoundMatchToken)).
        order_by(mt.endTime.desc())
        .unconditionally();

Which shows this (and more irrelevant data to the right for this case):

I would like to show something like that table, but grouping in table/day, something like this:

20th-Oct-2024
   Row1
   Row2
   Row3
   ...
19th-Oct-2024
   Row1
   Row2
   Row3
   ...

But I just do not know how to do it or if it is even possible. I would say I want to use group_by (but that is for aggregates or all data columns must appear in SQL I think...) and I want that the group by day_of(mt.startTime).

Is this even possible or makes sense in SQL? I think SQL group_by is for aggregate data actually. The problem I am finding is that I do not know how to group per date day only in sqlpp.

The other problem (SQL-related) is that I do not know even if that is legal in SQL. Maybe with some subquery is possible?

[rbock]

Hi,

You are right, GROUP BY in SQL (and group_by in sqlpp11) aggregates rows that match the given criteria.

IIUC, you want to retrieve all rows but you want rows clustered by start date? That's what ORDER BY / order_by is for. You can order by multiple columns, e.g.

ORDER BY cast(start_time AS Date) DESC, end_time DESC -- untested cast syntax

That orders by start date and if there were several rows of the same start date it orders by end time.

Unfortunately, cast is not supported by the sqlpp11 today, so I see two options

1. implement cast, to support something like cast_as<sqlpp::day_point>(mt.startTime). This is non-trivial and I am quite preoccupied with the optional-no-dynamic branch these days (but feel free to give it a try, of course).
2. use sqlpp::verbatim should be straight forward.

Does this help?

Best,
Roland

[germandiagogomez]

@rbock yes it helps partially.

In fact, I did an order by and I think with that data I can already render things the way I want by traversing and grouping the rows and get it done.

So thanks for your help, at least it clarified how to go forward.

However, and out of curiosity, how could I implement custom functions? I saw some of the code in the repo.

I think it would be a great feature to have some class (like boost did for iterator, for example, or like C++ ranges did with https://en.cppreference.com/w/cpp/ranges/view_interface) to help define, in the easiest way, new functions.

Why I say this? Because, for example, being able to do this would be really nice:

day_of(mt.startTime)

where mt.startTime is a column. I mean, applying arbitrary functions (that exist in the SQL backend, of course) would be great.

By the way, great library! :)

EDIT: I think you replied to my question here:

implement cast, to support something like cast_assqlpp::day_point(mt.startTime). This is non-trivial.

Ok. I won't do it since I do not feel the need right now. But maybe (just maybe, sorry) in the future, I would be interested in making exposing SQL functions in a strongly-typed way easier. That would be a great addition to this library I think.

[rbock]

Thanks for the great feedback.

Adding custom functions isn't super hard, but undocumented. The easiest (IMO) way is to copy an existing function and modify it to do what you need.

To create a day_of function that hides away the gory details of the cast, you could use trim as your template for instance.

• Both functions take a single argument.
• Both functions require a certain input type (text vs date time)
• Both return a defined type (text vs date)

Writing a generic cast function is a bit more complex. verbatim might be a good starting point.

Ok. I won't do it since I do not feel the need right now. But maybe (just maybe, sorry) in the future, I would be interested in making exposing SQL functions in a strongly-typed way easier. That would be a great addition to this library I think.

Actually, future is probably better in this case, as I believe that this will become easier with the next major iteration of the library in the optional branch I mentioned above.

Cheers,
Roland

[germandiagogomez]

When is the next release planned? Feel free to close the issue. It is actually a non-issue anymore after your first comment.

[rbock]

I hope to get the "optional" branch ready this quarter, but it is more likely to happen in early 2025.

I'll leave this open as I might add the cast_as function to that branch as an exercise.