On 6/1/2010 7:18 AM, nospam wrote:
I see significant slow down if I have 1000 items, and a couple of
annotations for each one. That's a 1001 queries I need to make, so I
want to avoid the for loop there - and just have 1 query that returns
me the list of correct annotation objs.
I think this is basically the "give me the last order each user
placed" problem, but w/ more complexity around the "last order"
definition.
http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html
I'm not as hip on the whole "selectable" stuff and where it should be
used in place of the ORM, since I usually want to interact with objects
instead of rows, but the following will hopefully get you closer to your
goal (using the ORM):
orm.mapper(
Item, items,
properties = dict(
annotations = orm.relation(Annotation,
order_by = [annotations.c.creation_datetime.desc()],
primaryjoin = sa.and_(annotations.c.item_id == items.c.id,
annotations.c.type.in_(('x','y'))),
lazy = False,
),
),
)
So this does a couple of things...first of all the /opposite/ of
lazy='select' since apparently you want the annotations loaded via the
same query that pulls in the items; also it filters the annotation.type
to only include ('x', 'y'). BTW also I used lazy=False instead of
lazy='joined' - I hadn't previously realized the SA 0.5 vs 0.6
difference with regard to that (lazy=False will work the same for both).
Obviously one drawback is that the annotations property is now
restricted with the type filter, so if you ever /did/ want to see all
the possible annotations then you'll need to create a second property
for that. Also, I personally am not familiar enough to say whether or
not this mapping should specify viewonly=True ... I'd think so but the
docs suggest complicated joins may not compile properly? Not sure what
that means specifically.
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.
