eagerload and count() doesn't use join

[sector119]

Hello!

Why second query show different from 1,3 query result? Why it doesn't
provide join?

>>> 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

>>> 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

>>> 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
system.reports.id AS system_reports_id, system.reports.reporter_id AS
system_reports_reporter_id, system.reports.owner_id AS
system_reports_owner_id, system.reports.type AS system_reports_type,
system.reports.version AS system_reports_version,
system.reports.component AS system_reports_component,
system.reports.priority AS system_reports_priority,
system.reports.status AS system_reports_status, system.reports.summary
AS system_reports_summary, system.reports.description AS
system_reports_description, system.reports.resolution AS
system_reports_resolution, system.reports.commit_date AS
system_reports_commit_date, system.reports.commit_time AS
system_reports_commit_time, users_1.id AS users_1_id, users_1.username
AS users_1_username, users_1.password AS users_1_password,
users_1.first_name AS users_1_first_name, users_1.last_name AS
users_1_last_name, users_1.middle_name AS users_1_middle_name,
users_1.locality_id AS users_1_locality_id, users_1.office_id AS
users_1_office_id, users_1.email AS users_1_email, users_1.create_date
AS users_1_create_date, users_1.last_login AS users_1_last_login,
users_1.expire AS users_1_expire
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

[Michael Bayer]

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.