> -----Original Message-----
> From: [email protected]
> [mailto:[EMAIL PROTECTED] On Behalf Of SinJax
> Sent: 21 November 2008 10:43
> To: sqlalchemy
> Subject: [sqlalchemy] Re: ORM mapping with Elixir compared to
> raw cursor query
>
[SNIP]
> sql = """
> SELECT annotations.id AS annotations_id, annotations.user AS
> annotations_user, annotations.subject AS annotations_subject,
> annotations.field AS annotations_field, annotations.value AS
> annotations_value, users_1.id AS users_1_id, users_1.user AS
> users_1_user, users_1.pass AS users_1_pass, users_1.lab_id AS
> users_1_lab_id, users_1.email AS users_1_email, groups_1.id AS
> groups_1_id, groups_1.name AS groups_1_name, subjects_1.id AS
> subjects_1_id, subjects_1.leg_id AS subjects_1_leg_id,
> subjects_1.`displayOrder` AS `subjects_1_displayOrder`, fields_1.id AS
> fields_1_id, fields_1.name AS fields_1_name, fields_1.help AS
> fields_1_help, values_1.id AS values_1_id, values_1.`parentId` AS
> `values_1_parentId`, values_1.field AS values_1_field, values_1.name
> AS values_1_name, values_1.image AS values_1_image, values_1.`order`
> AS values_1_order
> FROM users, subjects, annotations
> LEFT OUTER JOIN users AS users_1 ON users_1.id =
> annotations.user
> LEFT OUTER JOIN usergroup AS usergroup_1 ON users_1.id =
> usergroup_1.`userId`
> LEFT OUTER JOIN groups AS groups_1 ON groups_1.id =
> usergroup_1.`groupId`
> LEFT OUTER JOIN subjects AS subjects_1 ON subjects_1.id =
> annotations.subject
> LEFT OUTER JOIN `fields` AS fields_1 ON fields_1.id =
> annotations.field
> LEFT OUTER JOIN `values` AS values_1 ON values_1.id =
> annotations.value
> WHERE users.user = '%s' AND subjects.id = %s
> ORDER BY annotations.id, users_1.id, usergroup_1.`userId`,
> subjects_1.id, fields_1.id, values_1.id
> """%("msn",2)
[SNIP]
Your problem is the 'FROM users, subjects, annotations' part of that
query. There is no join condition between the three tables, so you are
getting a cartesian product, and a very large number of rows.
In your query, you need to explcitly join to other tables before you use
them as filters.
For example, something like:
allAnn = (Annotation.query()
.join(Annotation.user)
.filter(User.py_user == "msn")
.join(Annotation.subject)
.filter(Subject.id == 2 ))
This page in the docs describes querying with joins:
http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_joins
Hope that helps,
Simon
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
