Le mercredi 18 juin 2014 22:03:33 UTC+3, Michael Bayer a écrit : > > > > if you can show the SQL you expect that would help. it seems in your SO > question you want a subquery, you'd have to define that: > > class Holder(..): > some_prop = column_property(select([func.max(1 * col1 / col2)])) > > > I tried this strategy, and the problem is that when it is used in combination with other things it creates a cross product. In the below example, I'd like for all the queries to return 10 results but the last one returns 100 because it does not join the table generated in the max statement with the Fraction table. Ideally, I'd like the last query to only generate 10 rows where the last two columns in each row are all the maximum numerator and denominator.
The sql I'd expect would be like that in my SO question: rows selected from a table joined to itself. ECHO = False from sqlalchemy.orm import sessionmaker, aliased from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, select, func from sqlalchemy.orm import relationship from sqlalchemy.ext.hybrid import hybrid_property engine = create_engine('sqlite:///:memory:', echo=ECHO) Base = declarative_base() class Fraction(Base): __tablename__ = 'fractions' id = Column(Integer, primary_key = True) prop_1 = Column(Integer) prop_2 = Column(Integer) prop_3_num = Column(Integer) prop_3_den = Column(Integer) @hybrid_property def prop_3(self) : return 0 @prop_3.expression def prop_3(self) : alias_1 = aliased(Fraction) alias_2 = aliased(Fraction) statement1 = select([func.max(1.0 * alias_2.prop_3_num / alias_2.prop_3_den).label('fmax')]) statement2 = select([(1.0 * alias_2.prop_3_num / alias_2.prop_3_den).label('fdec'), alias_2.prop_3_num.label('max_num'), alias_2.prop_3_den.label('max_den')]) return select([statement2.c.max_num.label('prop_3_max_num'), statement2.c.max_den.label('prop_3_max_den')]).\ select_from(statement2).join(statement1, onclause=statement1.c.fmax == statement2.c.fdec) Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() for x in range(10) : session.add(Fraction(prop_1=x%2, prop_2=x%4, prop_3_num = x+1, prop_3_den=x+2)) session.commit() # should only print two rows...don't know why it prints 3 for x in session.query(Fraction.prop_3) : print x for x in session.query(Fraction.prop_1) : print x for x in session.query(Fraction.prop_1, Fraction.prop_2) : print x for x in session.query(Fraction.prop_1, Fraction.prop_2, Fraction.prop_3) : print x -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.