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.
