Hey All,
Sort of related to this is, for sub-selects again, the option of specifying
labels for other compound clauses, such as and_() and or_(). The following
snippet of code works fine:
valid_clause = and_(source.c.ts >= from_ts)
items = select([sql.CalculatedClause('lbl', valid_clause).label('lbl'), ...
but, as you can see, lacks a bit in elegance. I'd like for there to be a
clean consistent way to create an 'aliased' or 'labeled' column based on a
given clause, as above. Preferably without having to specify the label itself
several times :)
Now, the obvious answer would be to create a utility function similar to
union, exists, etc. that does the above snippet for a given passed in clause.
I'd do it myself, except I have no idea what it should be called :)
Any suggestions for a name?
-G
On Tuesday, May 16, 2006, 2:22:48 AM, you wrote:
> OK, this patch against trunk r#1455 works with your testcase.
> It defines a new CalculatedClause() class similar to that of Function(), and
> refactors Function() to derive from this new class.
> Also adds new visit_calculatedclause() function to handle the compile-time
> part.
> It requires the use of label() on the CalculatedClause when it's used in a
> subselect. I was going to auto-generate labels when they're not supplied in
> the fashion that bind params do, but the patch was beginning to get rather
> invasive, which I don't want to do right before a major release and, it's
> kind of a corner case anyway -- just use label() for now.
> I've also attached a modifed version of the testcase, so you can see how the
> label() works. It also changes the table alias from 'inner' to 'q_inner'. I
> tested with databases other than Sqlite, and the 'inner' name causes
> problems.
> Copying Mike as well
> Rick
> On 5/14/06, Gambit <[EMAIL PROTECTED]> wrote:
>>
>> 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
