On Feb 11, 2008, at 10:25 PM, Richard Levasseur wrote:
>
> Ok, so I tried this. It works fine, the only catch is that yeah,
> the pid and cid are hardcoded in there. I couldn't figure out any
> way to determine them programatically. I can get the join condition
> clause, but its just a binary expression object (essentially saying
> "pid = cid"), and I don't know how to extract the portions reliably
> (from the sounds of things, it doesn't sound possible at all). It
> looks like cid is always on the right, and pid is always on the
> left, but I'm guessing thats just deterministic chance, and wouldn't
> know what to do when its a more complicated expression. This
> worries me because I know there are other tables that will require
> more complicated join conditions (where deleted/archived/hidden == 0
> or IS NULL, or some such thing)
to programmatically determine columns on a table, use the table.c
collection. the direction of the "join" clause is not really
important here (dont see how thats related?) it should be easy enough
to just say:
for c in childtable.c:
setattr(instance, c.name, None)
i.e. when the row does not contain columns from "childtable".
A little bit of experimentation with the Table construct should reveal
that pretty much anything is possible there.
> It looks like the nested post_execute def has a reference to
> `statement` that it uses to figure out the join condition and issue
> the subquery. If I could simply call that inside my extension's
> populate_instance and handle the exception, that'd probably work.
sure, try calling that. But then, you could also adapt the source
code of that to do more specifically what you need.
> Ok, let me give a more practical example:
>
> Lets say we have the following schema:
> Persons(pid, etype, name, is_active)
> Managers(pid, mid, level, full_team)
> Engineers(pid, eid, language)
>
> Lets say there's a single search on the webpage and users can enter
> in queries like:
> 1) engineer.language:Java OR manager.level:5
> 2) name:john
> 3) engineer.language: python
>
> the psuedo-sql for those queries should be something like
> 1) select * from persons left join engineers on pid=pid left join
> managers on pid=pid and full_team=0 where engineers.language="Java"
> or managers.level=5
> 2) select * from persons where name ='john''
> 3) select * from persons left join engineers using pid where
> engineers.language='python'
If in the above example the mapper were configured with select_table,
mapper(Person, people,
select_table=people.outerjoin(engineers).outerjoin(managers))
you can filter on those columns directly:
sess.query(Person).filter(or_(Engineer.language=='java',
Manager.level==5))
if you don't want to use select_table, then you can set up the joins
on a per-query basis:
sess
.query
(Person
).select_from
(people
.outerjoin
(engineers).outerjoin(managers)).filter(or_(Engineer.language=='java',
Manager.level==5))
theres a query.join() call but that currently is used for joining
along relations between classes, which is not quite what we have here.
As far as the mappers seeing "Engineer.language" and magically knowing
to add "engineers" to the base table of "people" on its own that seems
a little magical to me (im not sure how it could guess the desired
action here....like how would it know to "outerjoin" and not "join" to
engineers ? how would it know that you didnt join to "engineers" in
some other way already ?). For reference, Hibernate inheritance could
never do that; it would require that you query specifically for
Engineer before specifying Engineer-specific criterion.
> Note that for (1), it has the additional full_team=0 condition as
> part of the join itself.
> So, depending on the user's query, we need to join to different
> tables.
yeah in any case, you'd need to specify that. Even with joins along
relations, we still require that you say query.join('relationname'),
which is less verbose than using the full join condition but still
requires explicitness. We dont "guess" joins at the query level. The
best we can do is some eventual feature like
query(Person).polymorphic_with(Engineer).filter(....), something like
that.
>
> For the api, it'd be nice to do something like:
> session
> .query(Person).filter(Manager.level=5).filter(Person.is_active==1)
> And the orm uses the join conditions we defined elsewhere (probably
> on the mapper?).
if you said
session
.query
(Person
).filter(Manager.person_id==Person.person_id).filter(Manager.level ==
5), then you've already joined to Managers. Thats what I mean by "we
can't guess".
>
> Right now, it'll join to the tables it needs, but it won't put in
> the join conditions. I know its in there somewhere, otherwise it
> couldn't do the subquery (...right?). Another catch I'm seeing is
> how to define those additional join conditions (use select_table
> with a custom condition?). It looks like its magically picking up
> the foreign key references between Managers/Engineers and
> Persons....just wish I knew how to tell it that explicitly (since
> some things have additional join conditions).
the join condtion is the second argument to table.join() :
people.join(engineer, people.c.id==engineer.c.person_id)
> Would it be better to abandon the orm portion of this, and use
> select() instead? I'd really like to avoid that since thats
> essentially what we're doing now in php, and it works, but its messy
> at all levels.
if you made yourself a select(), you could just feed it back into
query.from_statement() and get instances back, so sure if you prefer
that. The idea of SA is that you would be hand-creating SQL clauses
for more elaborate cases....its from a "I know what SQL i want"
perspective. Theres a little bit missing here with regards to a
"polymorphic_with" method but the original idea is that its not too
hard to pass in the joins to select_from().
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
