How to select questions which have no answers, in sqlalchemy

Question

I've two classes: Question and Answer. A question may have 0 or many answers.

class Question(Base):
    __tablename__ = "questions"
    answers = relationship('Answer', backref='question', 
                           primaryjoin="Question.id==Answer.question_id")

class Answer(Base):
    __tablename__ = "answers"

Now I want to find all the questions have no answers, how to do it?

I tried:

Session.query(Question).filter('count(Question.answers)==0').all()

It is incorrect. What is the right one?

Answer 1

I worked it out, use not exist:

Session.query(Question).filter(not_(exists().where(Answer.question_id==Question.id))).all()

Answer 2

Just use

session.query(Question).filter(Question.answers == None).all()

which basically is a NULL check (common filter operators).

Here's a gist example: http://gist.github.com/560473

The query generates the following SQL:

SELECT questions.id AS questions_id 
FROM questions 
WHERE NOT (EXISTS (SELECT 1 
FROM answers 
WHERE questions.id = answers.question_id))