Sqlalchemy select all where join not exists mysql using orm

We have two tables: Users and Permissions

We want to select all the users who do NOT have a "guest" permission. Now, it is possible for users to have multiple permissions (not just 1) so simply querying for !"guest" won't work. Here is what our query looks like now:

query = session.query(Users).join(Permission, and_(
        Permission.userId == theUser.uid, Permission.deviceId== theDevice.uid))
query.join(Permission).filter(~exists().where(and_(Permission.level==SqlConstants.PermissionLevels.GUEST, Users.uid == Permission.userId)))

I'm not sure if the join in the first line is relevant to the problem we are having, but we are using it, so I'm including it here. (I'll edit it out if it isn't relevant.)

The above returns the following exception:

returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually.

I gleaned this pattern from the following SO post: Using NOT EXISTS clause in sqlalchemy ORM query

as well as from the sqlalchemy documentation (which is shallow concerning not exists): http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html

It isn't clear to me what I'm doing wrong or whether there is a better way.

Solution

I'm not completely sure that I've understood your problem, mostly because the solution I came up with is quite simple. I'll give it a try, and anyway I hope it helps you in some way.

I was able to reproduce easily the exception you get when using exists. I think it happens because in the where parameters you are mixing columns from the two tables in the join. It would not give the exception if you rewrite it more or less like this,

sq = session.query(Users.pk).join(Permission).filter(Permission.level==SqlConstants.PermissionLevels.GUEST)
q = session.query(Users).join(Permission).filter(~sq.exists())

However it does not work, since as soon as there is 1 register in Permission with GUEST level, the query will give no result at all.

But why not rewriting it like this?

sq = session.query(Users.pk).join(Permission).filter(Permission.level==SqlConstants.PermissionLevels.GUEST)
q = session.query(Users).filter(~Users.pk.in_(sq))

In my trials, if I understood properly your problem, it works.

FYI, this is the toy example I used, where table A corresponds to Users, B to Permission, and B.attr would store the permission level.

In [2]:

class A(Base):
    __tablename__ = 'A'

    pk = Column('pk', Integer, primary_key=True)
    name = Column('name', String)


class B(Base):
    __tablename__ = 'B'

    pk = Column('pk', Integer, primary_key=True)
    fk = Column('fk', Integer, ForeignKey('A.pk'))
    attr = Column('attr', Integer)

    a = relationship("A", backref='B')

This is the data I have inserted,

In [4]:

q = session.query(B)
print(q)
for x in q.all():
    print(x.pk, x.fk, x.attr)

q = session.query(A)
print(q)
for x in q.all():
    print(x.pk, x.name)

SELECT "B".pk AS "B_pk", "B".fk AS "B_fk", "B".attr AS "B_attr" 
FROM "B"
1 1 1
2 1 2
3 2 0
4 2 4
5 1 4
SELECT "A".pk AS "A_pk", "A".name AS "A_name" 
FROM "A"
1 one
2 two
3 three

And this the result of the query,

In [16]:

from sqlalchemy import exists, and_, tuple_
sq = session.query(A.pk).join(B).filter(B.attr==2)
print(sq)
q = session.query(A).filter(~A.pk.in_(sq))
print(q)

for x in q.all():
    print(x.pk, x.name)
SELECT "A".pk AS "A_pk" 
FROM "A" JOIN "B" ON "A".pk = "B".fk 
WHERE "B".attr = :attr_1
SELECT "A".pk AS "A_pk", "A".name AS "A_name" 
FROM "A" 
WHERE "A".pk NOT IN (SELECT "A".pk AS "A_pk" 
FROM "A" JOIN "B" ON "A".pk = "B".fk 
WHERE "B".attr = :attr_1)
2 two
3 three

Hope it helps!