Filtering backref relationships by secondary table field

Question

I have a rather complicated set of sqlalchemy models (all are declared using DeclarativeBase, via flask-sqlalchemy db.Model class). The business logic is as follows:

• Each task has one or more questions.
• Each question has one or more options.
• The user can make a presentation containing answers to one or more questions (from one or more tasks)

I prepared an example application to demonstrate the basic functions:

#!/usr/bin/env python from __future__ import print_function from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy import sqlalchemy_defaults from flask.ext.script import Manager, Server app = Flask(__name__) app.config.update( DEBUG=True, SQLALCHEMY_DATABASE_URI=( 'postgresql://sqlareltest:sqlareltest' + '@localhost:5432/sqlareltest'), ) db = SQLAlchemy(app) db.Column = sqlalchemy_defaults.Column sqlalchemy_defaults.make_lazy_configured(db.mapper) class Task(db.Model): id = db.Column(db.Integer(), primary_key=True) title = db.Column(db.String(255), default='') def __repr__(self): return self.title class Submission(db.Model): id = db.Column(db.Integer(), primary_key=True) title = db.Column(db.String(255), default='') def __repr__(self): return self.title class TaskQuestion(db.Model): id = db.Column(db.Integer(), primary_key=True) task_id = db.Column(db.Integer, db.ForeignKey('task.id')) title = db.Column(db.String(255), default='') weight = db.Column(db.Integer(), default=0) task = db.relationship(Task, backref=db.backref( "task_questions", order_by=weight, lazy='dynamic')) def __repr__(self): return self.title class QuestionOption(db.Model): id = db.Column(db.Integer(), primary_key=True) task_question_id = db.Column(db.Integer, db.ForeignKey('task_question.id')) title = db.Column(db.String(255), default='') weight = db.Column(db.Integer(), default=0) task_question = db.relationship(TaskQuestion, backref=db.backref( "question_options", order_by=weight, lazy='dynamic')) def __repr__(self): return self.title class TaskSubmission(db.Model): id = db.Column(db.Integer(), primary_key=True) task_id = db.Column(db.Integer, db.ForeignKey('task.id')) submission_id = db.Column(db.Integer, db.ForeignKey('submission.id')) task = db.relationship(Task, backref=db.backref( "task_submissions", lazy='dynamic')) submission = db.relationship(Submission, backref=db.backref( "task_submissions", lazy='dynamic')) def __repr__(self): return '%s: %s' % (self.task, self.submission) class TaskQuestionSubmission(db.Model): id = db.Column(db.Integer(), primary_key=True) task_submission_id = db.Column(db.Integer, db.ForeignKey('task_submission.id')) task_question_id = db.Column(db.Integer, db.ForeignKey('task_question.id')) question_option_id = db.Column(db.Integer, db.ForeignKey('question_option.id')) task_submission = db.relationship( TaskSubmission, secondary=TaskQuestion.__table__, secondaryjoin=( "TaskQuestionSubmission.task_question_id==TaskQuestion.id"), primaryjoin=( "TaskQuestionSubmission.task_submission_id==" + "TaskSubmission.id"), backref=db.backref( "task_question_submissions", order_by=TaskQuestion.weight, lazy='dynamic')) task_question = db.relationship(TaskQuestion, backref=db.backref( "task_question_submissions", lazy='dynamic')) question_option = db.relationship( QuestionOption, backref=db.backref( "task_question_submissions", lazy='dynamic')) def __repr__(self): return '%s: %s: %s' % (self.task_submission, self.task_question, self.question_option) manager = Manager(app) @manager.command def createdb(): db.create_all() @manager.command def createtask(): t = Task(title='Tell us about yourself') db.session.add(t) tq = TaskQuestion(title='What is your favourite colour?', weight=1) t.task_questions.append(tq) db.session.add(tq) qo = QuestionOption(title='Blue', weight=1) tq.question_options.append(qo) db.session.add(qo) qo = QuestionOption(title='Red', weight=0) tq.question_options.append(qo) db.session.add(qo) qo = QuestionOption(title='Green', weight=2) tq.question_options.append(qo) db.session.add(qo) tq = TaskQuestion(title='What is your favourite fruit?', weight=0) t.task_questions.append(tq) db.session.add(tq) qo = QuestionOption(title='Apple', weight=2) tq.question_options.append(qo) db.session.add(qo) qo = QuestionOption(title='Peach', weight=1) tq.question_options.append(qo) db.session.add(qo) qo = QuestionOption(title='Grape', weight=0) tq.question_options.append(qo) db.session.add(qo) db.session.commit() @manager.command def createsubmission(): t = Task.query.first() s = Submission(title='Info about Johnny') db.session.add(s) ts = TaskSubmission(task=t, submission=s) db.session.add(ts) tq = t.task_questions[1] qo = QuestionOption.query.filter_by(task_question=tq).first() tqs = TaskQuestionSubmission(task_submission=ts, task_question=tq, question_option=qo) db.session.add(tqs) tq = t.task_questions[0] qo = QuestionOption.query.filter_by(task_question=tq).first() tqs = TaskQuestionSubmission(task_submission=ts, task_question=tq, question_option=qo) db.session.add(tqs) db.session.commit() @manager.command def showoldsubmission(): ts = TaskSubmission.query.first() for tqs in ts.task_question_submissions: print(tqs) @manager.command def shownewsubmission(): t = Task.query.first() s = Submission(title='Info about Sarah') ts = TaskSubmission(task=t, submission=s) tq = t.task_questions[1] qo = QuestionOption.query.filter_by(task_question=tq).all()[1] tqs = TaskQuestionSubmission(task_submission=ts, task_question=tq, question_option=qo) tq = t.task_questions[0] qo = QuestionOption.query.filter_by(task_question=tq).all()[1] tqs = TaskQuestionSubmission(task_submission=ts, task_question=tq, question_option=qo) for tqs in ts.task_question_submissions: print(tqs) if __name__ == "__main__": manager.run() 

