lundi 29 juin 2015

Translate plain sql to sql alchemy

I have this generic query, that i want to reproduce in sql alchemy

select id, last_year, ((atr_a * 0.3 + atr_b * 0.3 + atr_c * 0.3) / 0.9) as rating 
from analytics_product 
ORDER BY rating DESC, year DESC

I have a relation of each product can have an entry in the rating table.

class Review_product(db.Model):
    __tablename__ = 'review_product'
    id = db.Column(db.Integer, primary_key=True)
    last_year = db.Column(db.SmallInteger) 
    analytics = db.relationship('Review_product', backref='review_product',  lazy='joined', uselist=False)

class Analytic_product(db.Model):
    __tablename__ = 'analytics_product'
    id = db.Column(db.Integer, primary_key=True)
    review_id = db.Column(db.Integer, db.ForeignKey('review_product.id'), nullable=False)
    atr_a = db.Column(db.Integer)
    atr_b = db.Column(db.Integer)
    atr_c = db.Column(db.Integer)

How can i generate the same output as the query above using these models? Please not that i don't have the last_year in the analytics table.

Aucun commentaire:

Enregistrer un commentaire