python - How to construct query using the SQLAlchemy ORM -

python - How to construct query using the SQLAlchemy ORM -

i have 3 models (simplified):

user:

id username

resource:

id name description

comment:

id user_id (relationship user) resource_id (relationship resource) info date_created

i trying query comments user , grouping them resource. i'd results come as: [(resource a, [comment, comment, comment, ...]), (resource b, [comment, comment, ...]), (resource x, [comment])]

i have tried various ways of constructing , can't seem figure out. proper way this?

edit

right code looks this:

contrib = db_session.query(resource).filter(comment.user==user, resource.uuid==comment.resource_id).distinct(comment.resource_id).order_by(desc(comment.date_created)) comments = db_session.query(comment, resource).filter(comment.user==user, comment.resource_id.in_([r.uuid r in contrib]), resource.uuid==comment.resource_id).order_by(desc(comment.date_created))

i utilize list/dictionary comprehension combine these results looks

[{resource: resource, comments:[comment, comment, comment]}, {resource: resource, comments:[comment, .....]}, .....]

there has got improve way this!

you utilize custom mappedcollection grouping comments:

from sqlalchemy.orm.collections import collection, mappedcollection class groupedcollection(mappedcollection): def __init__(self): super(groupedcollection, self).__init__( self, lambda e: e.resource_id # key want grouping ) @collection.internally_instrumented def __setitem__(self, key, value, _sa_initiator=none): if key in self: # there comment resource # append comment (or # more fancy here if order comments) self[key]['comments'].append(value) else: # create new entry dictionary containing # resource , comment super(groupedcollection, self).__setitem__( key, {'resource': value.resource, 'comments': [value]}, _sa_initiator )

you add together corresponding relationship on user class:

class user(base): # ... grouped_comments = relationship( 'comment', collection_class=groupedcollection )

accessing give comments grouped resource:

>>> user.grouped_comments { 'resource_id_1': {'resource': <resource 1>, 'comments': [<comment ...>, <comment ...>]}, 'resource_id_2': {'resource': <resource 2>, 'comments': [<comment ...>]} } >>> user.grouped_comments.values() [ {'resource': <resource 1>, 'comments': [<comment ...>, <comment ...>]}, {'resource': <resource 2>, 'comments': [<comment ...>]} ]

note relationship should used view related models, enabling adding/deleting models require work.

finally, if pattern reproduce, can create groupedcollection mill function can specify grouping key.

python mysql sqlalchemy