CODEWITHSUNDEEP
pythonsqlalchemyflaskflask-sqlalchemy
demux
demux

Reputation: 4654

sqlalchemy count related

I have been using django's ORM and sqlalchemy has me beat a.t.m.
I have this:

recipie_voters = db.Table('recipie_voters',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('recipie_id', db.Integer, db.ForeignKey('recipie.id'))
)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ...

class Recipie(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ...
    voters = db.relationship('User', secondary=recipie_voters, backref=db.backref('votes', lazy='dynamic'))
    ...

The question is: how can I select the count of voters (User) in Recipie?
We'll start from here:

Recipie.query.all()

Upvotes: 0

Views: 1119

Answers (1)

van
van

Reputation: 76962

I am not sure how to put this in the django context, but with declarative and usage of Hybrid Attributes, the code might look like below:

class Recipie(Base):
    __tablename__ = 'recipie'
    id = Column(Integer, primary_key=True)
    name = Column(String(255))

    voters = relationship("User", 
             secondary=recipie_voters,
             backref=backref("votes", lazy="dynamic",),
             )


    @hybrid_property
    def voters_count(self):
        return len(self.voters)

    @voters_count.expression
    def voters_count(cls):
        return (select([func.count(recipie_voters.c.user_id)]).
                where(recipie_voters.c.recipie_id == cls.id).
                label("voters_count")
                )


# get only those Recipies with over 100 votes    
qry = session.query(Recipie).filter(Recipie.voters_count >= 100)

Upvotes: 3

Related Questions