On Aug 13, 2012, at 1:44 PM, Jonathan Vanasco wrote: > just wondering why i have to do : > sqlalchemy.sql.func.lower( class.column ) == string.lower() > > instead of : > class.column.lower() = string.lower() > > btw - i know in the archives people have mentioned doing an ilike > search, but a "WHERE lower(column) = 'string'" search will search > against a functioned index on postgres and I believe oracle. > considerably faster on large data sets.
There's hundreds of SQL functions that accept a single expression as an argument. SQLAlchemy's "func" system is designed so that in the vast majority of cases, these functions aren't explicit in SQLAlchemy itself, func.<NAME> just makes a new Function object with the name "<NAME>". So promoting some subset of those functions to be directly present would require a clear rationale as to why those SQL functions need to be present on the Column. Since they certainly can't all be. Also adding two ways to do the same thing needs a really good reason. The particular lower() comparison you have there isn't necessarily very generic. Depending on the collation behavior of the database, it may not be necessary, or may not be enough to produce a faithful comparison if multibyte characters are in play. For example, comparing unicode strings on MySQL (http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html) might look like: _latin1 'Müller' COLLATE latin1_german2_ci = k that's a lot more involved than a simple lower() call. The real use case you're looking for here is "case insensitive comparison", perhaps an operator which just does "case insensitive compare" at once would be easier to use, and also easier to augment with custom behavior. So there's a lot of questions to be asked about this use case, all of which is in light of that it is 100 times harder to remove a poorly considered feature than to add it. As a total coincidence, I'm working on an extensible operator system for core right now. A user-defined "case insensitive compare" operation would be easy to define in this new system (and actually you could define one using comparator_factory at the ORM level right now anyway). So a comparison such as the above, which might have edge cases that can't be met generically, might be better approached as a user-applied recipe. That way you get the ease of use and full capability, without adding incomplete features to SQLA directly. -- 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.
