CODEWITHSUNDEEP
sqldatabaseflasksqlalchemy
JamesB
JamesB

Reputation: 61

Query on many-to-many relationship in sqlalchemy

I have two db classes linked by a relationship table, and I can retrieve the questions assigned to each paper. However I want to retrieve all questions not currently assigned to the current paper, regardless of any other papers they are assigned to. I've tried a lot of different things, nothing quite did what I wanted.

I believe some kind of outer left join should do it, but I cannot figure out the correct syntax.

Thanks in advance for your suggestions.

Here is the DB structure so far:

class Question(db.Model):
    __tablename__ = 'question'
    id = db.Column(db.Integer, primary_key=True)
    papers = db.relationship(
            'Paper', secondary='question_in', backref='has_question', lazy='dynamic')

class Paper(db.Model):
    __tablename__ = 'paper'
    id = db.Column(db.Integer, primary_key=True)
    #returns the questions in the paper
    def all_questions(self):
        questions = Question.query.filter(Question.papers.any(id=self.id)).all()
        return questions

Question_in = db.Table('question_in',
    db.Column('question_id', db.Integer, db.ForeignKey('question.id'), primary_key=True),
    db.Column('paper_id', db.Integer,db.ForeignKey('paper.id'), primary_key=True), 
)

Upvotes: 2

Views: 54

Answers (1)

rrcal
rrcal

Reputation: 3752

You should be able to follow the same logic you used in your all_questions function, and filter it out using a subquery():

def not_assigned(self):
    assigned_questions = db.session.query(Question.id)\
                        .filter(Question.papers.any(id=self.id)).subquery()
    not_assigned = db.session.query(Question)\
                        .filter(Question.id.notin_(assigned_questions)).all()
    return not_assigned

Upvotes: 1

Related Questions