On 6/18/14, 4:50 PM, Mike Solomon wrote: > > > 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 not looking deeply but the hybrid you have in prop_3 doesn't seem to have any relationship to the base set of rows you're getting from "fractions". it returns multiple rows because statement2 isn't using any aggregates.
How about a straight SQL string? what SQL do you expect? these are very easy to link to a hybrid. > > -- > 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 > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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.