Ordering your sqlalchemy many-to-many relationship
I am currently working on another customer project and came across the problem that I had a many to many relationship, that I wanted to order.
Now as that might sound a little abstract, let’s build a little example. Let us say I am building a tool for customer research and I want to build a number of surveys using a lot of questions multiple times, so what comes to your mind first? A many-to-many relationship1:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
survey_questions = db.Table(
'survey_questions',
db.Column('survey_id', db.Integer,
db.ForeignKey('surveys.id')),
db.Column('question_id', db.Integer, db.ForeignKey('questions.id'))
)
class Survey(db.Model):
__tablename__ = 'surveys'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), unique=True)
ending = db.Column(db.DateTime)
class Question(db.Model):
__tablename__ = 'questions'
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.Text)
help = db.Column(db.Text)
So now we can create surveys and add questions to them. As long as we only want to order the questions by i.e. alphabetically by their contents, it is quite obvious what to do:
1
Question.survey = db.relationship(Survey, secondary=survey_questions, order_by=Question.text)
As I said, simply. But what if you want to be able to order the questions differently on a per-survey basis? Possible, but a little more tricky. First we need an additional attribute on the secondary table:
1
2
3
4
5
6
7
survey_questions = db.Table(
'survey_questions',
db.Column('survey_id', db.Integer,
db.ForeignKey('surveys.id')),
db.Column('question_id', db.Integer, db.ForeignKey('questions.id')),
db.Column('weight'), db.Integer
)
Now we can use this additional attribute to order those (which again is made really simple by sqlalchemy):
1
Question.survey = db.relationship(Survey, secondary=survey_questions, order_by=survey_questions.c.weight)
But how do we update the weight of a question in a survey? Unfortunately I did not find a really simple solution for that. The only thing I do have, is this:
1
2
3
4
5
6
query = survey_questions.update().where(
survey_questions.c.survey_id == survey.id
).where(
survey_questions.c.question_id == question.id
).value(weight=new_weight)
db.session.execute(query)
Afterwards you’re good to go, your relationship will automatically be ordered by the given weights and you can update them using the query above.
-
Please note that I am assuming the use of flask-sqlalchemy here, so sqlalchemy declarative base. It should be fairly easy reconstructing this with pure sqlalchemy (flask-sqlalchemy is merely a wrapper)- ↩
