Thanks Lance. Would this lazily load the annotations for each item?
I'm trying to avoid "# of item" trips to the db, and also avoid
loading all the annotations.for each item. I'm thinking I can do w/
some joins, and subquery()'s...
Cheers,
Lars
On May 29, 2:58 pm, Lance Edgar <lance.ed...@gmail.com> wrote:
> On 5/29/2010 11:59 AM, nospam wrote:> I'm trying to make the below logic into
> 1 query, so I don't have to
> > run an individual query for each item; but can't figure out how to do
> > it in sqlalchemy. Any ideas what the code would look like?
>
> > results = []
>
> > items = session.query(Item).filter(Item.type == "person")
>
> > for item in items
> > annotations = session.query(Annotation)
> > .filter(Annotation.item_id == item.id)
> > .order_by(Annotation.creation_datetime.desc())
>
> > annotationResult = None
>
> > for annotation in annotations:
> > if annotation.type == "x" or annotation.type == "y"
> > annotationResult = annotation
> > break;
> > if annoationResult == None
> > annotationResult = annotations[0]
>
> > results.append(annotationResult)
>
> > return results
>
> In my mind the final code would look like this:
>
> def your_function():
> results = []
> session = Session()
>
> for item in session.query(Item).filter(Item.type == 'person'):
> annotation_result = None
> for annotation in item.annotations:
> if annotation.type in ('x', 'y'):
> annotation_result = annotation
> break
> if annotation_result is None and item.annotations:
> annotation_result = item.annotations[0]
> if annotation_result:
> results.append(annotation_result)
>
> session.expunge_all()
> session.close()
> return results
>
> But of course that assumes that some relationship mapping has been done
> ahead of time, for instance:
>
> import sqlalchemy as sa
> from sqlalchemy import orm
>
> metadata = sa.MetaData()
>
> items = sa.Table(
> 'items', metadata,
> sa.Column('id', sa.Integer, primary_key=True),
> sa.Column('type', sa.String(20)),
> )
>
> annotations = sa.Table(
> 'annotations', metadata,
> sa.Column('id', sa.Integer, primary_key=True),
> sa.Column('item_id', sa.Integer, sa.ForeignKey('items.id')),
> sa.Column('type', sa.String(1)),
> sa.Column('creation_datetime', sa.DateTime),
> )
>
> class Item(object):
> pass
>
> class Annotation(object):
> pass
>
> orm.mapper(
> Item, items,
> properties = dict(
> annotations = orm.relation(Annotation,
> order_by=[annotations.c.creation_datetime.desc()]),
> ),
> )
>
> orm.mapper(
> Annotation, annotations,
> )
>
> Session = orm.sessionmaker()
>
> Hope this helps.
>
> Lance
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
