Hey Rick, Basically the issue as I see it is that whatever decides that something is a 'column', and thus part of the result set, doesn't (for the obvious reasons) know what to do with the CompoundClause. Now this might be rectified as simply as implementing a new class, say, arbitrarily, 'CalculatedClause' that implements or derives from ColumnClause providing interfaces like _get_label, visit_column for the callback, and the ability to specify a type for the result (column.type). I'm not very familiar with the internals, but just glancing through the code this seems like the right direction to go, if you're willing :) If you're busy, let me know and I'll see about cornering mike into giving me his opinion :)
Cheers! -G On Sunday, May 14, 2006, 4:50:41 AM, you wrote: > Hey Gambit, > Yeah, we'll need a way to alias the column created by the subquery. Have you > tried using the "use_labels" switch on the inner query? > Rick > On 5/13/06, Gambit <[EMAIL PROTECTED]> wrote: >> >> Hey Rick, >> >> I was playing with this patch a bit and noticed that while it seems pretty >> good for select()'s, it falls apart in sub-selects. It doesn't propagate >> as >> a column, thus doesn't get an alias for the outer select to work off of, >> and >> it all falls apart :) >> >> Hopefully there's an easy way to solve this :D Test case enclosed! Patch >> as >> you included it in your mail was applied against rev 1453. >> >> Cheers! >> -G >> >> On Friday, May 12, 2006, 12:17:18 AM, you wrote: >> > Hey Mike: >> >> > Attached please find a patch against trunk r#1441 that implements the >> SQL >> > CASE construct. >> >> > I know you've argued in the past for an implementation in some kind of >> > .ext-land, but let me at least present a few arguments for inclusion in >> the >> > core library. I'll do this in the form of responses to some points in >> your >> > latest post on this subject way back on St. Patrick's day (begora): >> >> > On 3/17/06, Michael Bayer <[EMAIL PROTECTED]> wrote: >> >> >> >> I love the CASE statement, but yah I think its a cleaner in a view >> >> >> >> > Well, only if the condition and result expressions used are invariant. >> If >> > that's the case, you may as well use a simple lookup table in a join. In >> my >> > particular use case, the condition and result expressions used in the >> WHEN >> > clauses change on almost every single query. >> >> > (also what DB's support this one ? let me guess, PG and Oracle and >> thats >> >> it....oh and MSSQL-9.nobodyhasit :) >> >> >> >> > ...actually CASE is pretty well supported across the board, PG, Oracle, >> > SQLite, MySQL all support it, and MS-SQL has had it since like version >> > 6.5back in 1996. >> >> > ). You can use literal strings too for this kind of thing. >> >> > Sure but that's effective only if the expressions are rather trivial. >> Not >> > having to build out string representations of SQL is one of the reasons >> I >> > use SA in the first place! :-) >> >> >> > if you have some brilliant notion of how this would even look as Python >> >> expressions, that would be interesting to see. It would definitely >> live as >> >> a plugged-in extension. I should make an effort to formally define the >> >> "extensions" idea I have so that people can contribute whatever plugins >> they >> >> want. >> >> >> >> >> > Well, I am certainly not claiming brilliance, but I will stand up for >> > "stupid simple". The meat of the patch is only about 10 lines long. >> >> > Here's a short doc: >> >> > case(whenlist, [value=value-expr], [else_=else-expr]) >> >> > whenlist: A list of pairs. >> > Each pair is itself a sequence or iterator yielding a >> > sequence >> > of two elements. >> >> > Each two-element pair represents a "WHEN" block of an SQL >> > CASE expression. The first element of the pair is an >> > expression >> > that represents the WHEN condition, and the second is an >> > expression that represents the THEN result. >> >> > The optional [value] expression, if present, sets up a >> > "simple case" SQL clause. If present, then each condition >> > in the whenlist must evaluate to a constant value to >> which >> > the >> > result of the value-expr is compared. >> >> > The optional [else_] expression represents the optional >> > "ELSE" >> > clause in the SQL CASE construct. >> >> >> > Some examples: >> >> > case([[MyTable.c.MyColumn < 2, 'First Bucket'], [ >> MyTable.c.MyColumn >>= 2, 'Second Bucket']]) >> >> > yields: >> > CASE WHEN MyTable.MyColumn < 2 THEN 'First Bucket' >> > WHEN MyTable.MyColumn >= 2 THEN 'Second Bucket' >> > END >> >> > ------------------------------------- >> >> > case([(100, 'one hundred'), (5, 'five'), (82, >> > 'quatre-vingts deux')], >> > value=MyTable.c.MyColumn, >> > else_='some other number') >> >> > yields: >> > CASE MyTable.MyColumn >> > WHEN 100 THEN 'one hundred' >> > WHEN 5 THEN 'five' >> > WHEN 82 THEN 'quatre-vingts deux' >> > ELSE 'some other number' >> > END >> >> > OK, this doc isn't really correct, as proper literal string quoting >> requires >> > the literal() function, but you get the idea. >> >> > CASE is a pretty basic construct, and I think it belongs right up there >> with >> > our friends DISTINCT and OUTER JOIN in the main library. Can ya spare a >> guy >> > space for 10 lines of code? >> >> > Thanks, >> > Rick >> >> ------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
