CODEWITHSUNDEEP
sqldjangomany-to-many
Pali775
Pali775

Reputation: 55

SQL double many-to-many relations with only two tables

Having two tables, one of them is called Tasks and the other one Relations where every task in Tasks might have 0-to-many predecessors and similarly, 0-to-many successors that relations among tasks are stored in the Relations-table.

So, if Task_1 is predecessor of Task_2 and Task_2 is predecessor of Task_3 then I would like to store the following records in the Relation-table:

pk predecessor successor
0 1 2
1 2 3

for instance, using Django's model definition:

class Tasks(models.Model):
    name_text = models.CharField(max_length=200)
    duration_int = models.IntegerField(default=1)

class Relations(models.Model):
    predecessors = models.ManyToManyField(Tasks)
    successors = models.ManyToManyField(Tasks)

Unfortunately, it does not work. I believe, that it is because giving the definition for successors as another relation to Tasks what already does exist might be inappropriate; although I also believe that Relation-table shall cascade back to Task-table with both of the relation: with predecessor and with the successors too in order to ensure the integrity.

After modifying the model based on given instructions in comments and answers, migration is done, but somehow the "relations" cannot be stored.

class Tasks(models.Model):
    name_text = models.CharField(max_length=200)
    duration_int = models.IntegerField(default=1)

    relations = models.ManyToManyField(
        'Tasks',
        through='Relations',
        through_fields=('predecessors', 'successors'),
        related_name='whatever',
    )

class Relations(models.Model):
    predecessors = models.ForeignKey(
        Tasks,
        on_delete=models.CASCADE,
        related_name='predecessors_relations',
    )
    successors = models.ForeignKey(
        Tasks,
        on_delete=models.CASCADE,    
        related_name='successors_relations',
    )

Using the shell provided by Django, "tasks"can be created and saved, but it is not the case with the "relations".

Using the Django example Example I believe that the "relation" is part of "tasks", for instance, having two "tasks" already available in the DB:

>>> from simo.models import Relations
>>> from simo.models import Tasks
>>> t1 = Tasks.objects.filter(id=1)
>>> t2 = Tasks.objects.filter(id=2)
>>> r1 = Relations()

But whatever solution I try to save the r1 "relation", it drops syntax error; even if it is tried to save as t1.relations(...) it is just not accepted.

Would you please help with some advice?

Upvotes: 1

Views: 140

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477533

The modeling makes not much sense, you use two ForeignKey fields [Django-doc] for the predecessor and the successor:

class Task(models.Model):
    name_text = models.CharField(max_length=200)
    duration_int = models.IntegerField(default=1)
    successors = models.ManyToManyField(
        Task,
        through='Relation',
        through_fields=('predecessor', 'sucessor'),
        related_name='predecessor',
    )


class Relation(models.Model):
    predecessor = models.ForeignKey(
        Task, on_delete=models.CASCADE, related_name='successor_relations'
    )
    successor = models.ForeignKey(
        Task, on_delete=models.CASCADE, related_name='successor_relations'
    )

Upvotes: 0

misraX
misraX

Reputation: 985

Since you are relating two fields to the same model, add different related names on the ManyToManyField.

predecessors = models.ManyToManyField(Tasks, related_name='tasks_predecessors_set')
successors = models.ManyToManyField(Tasks)

Recursive relationships using an intermediary model can’t determine the reverse accessors names, as they would be the same. You need to set a related_name to at least one of them. If you’d prefer Django not to create a backwards relation, set related_name to '+'.

Django ManyToManyField arguments ref

Django related_name ref

Upvotes: 0

Related Questions