CODEWITHSUNDEEP
pythonsqlalchemy
Abs
Abs

Reputation: 1766

Constructing a SqlAlchemy exists query

I have two tables, prizes and prize_unlocks. I'm trying to write a query to find a prize that a user has not previously unlocked.

The idea is that there is a table of prizes that can be won by any user and there is a table tracking which users have won which prizes. I'm using flask-sqlalchemy and the tables are defined like this:

class Prize(db.Model):
  __tablename__ = 'prizes'
  id = db.Column(db.Integer, primary_key = True)
  # ... some other info about the prize...


class PrizeUnlock(db.Model):
  __tablename__ = 'prize_unlocks'
  id = db.Column(db.Integer, primary_key = True)

  # associated prize
  prize_id = db.Column(db.Integer, db.ForeignKey('prizes.id'))
  prize = db.relationship('Prize',
    backref=db.backref('prize_unlocks'))

  # associated user
  user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
  user = db.relationship('User',
    backref=db.backref('prize_unlocks'))

I am trying to write a single SqlAlchemy query to select a random prize from the prizes that a user has not previously won. As I understand, I need to write the query with an exists clause, but I'm unable to get it right.

Could anyone please help out with this?

If it's of any help, the corresponding sql query looks like this:

select p.id from prizes as p where not exists (select * from prize_unlocks where prize_unlocks.prize_id=r.id) order by rand() limit 1;

EDIT: Got the answer! metmirr was very close, but I'm just posting the final answer here in case it helps someone in the future.

db.session.query(Prize.id).filter(
  not_(
    db.session.query(PrizeUnlock)
      .filter(Prize.id == PrizeUnlock.prize_id)
      .exists()
  )
).order_by(Prize.id).limit(10).all()

Upvotes: 1

Views: 4732

Answers (1)

metmirr
metmirr

Reputation: 4302

Use subquery in filter function:

db.session.query(Prize.id).filter(
    db.session.query(PrizeUnlock.id)
        .filter(Prize.id == PrizeUnlock)
        .exists()
).order_by(Prize.id).limit(1)

Upvotes: 3

Related Questions