CODEWITHSUNDEEP
pythonsqlalchemy
DantheMan
DantheMan

Reputation: 7417

Sqlalchemy -- Group by a relationship field

I wish to construct a query that has something like this

I would have 2 models A, B

B has a many to one relationship with A (There are many B's to one A).

I want my query to return something like this [(A, [B, B, B]), (A, [B, B]), etc...]

Where the first element of the tuple is an A class and the second is a list (or query) of all the B elements that have a relationship to the A class.

I have tried to use group_by but then it just ends up as [(A, B), (A, B), (A, B), ...]

Note: I dont want to simply iterate over all a's and then query the B's since an A might not have a B. What I would like to do is 

*  filter all B's by a datestamp
-> group those B's by A
-> return an iterable object tuples as described above

Is there a way to do that all in a SQL statement? I found like func.count, that would work if I wanted to count how many B's each A has, but I need to iterate over the B's.

Upvotes: 4

Views: 2414

Answers (1)

van
van

Reputation: 77012

No, you cannot achieve this by simply using group_by. This is best done using plain python:

from sqlalchemy.orm import joinedload
from collections import defaultdict

def handle_request(datestamp):
    """ Return dict(A, [Bs]) for Bs filtered by *datestamp*.  """
    qry = session.query(B).options(joinedload(B.A)).filter(B.datestamp > datestamp)
    res = defaultdict(list)
    for b in qry:
        res[b.A].append(b)
    res = dict(res)
    return res

datestamp = datetime.datetime(2001, 1, 1)
res = handle_request(datestamp)
for a, bs in res.items():
    print a, bs

Upvotes: 2

Related Questions