How to group by a fk and laterally join to the same foreign table

[michaelmcandrew]

Hi there,

This is a lovely project :)

I'm wondering how I might best construct a query that groups by a foreign key and also laterally joins to that foreign table to select some more info from it.

In the (simplified) example below I am grouping time spent data (fwiw from a Gitlab database) by day and by issue in prep for doing some pivot table like analysis.

const timeLogs = await db.select('timelogs', db.all, {
    columns: [],
    extras: {
        time_spent: db.sql<s.timelogs.SQL, number>`sum(${"time_spent"})`,
        day: db.sql<s.timelogs.SQL, string>`date_trunc('day',${"spent_at"})`,
    },
    groupBy: db.sql<s.timelogs.SQL, string>`date_trunc('day',${"spent_at"}), lateral_issue.result`,
    lateral: {
        issue: db.selectExactlyOne('issues', { id: db.parent('issue_id') }, { columns: ['id', 'title'] }),
    },

The above works, but a couple of things I am wondering about.

• should there be a nicer way to reference lateral_issue.result in the group by? Maybe something like ${db.lateral('issue'}
• is it actually a good idea to group by this column? If I don't do so, I get a column "lateral_issue.result" must appear in the GROUP BY clause... error but it seems odd and perhaps incorrect and perhaps not performant to group by an arbitrary json object.
• am I approaching the limits of what it makes sense to try and build with shortcut functions and should I switch to arbitrary SQL, at least for some of this query.

I've tried the arbirary SQL for the base table approach but am not sure if there is a neat way of adding lateral joins to a fragment.

For example, here is a fragment that could be used as the 'parent' select:

const fragment = db.sql<s.timelogs.SQL, { user_id: number, issue_id: number, project_id: number, day: string, minutes: number, }[]>`SELECT
    ${"user_id"},
    ${"issue_id"},
    ${"project_id"},
    date_trunc('day', ${"spent_at"}) AS day,
    sum(${"time_spent"}) AS time_spent
FROM timelogs
GROUP BY
    ${"user_id"},
    ${"issue_id"},
    ${"project_id"},
    date_trunc('day', ${"spent_at"})`

If there was a way that I could pass this to db select as the first parameter (instead of the table name string) and then continue adding the lateral joins as in the first example (but this time without the need for the group by) that would feel quite ergonomic, but I don't think that is possible at the minute.

But perhaps there are other ways of achieving the same result, e.g. other ways of augmenting arbitrary SQL with lateral joins, or ways to write lateral join fragments that I could stick insert into the sql tagged template string.

The other alternative I have considered is creating a view based on this fragment. I could do this as a last resort but I would like to avoid doing so if possible since I would prefer to treat this database as read only and not alter its schema.

Hopefully the above is clear and not too esoteric a question. Let me know if you would like more info or better explanation.

PS. Also happy to come up with alternative approaches and ways to think about the problem - e.g. I could always just split it up into multiple queries - just want to make sure I am not missing anything.