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.

Reply via email to