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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
