Creating join table for ManytoMany relationship

Using Django Using the ORM
1

Edit: Added possible solution and move M2M to question

I have a model where there is a many to many relationship between questions and recommendations. Each question can have many recommendations and each recommendation can apply to many questions.

Once a survey is completed the recommendations will be displayed for each question based on the risk_value.

I already have tables for question and recommendation, my question is how do I create the relationships?

Might this work:

answer_list = [1,4,9 ] #PK for answers related to question #This would be extracted from a list of lists that have the answer PKs for each question
for n = 1 to 80
question = Question.objects.get(pk = n) #Get question PK
	for item in answer_list #Iterate through list of answers related to question
	rec = Recommendation.get(pk= item)
	question.recommendation.add(rec) #Add recommendation to question

I would define a function in the app to do that, I’m guessing I would only need o do that once and the join table would remain part of the database

class Question(models.Model):
    question = models.CharField(max_length=200)
    risk_value = models.IntegerField(null = True, blank=False)
    q_num = models.IntegerField(null = True, blank=False)
    survey = models.ForeignKey(
        Surveys, on_delete=models.CASCADE, null=True
    )
    area = models.ForeignKey(
        Area, on_delete=models.CASCADE, null=True
    ) # Django will store this as area_id on database
    category = models.ForeignKey(
        Category, on_delete=models.CASCADE, null=True
    ) # Django will store this as category_id on database 
    analysis = models.TextField(max_length=1000, default = "This would be the analysis") #Results of analysis

     recommendation=recommendation.ManyToManyField(Question)

    class Meta:
            ordering = ['question']
    def __str__(self):
        return f"{self.question} Risk value is {self.risk_value}"
    
class Recommendation(models.Model):
    recommendation = models.TextField(max_length=1000, blank = True)
    

def __str__(self):
        return f"{self.recommendation}"
2

Are you asking how to create the actual supporting table in the database for ManyToMany relationships?

If so, it will automatically be created in the migration files and your database when running migrations.

If you are asking how to actually create relationships when using ManyToMany fields, there are several ways.

Given the models:

from django.db import models


class Publication(models.Model):
    title = models.CharField(max_length=30)

    class Meta:
        ordering = ["title"]

    def __str__(self):
        return self.title


class Article(models.Model):
    headline = models.CharField(max_length=100)
    publications = models.ManyToManyField(Publication)

    class Meta:
        ordering = ["headline"]

    def __str__(self):
        return self.headline
  1. Calling the add method.
p1 = Publication(title="The Python Journal")
p1.save()

a1 = Article(headline="Django lets you build web apps easily")
a1.save()

a1.publications.add(p1)  # Calling the add() method
  1. Calling the set method. Note that the set method completely rewrites the existing relationships, so if you have multiple publications already added to the article, this will remove them all and only add the p1 publication.
a1.publications.set([p1]
  1. Adding via the other end of the M2M relationship.
p1.article_set.add(a1)
3

Thanks.

So if I understand it correctly, what I need to do is not use add but set in a loop to create the relationship. From what I understand, I only need to do this once in my app and along as I use the same sqllite3 db I do not need to do this each time it runs, although if I do it will simply replace existing values with new ones; but unless I add new questions and or add/change answers I need not run it again.

I guess instead of hardcoding the question list I could use:
question_list = list(Questions.objects.get().values_list('id', flat=True))

as long as the answer_list has the same number of items, so new questions would be automatically included and I just need to add the answers pk

I defined the function as relation() and call it within the app:

def relation():
#question_list is list of questions pk to relate answers
    questions_list = [87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151]

#answer_list is answer pk related to question
    answer_list = [[17,38],[38],[38],[18,19],[38,18,19],[19],[19],[2],[19],[17,13],[17],[17,13],[14],[44],[14,15,22],[41,7,42],[41,7,42],[41,7,42],[41,7,42],[41,7,42],[41,42,8],[24],[41,18],[31],[35,43,44],[26,28],[31,34],[31,11,44],[8,9,32],[28,34,48],[34],[31,44],[20,21],[29],[36,1,15,14,22,25,33,47,40,10],[9,47],[9],[25,46],[16,22],[5,22],[9,25,46],[9,14],[9,14],[9,6],[9,45],[8,9],[9,14,39],[9,14,39],[39],[39],[37,32],[32,8],[37,8,32],[23],[20,21,9],[20,21,9],[20,21,9],[9,20,27],[3],[3],[3],[30],[4,12],[4,12],[4,12]]
    n = 0 # List element to get values for question pk and answers related to that question
    for item in questions_list: #Loop through all questions
        a=answer_list[n] #Get answer list item based on n
        q = Question.objects.get(pk=item) #Get pk from question list
        q.recomendations_set.set(a) #relate answer to question
        n +=1 #increment counter

    return
4

You use the two calls for different purposes.

The add function adds a new instance of the relationship between the two models.

The set function replaces all relationships on the base instance with the supplied set.

Regarding your example, it may be helpful if you identified where these values are coming from.

Where does this answer_list come from? Are you manually identifying these relationships? If so, this may be a case where you’re better off just using the admin to set these.

Now regarding your code, if this is something you want to embody within code instead of just setting it from the admin, I’d consider a different organization.

You’re doing a lot of excess and unnecessary data manipulations.

Specifically, you don’t need to create questions_list - it provides no benefit when all you’re using it for is a subsequent query.

Keep in mind, too, that these related-object functions (add, set) work with objects, not directly with the PKs.

Based upon what you’ve provided, your question_list list is better defined as:
question_list = Questions.objects.all()

Then your loop becomes:
for question in question_list:

But more fundamentally, if you want to manage this by pk, you’d be better off changing how you’re encoding this data.

If the questions_list are matched in length to the answer_list you’re showing here, you’d be better off coding it like this:
relationships = [[87, [17,38]]. [88,[38]], [89,[38]], [90, [18,19]], ...
This makes it obvious which IDs are related between the two.

This makes your loop something like:

for question, answers in relationships:
    Question.objects.get(pk=question).set(Answers.objects.filter(id__in=answers)

And yes, once you’ve done this once, these relationships are stored in the database and this doesn’t need to be rerun unless the relationships change.

As a result, I’d suggest doing this as a data migration within your app, or do it manually in the admin and then export it all as a fixture.

1 Like
5

Thanks,

I will change the list like you recommend and run it in the app once.

I’m trying to avoid manually coding 63 questions and making an error, and I’m not clear on what a fixture is; it appears to be a way to dump data from an app and use that elsewhere or as a backup to the db in case it gets damaged. I’ve simply been saving a copy of the db to a different location for backup.

It looks like it is useful for creating a test data set or initial dataset.

6

A fixture can be manually defined and created in your app. While it can be generated using the dumpdata command, they can be handwritten or created using serialization tools.

It contains serialized data which can then be loaded into your database.

It seems like fixtures would be perfect for your use case – if you have a predefined set of questions, answers, etc..

They will allow you to easily populate your database acrossed deployed instances, while still being real objects in your database – meaning you can edit them through the Django Admin or through the code (if you have/want to).

More on how to generate and load fixtures can be found here.