python - How to filter a Model with a One-To-Many Relationship using Flask-Sqlalchemy?

Question

I'm new to Flask-SQL-Alchemy so this may be a noob quesiton. Let's say I have a Tweet model:

class Tweet(db.Model):
    __tablename__ = 'tweet'
    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String(500), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

And a User model, which can have many tweets:

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    birth_year = db.Column(db.Integer)
    tweets = db.relationship('Tweet', backref='user', lazy=True)

Now, I want to apply different filters on the User table. For example, to fetch all the users born in 1970 whose first name is John

filters = (
  User.first_name == 'John',
  User.birth_year == 1970
)
users = User.query.filter(*filters).all()

So far, so good! But now I want to add a filter for the tweets attribute which notice is not a real database column but rather something provided by the ORM. How can I fetch Johns who have posted more than 20 tweets? I tried:

filters = (
  User.first_name == 'John',
  len(User.tweets) > 20
)

But this raises an Exception: object of type 'InstrumentedAttribute' has no len(). I have also tried adding a new hybrid property in the User model:

class User(db.Model):
    ...

    @hybrid_property
    def tweet_count(self):
        return len(self.tweets)

filters = (
  User.first_name == 'John',
  User.tweet_count > 20
)

But I still get the same error. This seems like a common task but I'm not able to find any documentation or related examples. Am I approaching the problem the wrong way?

标签： pythonsqlalchemyormflask-sqlalchemyone-to-many

Answer 1

I think you are missing the second part as seen here SQLAlchemy - Writing a hybrid method for child count

from sqlalchemy.sql import select, func

class User(db.Model):

    #...

    @tweet_count.expression
    def tweet_count(cls):
        return (select([func.count(Tweet.id)]).
                where(Tweet.user_id == cls.id).
                label("tweet_count")
                )