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)
____________________________________________________________________________________
Moody friends. Drama queens. Your life? Nope! - their life, your story. Play
Sims Stories at Yahoo! Games.
http://sims.yahoo.com/
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
