Get posts only from those who follow you back in flask app with sqlalchemy

0
votes

I have a flask app that uses a blend of sqlalchemy (ORM) and flask sqlalchemy.

I have a User model, a Post model, and a followers table as follows:

from app import db
from flask_login import UserMixin
from sqlalchemy.orm import relationship

class User(UserMixin, db.Model)
    id = db.Column(db.Integer, primary_key=True)
    followed = db.relationship('User', secondary=followers,
                               primaryjoin=(followers.c.follower_id == id),
                               secondaryjoin=(followers.c.followed_id == id),
                               backref=db.backref('followers', lazy='dynamic'), lazy='dynamic') # many to many relationship with other Users
    posts = relationship('Post') # one to many relationship with posts
    # ...

class Post(db.Model)
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    #...

followers= db.Table('followers',
                       db.Column('follower_id', db.Integer, db.ForeignKey('user.id')),
                       db.Column('followed_id', db.Integer, db.ForeignKey('user.id'))
                       )

My question is how to query the Post table/model so that it returns only posts written by users that follow you back (i.e. friends).
The flask mega tutorial by Miguel Grinberg (which these models are based off of) uses the following sqlalchemy query to accomplish something similar to what I'm after.

def followed_posts(self):
        return Post.query.join(
            followers, (followers.c.followed_id == Post.user_id)).filter(
                followers.c.follower_id == self.id).order_by(
                    Post.timestamp.desc())

I've been trying to modify the above query so that the Post table joins only with the subset of followers, characterized by if (a,b) in followers, then (b,a) in followers. I am just not sure how to implement this.

Any help would be appreciated. Also if anyone has a recommendation for a guide that goes over more 'advanced' sqlalchemy queries like the one above without a ton of pure SQL prerequisite knowledge please let me know. Thanks in advance!

2
flasksqlalchemyflask-sqlalchemy
I guess you just switch the followed_id and follower_id in the def followed_posts(self): ... query from Miguels's case: he lists the posts of those you follows, so the inverse will be the posts of those who follow you. – van
thanks for the suggestion @van. that's closer to what i'm after than the original query. but in my case I still need to check that both users follow each other. – mark getrost

2 Answers

0
votes

In order to execute and get what you want in one query, the following two options should produce the same result, but are quite different in being pythonic or SQL-ic.

Option-1: I find this to be quite pythonic as the Poster.followed.any(...) reads as "where one of the followers is me.". But the SQL it produces is a bit "ugly", although it gets the job done:

    def followed_posts__elegant_any(self):
        user_id = self.id
        Poster = aliased(User, name="poster")
        return (
            object_session(self)
            .query(Post)
            .join(Poster, Post.user)
            .filter(Poster.followed.any(User.id == user_id))
            .filter(Poster.followers.any(User.id == user_id))
            .order_by(Post.timestamp.desc())
        )

Option-2:

This is much more straightforward and is expressed in very "pure" SQL logic:

  • create aliases so that can join on followers table twice
  • perform join twice to ensure that the relationship between current user and "Poster" are both ways. The join itself is not really used for anything else, but filtering:
def followed_posts__optimal_join(self):
    user_id = self.id
    f1 = aliased(followers)
    f2 = aliased(followers)
    return (
        object_session(self)
        .query(Post)
        .join(User, Post.user)
        .join(f1, and_(User.id == f1.c.follower_id, f1.c.followed_id == user_id))
        .join(f2, and_(User.id == f2.c.followed_id, f2.c.follower_id == user_id))
        .order_by(Post.timestamp.desc())
    )
0
votes

I figured it out.

def get_friend_posts():
    return Post.query.filter(Post.user_id.in_([f.id for f in self.followed if f.is_following(self)]))

# helper function
def is_following(self, user):
        return self.followed.filter(followers.c.followed_id == user.id).count() > 0