List aggregate

[powellnorma]

Let's say I have

class Subject(Table):
    name = Varchar()

class Event(Table):
    subject = ForeignKey(Subject)
    timestamp = Timestamptz()
    data = JSONB()

How can I fetch all Subject with corresponding Events given a particular timerange? It should be possible with a 'ReverseLookup' (#599), when we use a 'RIGHT JOIN' to the subquery, I think?

Alternatively an list aggregate Function could be helpful:

1. Events.select and group_by subject + list aggregate the rest (e.g. with json_agg in postgres)
2. Join Subject data inside that Events.select query

Is there currently a way to do this? Thank you!

[powellnorma]

It should be possible with a 'ReverseLookup' (https://github.com/piccolo-orm/piccolo/pull/599), when we use a 'RIGHT JOIN' to the subquery, I think?

Alternatively, would it be possible to use the ReverseLookup in the where clause? So that where and select can use two different Subqueries (ReverseLookups)

[dantownsend]

Yeah, it's not possible with a single query currently.

Something like this should work:

events = await Event.select(
    Event.subject.name.as_alias('event_name'),
    Event.data
).where(Event.timestamp > some_timestamp).order_by(Event.subject)

grouped = {
    key: list(group) for key, group in itertools.groupby(
        events,
        lambda event: event['event_name']
    )
}