Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLAlchemy: exclude rows taken from a subquery on a query

Abstraction of my problem, I have 2 tables. A User table, and a Friendship table.

I'm trying to make a query to list all the users available to be added as friend to User 1, Alice, and also excluding herself, using SQLAlchemy.

Considering there could be a lot of friendships, to find Alice's friends:

friend_subquery = db.session.query(Friendship).filter_by(User_id=1).subquery()

Now I want all the users listed, except Alice, and her friends, Bob and Jack.

friends = (db.session.query(User).
            filter(User.ID != 1).
            outerjoin(friend_subquery,
                      User.ID != friend_subquery.c.Friend_id))

My expected result would have been to get User 4 and 5, but this query returns all except Alice herself. The condition of

User.ID != friend_subquery.c.Friend_id

seem NOT to be working as expected.

P.S. I've done my homework of searching, reading docs, but couldn't figure it out. Thanks for your time.

like image 457
Sam Hosseini Avatar asked Aug 25 '15 18:08

Sam Hosseini


1 Answers

I assumed that your models are defined as below:

class User(db.Model):
    __tablename__ = 'User'

    ID = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))

    friendships = db.relationship(
        'Friendship',
        foreign_keys='Friendship.User_id',
        backref='friender',
    )
    friendships_of = db.relationship(
        'Friendship',
        foreign_keys='Friendship.Friend_id',
        backref='friendee',
    )


class Friendship(db.Model):
    __tablename__ = 'Friendship'
    ID = db.Column(db.Integer, primary_key=True)
    User_id = db.Column(db.Integer, db.ForeignKey('User.ID'))
    Friend_id = db.Column(db.Integer, db.ForeignKey('User.ID'))

In which case two ways to perform this query is shown in the code below. The first query relies on the relationship User.friendships_of, while the second works with explicit joins:

    # Add users
    u1, u2, u3, u4, u5 = users = [
        User(name="Alice"),
        User(name="Bob"),
        User(name="Jack"),
        User(name="Pluto"),
        User(name="Mike"),
    ]
    db.session.add_all(users)

    # Add friendhips
    u1.friendships.append(Friendship(friendee=u2))
    u1.friendships.append(Friendship(friendee=u3))
    db.session.commit()

    # Find Alice
    u_alice = db.session.query(User).filter(User.name == 'Alice').one()

    # Query (version 1)
    q = (
        db.session.query(User)
        .filter(~User.friendships_of.any(Friendship.User_id == u_alice.ID))
        .filter(User.ID != u_alice.ID)
        .all()
    )
    for x in q:
        print(x)

    # Query (version 2)
    q = (
        db.session.query(User)
        .outerjoin(
            Friendship,
            db.and_(
                u_alice.ID == Friendship.User_id,
                User.ID == Friendship.Friend_id,
            )
        )
        .filter(Friendship.ID == None)
        .filter(User.ID != u_alice.ID)
        .all()
    )
    for x in q:
        print(x)
like image 114
van Avatar answered Sep 22 '22 06:09

van