Great thanx for the answer,
I have tried your suggestion with AliasedClass and it works fine for
me, but unfortunately on my production machines I have version 0.4.4
of SA and AliasedClass is in 0.5.x .
I think I will rebuild my model into single inheritance.
On Jan 21, 8:38 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> If I make a slight change to the particular line of code which
> generates this subquery:
>
> Index: lib/sqlalchemy/orm/strategies.py
> ===================================================================
> --- lib/sqlalchemy/orm/strategies.py (revision 5711)
> +++ lib/sqlalchemy/orm/strategies.py (working copy)
> @@ -673,7 +673,7 @@
> towrap = context.eager_joins.setdefault(entity_key,
> default_towrap)
>
> # create AliasedClauses object to build up the eager query.
> - clauses =
> mapperutil.ORMAdapter(mapperutil.AliasedClass(self.mapper),
> + clauses =
> mapperutil.ORMAdapter(mapperutil.AliasedClass(self.mapper,
> self.mapper._with_polymorphic_selectable),
> equivalents=self.mapper._equivalent_columns)
>
> running a simple test case of Company->eagerload->Engineer(subclass of
> Person) leads to this query:
>
> SELECT companies.company_id AS companies_company_id, companies.name AS
> companies_name, people.person_id AS people_person_id,
> engineers.person_id AS engineers_person_id, people.company_id AS
> people_company_id, people.name AS people_name, people.type AS
> people_type, engineers.status AS engineers_status,
> engineers.engineer_name AS engineers_engineer_name,
> engineers.primary_language AS engineers_primary_language
> FROM companies LEFT OUTER JOIN (people JOIN engineers ON
> people.person_id = engineers.person_id) ON companies.company_id =
> people.company_id
>
> which in fact fails to execute on SQLite. Additionally, the
> "people" and "engineers" tables are not aliased, meaning if your query
> happened to already be joined to "people" or "engineers", the
> eagerload would ruin the query. The query above might work on PG or
> MySQL, but still breaks the general contract expected by the automatic
> eagerloading feature. Many tests would fail.
>
> This is just one example of a whole universe of reasons why wrapping
> any mapped "selectable" within a single named unit allows a tremendous
> amount of capability with regards to generation of queries without
> direct user involvement, with a reasonable level of code complexity
> and a great degree of stability. Similar logic exists if you said
> query(Company).outerjoin(Company.employees) - the "employees"
> collection, being mapped to a join, would be wrapped in a subquery.
> If I comment out the logic which does that, lots of tests fail
> particularly within the inheritance tests, including when run on MySQL
> or Postgres, which supports the nested JOIN syntax, yet the particular
> quirks of the query being tested fail to return the expected results
> when the encapsulation of sub-selectables is removed.
>
> This is not to say that conditional logic to adequately detect when we
> can "skip" the subquery without impacting results couldn't be
> added. From poking around the code a bit, it seems like it could be
> doable - although the logic would have to take place only for certain
> dialects, in certain circumstances, and particularly with regards to
> the aliasing of an eager loaded selectable it still may be very
> difficult to deal with that. There are also issues which would need
> to be solved on the SQLA side regarding detection of join conditions
> to a Join object as opposed to an alias() of that join - again
> probably solvable but not working at the moment.
>
> While I'm shaking my head saying its not worth it, I would welcome
> your input into the current codebase if you'd like to help work up
> test cases and such for this capability - I'm always open to potential
> improvements.
>
> But when you consider that upon embarking upon that very significant
> and code-destablizing task, the only reason it even needs to be
> considered, versus remaining with the current very clean, stable, well
> tested, and relational approach, is because *MySQL alone can't
> properly optimize subqueries*, it begins to seem like a waste of
> time. There are other free and open databases that perform better
> than MySQL and don't have these issues (namely Postgres). There are
> ways which SQLA allows you to write your own query for performance-
> critical sections if needed such as the one which you mentioned.
> you can also do it this way (again, would fail on SQLite):
>
> a = aliased(Engineer, people.join(engineers))
> s.query(Company).outerjoin((a,
> Company.employees)).options(contains_eager(Company.employees,
> alias=a)).all()
>
> the above approach can also be distilled into a single function to cut
> down on redundancy:
>
> q = eagerload_engineers(q)
>
> But for MySQL in particular, I would advise not using joined table
> inheritance at all, in favor of single table inheritance. The last
> project I did was with MySQL and we just used single inheritance, and
> it runs great - I made lots of improvements to the single inheritance
> system to support it. MySQL just has too many issues with joins and
> subqueries in general such that a more normalized approach is not
> really worth it. While increased complexity to SQLA might allow SQLA
> to better work around MySQL's specific issues, it starts to feel like
> SQLA, a project that earns about $500 a year in donations, is taking
> responsibility for an issue that MySQL, a product that has received
> $1B from Sun, should really be addressing itself.
>
> On Jan 21, 2009, at 1:25 PM, Tomasz Domanski wrote:
>
>
>
> > 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
-~----------~----~----~----~------~----~------~--~---
