Inverting the like() operation
Matthew Pounsett
I'm having a tough time figuring out how to manage this, and I don't
think I know enough about what the end result might look like to be
able to google it successfully.
I'm trying to invert the sense of the like() operation in an unusual
way. Rather than doing 'not like()' I want to take the usual
Class.attribute.like(wildcard_expression) and do
wildcard_expression.like(Class.attribute). The implication being that
I'm storing wildcard expressions in the database, and passing absolute
strings to see which wildcards match.
The final SQL would look something like this:
dbtest=# create table foo ( bar varchar primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
dbtest=# insert into foo values ( 'foo%' );
INSERT 0 1
dbtest=# select * from foo where 'foobar' like foo.bar;
bar
------
foo%
(1 row)
Anyone have any suggestions for how to express that select from
SQLAlchemy? I'm using SQLAlchemy version 0.7.3 with postgreSQL 8.4.8
under python 2.7.2.
Thanks for any suggestions..
Michael Bayer
both the left and right sides of an expression are ultimately represented as SQLAlchemy ColumnElements. When you say "somecol == 'somestr'", the 'somestr' part is coerced into a "literal" object as a result of it being pulled into the "binary" expression (that is, an expression with a left, right and an operator in the middle). You can do this explicitly so that you can call operators from either side:
literal("foobar").like(foo.bar)
Matthew Pounsett
both the left and right sides of an expression are ultimately represented as SQLAlchemy ColumnElements. When you say "somecol == 'somestr'", the 'somestr' part is coerced into a "literal" object as a result of it being pulled into the "binary" expression (that is, an expression with a left, right and an operator in the middle). You can do this explicitly so that you can call operators from either side:
literal("foobar").like(foo.bar)