英文:

ManyToManyField self symetrical unique together relation

问题

Here's the translated code and information you requested:

我在尝试在Django应用程序中获取许多对多关联的数据时遇到了一些问题。

这是我的模型：

```python
class SoftwareVersion(models.Model):
    id = models.AutoField(
        primary_key=True,
        db_index=True
    )

    ... 其他字段 ...

    incompatibilities = models.ManyToManyField(
        "self",
        symmetrical=True,
        blank=True,
        default=None,
        through="Incompatibilities"
    )

class Incompatibilities(models.Model):
    id = models.AutoField(
        primary_key=True,
        db_index=True
    )

    softwareversion_a = models.ForeignKey(
        "SoftwareVersion",
        models.CASCADE,
        db_index=True,
        db_column='softwareversion_a',
        related_name='softwareversion_a',
        verbose_name="software version a",
    )

    softwareversion_b = models.ForeignKey(
        "SoftwareVersion",
        models.CASCADE,
        db_index=True,
        db_column='softwareversion_b',
        related_name='softwareversion_b',
        verbose_name="software version b",
    )

    status = models.BooleanField(
        verbose_name='Status',
        default=False
    )

    class Meta:
        unique_together = ('softwareversion_a', 'softwareversion_b')

在SoftwareVersion的save方法中，我添加了一个逻辑来为每个新的软件版本创建相关的Incompatibilities。我尝试过多种方法来实现它（使用循环或使用bulk_create），这是我使用的bulk_create函数：

# 在SoftwareVersion模型类中
def save(self, force_insert=False, force_update=False, using=None, update_fields=None) -> None:
    save = super().save(force_insert, force_update, using, update_fields)

    Incompatibilities.objects.bulk_create(
        (Incompatibilities(
            softwareversion_a=self,
            softwareversion_b=software_version,
            status=False
        ) for software_version in SoftwareVersion.objects.exclude(self)),
        ignore_conflicts=True,
        batch_size=1000
    )

    return save

我遇到的第一个问题是，这种方法忽略了unique_together约束，它创建了重复项。之前我使用循环在每个SoftwareVersion上创建对象，但这太慢了，所以我想使用bulk_create，但它似乎不按我的意图工作。是否有其他优化的方法或者是否有参数可以传递以尊重unique_together约束？

其次，查询“给出这个软件版本的所有不兼容性”的最优化方式是什么？目前，我正在这样做：

Incompatibilities.objects.filter(softwareversion_a=?)

但实际上我错过了在字段“softwareversion_b”中引用软件版本的不兼容性。我尝试使用以下逻辑：

SoftwareVersion.objects.get(pk=?).incompatibilities.all()

但Django输出相同的SQL请求，因此结果也相同。

Incompatibilities.objects.filter(softwareversion_a=?)

是否有一个参数可以放在某个地方，以便两个字段（这里是'softwareversion_a'和'softwareversion_b'）可以互换。或者我必须以特定方式查询以访问不兼容性对象。我尝试了以下查询：

Incompatibilities.objects.filter(Q(softwareversion_a=?) or Q(softwareversion_b=?))

但由于我必须根据softwareversion_a相关字段和softwareversion_b相关字段对结果进行排序，我无法使它按照我的意愿工作。

PS：对不起我的英语不好，注意我将模型翻译成英语，所以我可能做了一些错误，但在我的端上，模型编译和迁移是按预期工作的。

非常感谢您提前的任何帮助。

I've provided the translated code and information as requested.

<details>
<summary>英文:</summary>

I have some trouble trying to fetch some manytomanyrelated data in by Django Application

Here is my model :

SoftwareVersion(models.Model):
id = models.AutoField(
primary_key=True,
db_index=True
)

... Some other fields ...

incompatibilities= models.ManyToManyField(
    "self",
    symmetrical=True,
    blank=True,
    default=None,
    through="Incompatibilities"
)

Incompatibilities(models.Model):
id = models.AutoField(
primary_key=True,
db_index=True
)

softwareversion_a = models.ForeignKey(
    "SoftwareVersion",
    models.CASCADE,
    db_index=True,
    db_column='softwareversion_a ',
    related_name='softwareversion_a',
    verbose_name="software version a",
)

softwareversion_b = models.ForeignKey(
    "SoftwareVersion",
    models.CASCADE,
    db_index=True,
    db_column='softwareversion_b',
    related_name='softwareversion_b',
    verbose_name="softwareversion_b",
)

status = models.BooleanField(
    verbose_name='Status',
    default=False
)

class Meta:
    unique_together = (('softwareversion_a', 'softwareversion_b'),)

To this I add in the SoftwareVersion's save method a logic to create related Incompatibilities for each new software version. I have tried several way to do it (with a loop or with a bulk_create) here is the bulk create function i use :

Inside SoftwareVersion Model class

def save(self, force_insert=False, force_update=False, using=None, update_fields=None) -> None:
save = super().save(force_insert, force_update, using, update_fields)

Incompatibilities.objects.bulk_create(
   (Incompatibilities(
   softwareversion_a=self,
   softwareversion_b=software_version,
   status=False
        ) for software_version in SoftwareVersion.objects.exclude(self)),
        ignore_conflicts=True,
        batch_size=1000
    )

return save

First problem I have is this method ignore the unique_together constraint, it creates duplicates.
Before i was using a loop on each SoftwareVersion to create an object but it was too long so i wanted to use bulk_create but it seems to not work as i intended. Is there another optimized way to do it or a parameter to pass so that the unique together constraints is respected.

Secondly, what is the most optimized way to query "Give all the incompatibilities for this software version". Currently i am doing this :

Incompatibilities.objects.filter(softwareversion_a=?)

But i actually miss the incompatibilities which reference the software version in the field "softwareversion_b".
I have tried to use the logic :

VersionLogiciel.objects.get(pk=?).incompatibilities.all()

But django output the same SQL request and so, the same result.

Incompatibilities.objects.filter(softwareversion_a=?)

Is there a parameter to put somewhere so that two field (here 'softwareversion_a', 'softwareversion_b') can be interchangeable. Or maybe i have to query in a particular way to access to the incompatibilities objects. I have tried querying :

Incompatibilities.objects.filter(Q(softwareversion_a=?) or Q(softwareversion_b=?))

But since i have to order result on softwareversion_a related fields and softwareversion_b related field i can't get it working like i want.  
  
PS : Sorry for my bad english and notice that i translated the model to english so i may i have done some mistakes but on my side the model compilation and migrations is working as intended.

Thanks in advance for any help.

</details>


# 答案1
**得分**: 0

我选择在这里采用第二种解决方案：
https://charlesleifer.com/blog/self-referencing-many-many-through/
而不是向我的Django模型添加方法，我决定使用Django-pgtrigger创建PostgreSQL触发器。我的触发器允许复制更新、插入和删除操作。为了避免递归，我添加了一个 "如果存在" 的验证，以避免更新已经是最新数据的数据（插入和删除也是如此）。

<details>
<summary>英文:</summary>

I end up opting for the second solution here : 
https://charlesleifer.com/blog/self-referencing-many-many-through/

Instead of adding methods to my django model i deside to create postgres Trigger with django-pgtrigger. My trigger permit to replicate update, insert and delete. To avoid recursion i add a &quot;if exist&quot; verification to avoid updating already up to date data (same for insert and delete).


</details>