On 6/19/14, 2:41 PM, Mike Solomon wrote:
>
>
> Le jeudi 19 juin 2014 16:10:19 UTC+3, Michael Bayer a écrit :
>
>
> On 6/19/14, 4:09 AM, Mike Solomon wrote:
> >
> >
> >
> > It's difficult to issue a straight SQL string for the hybrid
> property
> > itself because
>
>
> sorry, I meant, please write the query *that you really want* as a
> SQL
> string. Don't use SQLAlchemy. It's better to work in that
> direction.
>
> If you don't know what the SQL you want is, that's a different issue,
> I'd start on that part first.
>
>
> Ah, OK. The SQL below will group fractions based on tag and give the
> num(erator) and den(ominator) of the maximum for each group. In
> Python, I'd like to have a fraction class that has members tag and val
> where val is a hybrid_property combining num and den. I'd like to be
> able to do a query like session.query(Fraction.tag,
> func.max(Fraction.val)).group_by(Fraction.tag) and get the SQL below:
>
>
> SELECT DISTINCT fraction_a.tag, fraction_a.high,
> fraction_b.num, fraction_b.den
> FROM
> (SELECT fraction.tag, max(1.0 * fraction.num / fraction.den) AS high
> FROM fraction
> GROUP BY fraction.tag)
> AS fraction_a JOIN
> (SELECT fraction.tag, fraction.num, fraction.den
> FROM fraction)
> AS fraction_b
> ON fraction_a.tag = fraction_b.tag
> AND fraction_a.high = 1.0 * fraction_b.num / fraction_b.den;
So to the extent that "1.0 * num / den" is a column-based expression you
like to use in your query, it's a good candidate for a hybrid or
column_property (deferred one in case you don't want to load it
unconditionally). But as far as the FROM clauses, when we work with
Query(), the FROM clauses are always distinct entities that we have to
combine together as we want, there's never any kind of implicit behavior
with that.
Here's the appropriate place to use column_property() (specifically
deferred() so that it doesn't get loaded by default) in terms of how the
query should come out:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Fraction(Base):
__tablename__ = 'fraction'
id = Column('id', Integer, primary_key=True)
tag = Column(Integer)
num = Column(Integer)
den = Column(Integer)
high = deferred(1.0 * num / den)
fraction_a = select([
Fraction.tag,
func.max(Fraction.high).label("high"),
]).group_by(Fraction.tag).alias('fraction_a')
fraction_b = aliased(Fraction, name="fraction_b")
sess = Session()
q = sess.query(fraction_a, fraction_b.num, fraction_b.den).\
distinct().\
join(fraction_b, and_(
fraction_a.c.high == fraction_b.high,
fraction_a.c.tag == fraction_b.tag
)
)
print q
output:
SELECT DISTINCT fraction_a.tag AS fraction_a_tag, fraction_a.high AS
fraction_a_high, fraction_b.num AS fraction_b_num, fraction_b.den AS
fraction_b_den
FROM (SELECT fraction.tag AS tag, max((:param_1 * fraction.num) /
fraction.den) AS high
FROM fraction GROUP BY fraction.tag) AS fraction_a JOIN fraction AS
fraction_b ON fraction_a.high = (:param_2 * fraction_b.num) /
fraction_b.den AND fraction_a.tag = fraction_b.tag
> --
> 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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> 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 [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.
