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?

Answer

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'
    )

Advertisement

Answer