On May 2, 2007, at 8:32 PM, Michael Bayer wrote: > > oh duh, i forgot about the new thing Gaetan came up with, try this > too: > > mapper(Invoice, invoice_table, properties={ > 'customer':column_property(func.substr(invoice_table.c.invnum, 1, > 4).label('customer')) > }) > > > > >
That's so very, *very* close. It works perfectly for that particular column, but a slightly more complex function causes breakage. The only problem is that it generates SQL that PostgreSQL 8.2 isn't quite happy with. Given the following code: rdy2bill_table = Table('rdy2bill', metadata, Column('invid', Integer, primary_key=True), Column('invnum', String), Column('pprresp', String, key='responsible'), Column('xmlvars', String), ) mapper(ReadyToBill, rdy2bill_table, properties={ 'customer' : column_property(func.substr (rdy2bill_table.c.invnum, 1, 4).label('customer')), 'groupcode': column_property(func.substring (rdy2bill_table.c.xmlvars, "<M.XRSGRPCD>(.*)</M.XRSGRPCD>").label ('groupcode')), }) inv = session.query(ReadyToBill).get_by(invid=1000346504, groupcode='BILLGSCB') I get a query like: 'SELECT substr(rdy2bill.invnum, %(substr)s, %(substr_1)s) AS customer, rdy2bill.xmlvars AS rdy2bill_xmlvars, rdy2bill.pprresp AS rdy2bill_pprresp, rdy2bill.invnum AS rdy2bill_invnum, rdy2bill.invid AS rdy2bill_invid, substring(rdy2bill.xmlvars, %(substring)s) AS groupcode \nFROM rdy2bill \nWHERE (rdy2bill.invid = %(rdy2bill_invid) s) AND (substring(rdy2bill.xmlvars, %(substring)s) AS groupcode = % (literal)s) ORDER BY rdy2bill.invid \n LIMIT 1' {'substring': '<M.XRSGRPCD>(.*)</M.XRSGRPCD>', 'substr': 1, 'literal': 'BILLGSCB', 'rdy2bill_invid': 1000346504, 'substr_1': 4} The killer part is the "(substring(rdy2bill.xmlvars, %(substring)s) AS groupcode = " in the WHERE clause. PostgreSQL apparently doesn't want that predicate to be named. Can that be disabled? -- Kirk Strauser --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---