I want my TaskSubmission.task_question_submissions ordered by TaskQuestion.weight , but this does not work, so I will comment the corresponding lines in TaskQuestionSubmission.task_submission :

  task_submission = db.relationship( TaskSubmission, # secondary=TaskQuestion.__table__, # secondaryjoin=( # "TaskQuestionSubmission.task_question_id==TaskQuestion.id"), # primaryjoin=( # "TaskQuestionSubmission.task_submission_id==" + # "TaskSubmission.id"), backref=db.backref( "task_question_submissions", # order_by=TaskQuestion.weight, lazy='dynamic')) 

I get this example application with these commands:

 ./app.py createdb ./app.py createtask ./app.py createsubmission 

Then I run debugging commands to see how the materials come out.

 ./app.py showoldsubmission 

Outputs:

 Tell us about yourself: Info about Johnny: What is your favourite colour?: Blue Tell us about yourself: Info about Johnny: What is your favourite fruit?: Apple 

and

 ./app.py shownewsubmission 

Outputs:

 Tell us about yourself: Info about Sarah: What is your favourite colour?: Red Tell us about yourself: Info about Sarah: What is your favourite fruit?: Peach 

They are wrong because the question "What is your favorite fruit?" has a lower weight than "What is your favorite color?". He shows "What is your favorite color?" firstly because I first saved TaskQuestion to the database.

If I comment on the lines secondary , secondaryjoin , primaryjoin and order_by , then run the debug commands again, then showoldsubmission works fine:

 ./app.py showoldsubmission 

Outputs:

 [Tell us about yourself: Info about Johnny]: What is your favourite fruit?: Apple [Tell us about yourself: Info about Johnny]: What is your favourite colour?: Blue 

But:

 ./app.py shownewsubmission 

Outputs:

 Traceback (most recent call last): File "./app.py", line 231, in <module> manager.run() File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/flask_script/__init__.py", line 412, in run result = self.handle(sys.argv[0], sys.argv[1:]) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/flask_script/__init__.py", line 383, in handle res = handle(*args, **config) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/flask_script/commands.py", line 216, in __call__ return self.run(*args, **kwargs) File "./app.py", line 217, in shownewsubmission question_option=qo) File "<string>", line 4, in __init__ File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 260, in _initialize_instance return manager.original_init(*mixed[1:], **kwargs) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py", line 526, in _declarative_constructor setattr(self, k, kwargs[k]) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 226, in __set__ instance_dict(instance), value, None) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 1009, in set lambda adapter, i: adapter.adapt_like_to_iterable(i)) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 1025, in _set_iterable new_values = list(adapter(new_collection, iterable)) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 1009, in <lambda> lambda adapter, i: adapter.adapt_like_to_iterable(i)) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/collections.py", line 646, in adapt_like_to_iterable given, wanted)) TypeError: <flask_script.commands.Command object at 0x7f4c52967190>: Incompatible collection type: TaskSubmission is not list-like 

I tried to pull my hair out, trying to solve this problem - I looked at tons of sqlalchemy documents, forum posts, etc., but I can’t understand.

I am not an expert in sqlalchemy, the material is 'relationship' / 'backref' / 'primaryjoin' / 'secondaryjoin' a little over my head. Anyone who can point out what I'm doing wrong?

Update: I also noticed that after uncommenting the problematic lines, TaskQuestionSubmission.task_submission displays a list instead of a single element. So I tried changing the lines that define the new TaskQuestionSubmission (in the shownewsubmission function) as follows:

  tqs = TaskQuestionSubmission(task_submission=[ts], task_question=tq, question_option=qo) 

(Note the change: previously task_submission=ts ).

But after doing this, run:

 ./app.py shownewsubmission 

Gives another error:

 Traceback (most recent call last): File "./app.py", line 235, in <module> manager.run() File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/flask_script/__init__.py", line 412, in run result = self.handle(sys.argv[0], sys.argv[1:]) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/flask_script/__init__.py", line 383, in handle res = handle(*args, **config) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/flask_script/commands.py", line 216, in __call__ return self.run(*args, **kwargs) File "./app.py", line 223, in shownewsubmission tq = t.task_questions[0] File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/dynamic.py", line 254, in __getitem__ sess = self.session File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/dynamic.py", line 237, in session sess.flush() File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1919, in flush self._flush(objects) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2037, in _flush transaction.rollback(_capture_exception=True) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2001, in _flush flush_context.execute() File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 372, in execute rec.execute(self) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 481, in execute self.dependency_processor.process_saves(uow, states) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/dependency.py", line 1051, in process_saves False, uowcommit, "add"): File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/dependency.py", line 1156, in _synchronize self.prop.synchronize_pairs) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/sync.py", line 84, in populate_dict _raise_col_to_prop(False, source_mapper, l, None, r) File "/wsgi/sqla-rel-test/env/local/lib/python2.7/site-packages/sqlalchemy/orm/sync.py", line 123, in _raise_col_to_prop (source_column, source_mapper, dest_column)) sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for source column 'task_question.'; mapper 'Mapper|TaskSubmission|task_submission' does not map this column. Try using an explicit `foreign_keys` collection which does not include destination column 'task_question_submission.' (or use a viewonly=True relation). 

(Which I find even more perplexing). Thus, it is still not closer to solving this issue.