1
\$\begingroup\$

I am building Twitter. People can follow each other, and hence I have 2 tables, users and followers.

My current problem is that I feel like I'm doing too many SELECTs before doing anything useful. Is it common to have a SELECT on users to find the current user each time?

I have:

models.py

class User(Base):
    """Table schema containing profiles."""

    __tablename__ = "users"

    id = Column(String, primary_key=True)
    username = Column(String)
    created_date = Column(DateTime)

    following = relationship('User',
        secondary='followers',
        primaryjoin='User.id==Follower.user_id',
        secondaryjoin='Follower.follows_user_id==User.id'
    )

    followers = relationship(
        'User', secondary='followers',
        primaryjoin='User.id==Follower.follows_user_id',
        secondaryjoin='User.id==Follower.user_id'
    )

class Follower(Base):
    __tablename__ = 'followers'

    user_id = Column(String, ForeignKey('users.id'), primary_key=True)
    follows_user_id = Column(String, ForeignKey('users.id'), primary_key=True)
    created_date = Column(DateTime)
    updated_date = Column(DateTime)
    active = Column(Boolean, default=True)

    user_creating_following = relationship("User", primaryjoin="Follower.user_id==User.id")
    user_being_followed = relationship("User", primaryjoin="Follower.follows_user_id==User.id")

To follow a user, I created an endpoint, which ends up calling follow_user() function.

def follow_user(session, user_id, username_to_follow):
    # first check if user already follows the person trying to follow
    user_to_follow = session.query(User).filter(User.username==username_to_follow).first()

    if not user_to_follow:
        raise BadRequest("The user you are trying to follow doesnt exist")

    # Check not performing action on yourself 
    if user_to_follow.id == user_id:
        # Can not perform this action on yourself
        raise BadRequest("You cannot follow yourself")

    follow = session.query(Follower).filter(
        and_(
            Follower.user_id==user_id,
            Follower.follows_user_id==user_to_follow.id
        )
    ).first()

    if follow:
        # Update the existing follow record
        follow.active = True
        follow.updated_date = datetime.now()
    else: # if the follow doesn't already exist, create it
        follow = Follower(
            user_id=user_id,
            follows_user_id=user_to_follow.id,
            created_date=datetime.now(),
            updated_date=datetime.now(),
            active=True
        )
    session.add(follow)
    session.commit()
    return 

I wish I could write something like:

follow = session.query(Follower)\
.filter(Follower.user_creating_following.id==user_id)\
.filter(Follower.user_receiving_following.username==username_to_follow)

This would allow me to avoid the first query on the User altogether, but returns the error:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with
        Follower.user_creating_following has an attribute 'id' 

Any other input is greatly appreciated.

\$\endgroup\$
  • 1
    \$\begingroup\$ I am building Twitter - I have bad news - someone may have beaten you to it. \$\endgroup\$ – Reinderien Apr 12 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.