On Apr 19, 2007, at 9:39 AM, Gaetan de Menten wrote:
> By the way, lately I've been wishing SQLAlchemy would add a column
> (and possibly its table) automatically to the select clause if I do an
> order by a column which is not in the currently selected columns.
>
> I mean that you could write:
>
> query(System).select(System.c.lastseen > self.this.week,
> order_by=[client.c.name])
>
> and it would figure out that the client.c.name is not in the
> selection, and would add it (or rather would add the join you describe
> above).
>
I would agree up to the point that the table gets added, which is
what happens if you add columns to the SELECT clause of a select; the
table gets appended to the FROM clause. but i dont agree in creating
JOIN objects automatically with no explicit specification that that's
whats desired (as usual, i am open to all sorts of explicit methods
of specifications...although we have plenty for specifying "join on a
relationship" at this point).
the query above I would express generatively as:
query(System).filter(System.c.lastseen > self.this.week).order_by
(client.c.name).join('clients').list()
Maybe it would also be handy to have join() accept a Table as well as
a Class argument, and have it figure out the auto-thing in those
cases as well. all that is fine with me (since theres no other
meaning you could get from join(SomeOtherClass) ).
>
> When using, the "SQL" layer of SQLAlchemy, I don't mind having to
> write the joins myself. But when using the ORM, I'd expect this to be
> done for me. And it would be even better if this worked also for
> relations and mappers. The use case I am most interested in (and I
> haven't found a nice solution for it yet) is the order_by argument for
> mappers.
>
> Yes, I know, that would mean I'd always have a join whenever I select
> from that mapper. Strange use case, but well, I need to do that
> sometimes.
The key phrase here is "sometimes". Lets talk about all those pesky
other times. assume autojoins.
MyClass-> table1
query(MyClass).select(table2.c.name=='foo')
query:
select * from table1 JOIN table2 on table1.t2id=table2.id
WHERE table2.name='foo'
Now, i want this query:
select * from table1, table2 where
table2.foobar > table1.lala and table2.name='foo'
How ?
should I parse through the criterion and "guess" when i detect a join
condition between table1 and table2, and thereby remove the auto-
condition ? (arent we supposed to refuse that temptation?)
well what about this query:
select * from table1, table2, table3 where
table2.foobar > table3.hoho and table3.xfactor=table1.id and
table2.name='foo'
Now theres *no* direct join between table1 and table2. Do i add the
autojoin ? do i have to figure out that it joins through some other
path ? what kind of algorithm is going to work in all cases
considering how complex SQL can get ? what if theres no path between
t1 and t2 at all (and thats what someone wants) ?
cant we just agree that adding the join automatically is an egregious
case of implicit over explicit ? considering that it is *so*
*easy* to join on a relationship now:
query(MyClass).join('somerelation').select(table2.c.name=='foo')
>
>> there is a way to get extra columns in the "SELECT" clause of a
>> mapper query in the most recent version of SA but thats not what
>> youre looking for here.
>
> Or is what I describe possible by using what you say in the above
> paragraph????
i was referring to a new method add_column() which is used to affect
the way you get the results back (as tuples which contain objects and
scalars).
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
