sector119 wrote:
>>>> meta.Session.query(model.Report).join((model.User,
>>>> model.Report.reporter_id==model.User.id)).filter(model.User.office_id==62).count()
> 18:08:29,568 INFO [sqlalchemy.engine.base.Engine.0x...888c] SELECT
> count(1) AS count_1
> FROM system.reports JOIN system.users ON system.reports.reporter_id =
> system.users.id
> WHERE system.users.office_id = %(office_id_1)s
> 18:08:29,569 INFO [sqlalchemy.engine.base.Engine.0x...888c]
> {'office_id_1': 62}
> 14L
correct. you are joining from Report to User and getting a count of
Report rows.
meta.Session.query(model.Report).options(eagerload('reporter')).filter(model.User.office_id==62).count()
> 18:09:08,488 INFO [sqlalchemy.engine.base.Engine.0x...888c] SELECT
> count(1) AS count_1
> FROM system.users, system.reports
> WHERE system.users.office_id = %(office_id_1)s
> 18:09:08,489 INFO [sqlalchemy.engine.base.Engine.0x...888c]
> {'office_id_1': 62}
> 42L
here, your query is incorrect. you are not specifying a join between
Report and User, and you are getting a cartesian product, hence 42
results. the usage of eagerload does not change those results. See
http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN
.
len(meta.Session.query(model.Report).options(eagerload('reporter')).filter(model.User.office_id==62).all())
> 18:09:22,053 INFO [sqlalchemy.engine.base.Engine.0x...888c] SELECT
...
> FROM system.users, system.reports LEFT OUTER JOIN system.users AS
> users_1 ON users_1.id = system.reports.reporter_id
> WHERE system.users.office_id = %(office_id_1)s
> 18:09:22,054 INFO [sqlalchemy.engine.base.Engine.0x...888c]
> {'office_id_1': 62}
> 14
Again, you are not specifying a join between Report and User, so you are
still getting a cartesian product here. But SQLAlchemy filters redundant
instances with the same primary key into a unique list so the end result
is 14. If you queried instead for model.Report.id you'd see 42 again.
Again, the eagerload() has no effect on the results.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
