Constructing a SqlAlchemy exists query

Question

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()

What's the wrong query that you wrote? Perhaps we could help you fix it. — univerio
– univerio, Commented Feb 6, 2017 at 8:30

metmirr · Accepted Answer · 2017-02-06 11:18:00Z

3

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)

answered Feb 6, 2017 at 11:18

metmirr

4,3222 gold badges23 silver badges36 bronze badges

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

1 Comment

Abs Over a year ago

Sweet, this was close enough to the answer that I found it. Thanks!

Collectives™ on Stack Overflow

Constructing a SqlAlchemy exists query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related