You'll need to include questions for which there are no responses, so a
left outer join with an appropriate filter should help. Something like this
may point you in the right direction:
user_id = 4
session.query(Question).\
outerjoin(Response).\
filter(or_(
Response.id == None,
~Question.responses.any(Response.user_id == user_id)))
On Friday, March 13, 2015 at 10:30:59 PM UTC-7, mal...@pteracuda.com wrote:
>
> Hello -- I'm having some trouble forming a query. I'm using sqlalchemy
> 0.9.9 with Postgres 9.3. I have 3 classes: Users, Responses, and Questions.
> Question has a one-to-many relationship to Responses. User has a
> one-to-many relationship to Responses. I'd like to find all questions that
> a user hasn't answered. So, basically, I'm trying to find all questions
> where none of the question's response's user ID == X. I spent the evening
> banging my head against a wall, so I figured it was time to ask the
> experts. Below I've included my model, greatly simplified, followed by some
> of my "closest" incorrect attempt. What is the magic query to find all of
> the questions that a user hasn't answered?
>
> Thanks so much for your time,
> Mallory
>
> class User(db.Model):
> __tablename__ = 'users'
> id = db.Column(db.Integer, primary_key=True)
> responses = db.relationship('Response', backref='user', lazy='dynamic')
>
> class Response(db.Model):
> __tablename__ = 'responses'
> id = db.Column(db.Integer, primary_key=True, index=True)
> question_id = db.Column(db.Integer, db.ForeignKey('questions.id'))
> user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
>
> class Question(db.Model):
> __tablename__ = 'questions'
> id = db.Column(db.Integer, primary_key=True, index=True)
> responses = db.relationship('Response', backref='question',
> lazy='dynamic', cascade='all,delete')
>
>
> My most recent failed attempt:
> Question.query.join(Response).join(User).filter(sqlalchemy.not_(Question.responses.any(User.id
>
> == 4))).all()
>
> Thanks again!
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
