I've started using add_column() as needed to work around this. Our application tends to have a single, fairly generic filter_sort function for each class. Some use cases of this function require add_column() and some don't. From the caller's perspective, the filter_sort() function is poorly encapsulated because sometimes it sometimes returns objects and sometimes tuples.
It'd be very helpful if the add_column() function took an optional parameter 'omit_from_results' telling whether the column should be included in the results, then our filter_sort() functions could always return objects. Barry ----- Original Message ---- From: Barry Hart <[EMAIL PROTECTED]> To: sqlalchemy@googlegroups.com Sent: Thursday, September 6, 2007 1:55:14 PM Subject: Re: [sqlalchemy] Re: Error combining DISTINCT and ORDER BY in an ORM query on PostgreSQL add_column() worked great, thanks! Barry ----- Original Message ---- From: Michael Bayer <[EMAIL PROTECTED]> To: sqlalchemy@googlegroups.com Sent: Thursday, September 6, 2007 10:18:58 AM Subject: [sqlalchemy] Re: Error combining DISTINCT and ORDER BY in an ORM query on PostgreSQL the two immediate ways to do this with ORM are to use add_column() on Query, or to query from a fully constructed select() statement. The former will have the effect of returning tuples containing the instance as well as the additional columns, the latter doesnt play as nicely with eager loading. add_column() is probably the better option here. On Sep 6, 2007, at 9:09 AM, Barry Hart wrote: My application has the following data model: A promotion has 1 mechanic. A promotion has 0 or more products. I want to query for a list of promotions containing products whose name matches a pattern, and I want the resulting list to be sorted by a property on the promotion's mechanic. This query does not work with SQLAlchemy 0.3.10 on PostgreSQL 8.1. It's because of the combination of using DISTINCT and ORDER BY on an table external to the main table for the object. (The use of DISTINCT is necessary because joining with the products table creates duplicate records. I'm showing the query results in a web application with 'n' records per page. Without DISTINCT, the ORM mapper will ignore the duplicates, but the record count for the query no longer reflects the actual number of objects to be returned, making it impractical to page through the data.) The error is the following: sqlalchemy.exceptions.SQLError: (ProgrammingError) for SELECT DISTINCT, ORDER BY expressions must appear in select list 'SELECT DISTINCT promo_promotion.mechanic_id AS promo_promotion_mechanic_id, pr omo_promotion.id AS promo_promotion_id, promo_promotion.name AS promo_promotion_ name \nFROM promo_promotion JOIN prod_product ON promo_promotion.id = prod_produ ct.promotion_id JOIN promo_mechanic ON promo_mechanic.id = promo_promotion.mecha nic_id \nWHERE lower(prod_product.name) LIKE %(lower)s ORDER BY promo_mechanic.n ame ASC' {'lower': '%1%'} The same program works fine on SQLite. I could write a similar query at the relational level that explicitly includes the sort field, but I'd prefer to work at the ORM level. Is there a way to do this? My test case is below. Barry H. -- from sqlalchemy import * from sqlalchemy.ext.assignmapper import assign_mapper import sqlalchemy from sqlalchemy.ext import activemapper, sessioncontext engine = None def create_engine(): global engine #engine = sqlalchemy.create_engine('sqlite://') engine = sqlalchemy.create_engine('postgres://postgres:[EMAIL PROTECTED]:5432/testdb') metadata.connect(engine) def create_session(): return sqlalchemy.create_session(bind_to=engine) def fuzzy_search(column, value): """Case insensitive search allowing partial string matches.""" return func.lower(column).like('%%%s%%' % value.lower()) metadata = activemapper.metadata create_engine() session = activemapper.Objectstore(create_session) activemapper.objectstore = session ########################################################################## # Classes ########################################################################## class Mechanic(object): pass class Promotion(object): def __repr__(self): return 'Promotion: %s, mechanic=%s' % (self.name, self.mechanic.name) class Product(object): pass ########################################################################## # Tables ########################################################################## mechanic_table = Table( 'promo_mechanic', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode, unique=True)) promotion_table = Table( 'promo_promotion', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode, unique=True), Column('mechanic_id', Integer, ForeignKey('promo_mechanic.id'))) product_table = Table( 'prod_product', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode), Column('promotion_id', Integer, ForeignKey('promo_promotion.id'))) ########################################################################## # Mappings ########################################################################## assign_mapper(session.context, Mechanic, mechanic_table) assign_mapper(session.context, Promotion, promotion_table, properties=dict( mechanic=relation(Mechanic), products=relation(Product))) assign_mapper(session.context, Product, product_table) ########################################################################## # Main program ########################################################################## import sys mechanic_table.create(checkfirst=True) promotion_table.create(checkfirst=True) product_table.create(checkfirst=True) #for t in metadata.tables.values(): # t.create() for i in range(1, 10): m = Mechanic(name='mech%d' % (10 - i)) if i <= 5: products = [Product(name='1a'), Product(name='1b')] else: products = [Product(name='2a'), Product(name='2b')] promo = Promotion(name='promo%d' % (i), mechanic=m, products=products) session.flush() q = Promotion.query().distinct() q = q.join('products') q = q.filter_by(fuzzy_search(Product.c.name, '1')) q = q.reset_joinpoint() q = q.join('mechanic') q = q.order_by(asc(mechanic_table.c.name)) for promo in q: print 'promo: %s, Mechanic: %s' % (promo.name, promo.mechanic.name) Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search. ____________________________________________________________________________________ Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545469 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---