Having trouble with a PostgreSQL query

Question

I've got the following two tables:

User

userid | email | phone  
1 | [email protected] | 555-555-5555  
2 | [email protected] | 555-444-3333  
3 | [email protected] | 333-444-1111
4 | [email protected] | 123-333-2123

UserTag

id | user_id | tag  
1 | 1 | tag1  
2 | 1 | tag2  
3 | 1 | cool_tag  
4 | 1 | some_tag  
5 | 2 | new_tag  
6 | 2 | foo  
6 | 4 | tag1

I want to run a query in SQLAlchemy to join those two tables and return all users who do NOT have the tags "tag1" or "tag2". In this case, the query should return users with userid 2, and 3. Any help would be greatly appreciated.

I need the opposite of this query:

users.join(UserTag, User.userid == UserTag.user_id)
    .filter(
        or_(
            UserTag.tag.like('tag1'),
            UserTag.tag.like('tag2')
        ))
    )

I have been going at this for hours but always end up with the wrong users or sometimes all of them. An SQL query which achieves this would also be helpful. I'll try to convert that to SQLAlchemy.

Bell · Accepted Answer · 2013-11-26 20:16:31Z

1

Not sure how this would look in SQLAlchemy, but hopefully and explanation of why the query is the way it is will help you get there.

This is an outer join - you want all the records from one table (User) even if there are no records in the other table (UserTag) if we put User first it would be a left join. Beyond that you want all the records that don't have a match in the UserTag for a specific filter.

SELECT user.user_id, email, phone
  FROM user LEFT JOIN usertag
  ON usertag.user_id = user.user_id
    AND usertag.tag IN ('tag1', 'tag2')
  WHERE usertag.user_id IS NULL;

answered Nov 26, 2013 at 20:16

Bell

19k4 gold badges26 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ms03 · Accepted Answer · 2013-11-26 16:19:03Z

0

SQL will go like this

select u.* from user u join usertag ut on u.id = ut.user_id and ut.tag not in ('tag1', 'tag2');

I have not used SQLAlchemy so you need to convert it to equivalent SQLAlchemy query.

Hope it helps.

Thanks.

answered Nov 26, 2013 at 16:19

ms03

1014 bronze badges

Comments

van · Accepted Answer · 2013-11-27 08:12:38Z

0

Assuming your model defines a relationship as below:

class User(Base):
    ...

class UserTag(Base):
    ...
    user = relationship("User", backref="tags")

the query follows:

qry = session.query(User).filter(~User.tags.any(UserTag.tag.in_(tags))).order_by(User.id)

answered Nov 27, 2013 at 8:12

van

77.6k13 gold badges174 silver badges179 bronze badges

Collectives™ on Stack Overflow

Having trouble with a PostgreSQL query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related