Many-to-Many association table with 3 foreign-key columns

Adderus Berg

unread,

Aug 16, 2022, 9:06:39 AM8/16/22

to sqlalchemy

Good afternoon community

I have the following tables:

Survey
Answer
Question
SurveyAnswer (Association table)

My association table has 3 foreign keys that relates to Answer, Question and Survey.
A Survey has many Questions, many Answers.

When I insert a survey object into the db, SqlAlchemy creates two seperate entries for both questions and answers relating to the Survey instead of adding them in the same data set.

I would appreciate any guidance regarding this issue. Thank you in advance.
Here is a screenshot of my db models:

Here is an example of what happens after inserting a Survey objet to the db:

As you can see it seems to me that it inserts the first dataset for answers then afterwards inserts the questions' dataset. I would like to have these integrated into the same rows.
I am not sure if I am doing something wrong.

Once again thank you for your help

Mike Bayer

unread,

Aug 16, 2022, 9:22:08 AM8/16/22

to noreply-spamdigest via sqlalchemy

There is no automatic or implied coordination against the survey_answer table as indicated in the "secondary" field. You need to not add entries to Survey.answers, Survey.questions as is, those would be viewonly=True as long as they refer to survey_answer as "secondary". Instead, create new SurveyAnswer instances. To facilitate this, add relationships to Survey, Answer, Question and SurveyAnswer that link SurveyAnswer directly to these classes.

this pattern is known as the "association object" pattern and is illustrated at https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object . It is used as a replacement for the "many to many" pattern when the "many to many" table has more columns than just a simple linkage between two rows.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4249ef32-27e9-405d-ab9c-472b28f00917n%40googlegroups.com.