Hello all,
I have a question about SQLAlchemy when dealing with joining on one-to-
many relationships.
When I try to load data with eager=True for relation with objects,
which subclasses from some base class, SQLAlchemy generates query with
subselect.
Subselects are quite painful for me, as I'm using MySQL so I can't
count on query-optymalization.
Is there some easy way to tell mapper to use outerjoins instead of
subselects?
Here is some simple example, that generates such a query:
let say we have 3 classes: Employee(object), Doctor(Employee),
MedCompany(object)
e_mapper = mapper( Employee,
employees,
polymorphic_on=employees.c.type,
polymorphic_identity='employee' )
d_mapper = mapper( Doctor,
doctors,
inherits = Employee,
polymorphic_identity='doctor' )
c_mapper = mapper( Company,
companies,
properties={
'employees' : relation(Doctor, lazy=False)
}
)
data = session.query(Company).all()
it generates something like:
SELECT companies... , anon_1...
FROM companies
LEFT OUTER JOIN (
SELECT employees... , doctors...
FROM employees INNER JOIN doctors ON employees.employee_id =
doctors.doctor_id
) AS anon_1 ON companies.company_id = anon_1.employees_company_id
One solution could be:
remove : "lazy=False" from c_mapper
and write query like:
data = session.query(Company).outerjoin(employees
).outerjoin(doctors
).options(
contains_eager('employees')
).all()
But is there any way to avoid writing such ugly queries?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~----------~----~----~----~------~----~------~--~---
