django 查询相关知识记录

1、 BomDetailed.objects.filter(bom=bom)..annotate(Count("sheet_name"))
查询语句为

SELECt "bom_bomdetailed"."id", "bom_bomdetailed"."bom_id", "bom_bomdetailed"."component_id", "bom_bomdetailed"."bom_serial", "bom_bomdetailed"."sheet_name", "bom_bomdetailed"."component_remark", "bom_bomdetailed"."component_quantity", "bom_bomdetailed"."component_position", 
COUNT("bom_bomdetailed"."sheet_name") AS "sheet_cnt" 
FROM "bom_bomdetailed" 
WHERe "bom_bomdetailed"."bom_id" = 71-16-3-1 
GROUP BY "bom_bomdetailed"."id", "bom_bomdetailed"."bom_id", "bom_bomdetailed"."component_id", "bom_bomdetailed"."bom_serial", "bom_bomdetailed"."sheet_name", "bom_bomdetailed"."component_remark", "bom_bomdetailed"."component_quantity", "bom_bomdetailed"."component_position

2、BomDetailed.objects.filter(bom=bom).values(“sheet_name”)
查询语句为

SELECt "bom_bomdetailed"."sheet_name" FROM "bom_bomdetailed" WHERe "bom_bomdetailed"."bom_id" = 71-16-3-1

3、BomDetailed.objects.filter(bom=bom).values("sheet_name").annotate(Count("sheet_name"))
查询语句为

SELECt "bom_bomdetailed"."sheet_name", COUNT("bom_bomdetailed"."sheet_name") AS "sheet_name__count" FROM "bom_bomdetailed" WHERe "bom_bomdetailed"."bom_id" = 71-16-3-1 GROUP BY "bom_bomdetailed"."sheet_name"

要查询 BomDetailed 指定 bom 存在多少种 sheet_name，需要使用 values 过滤，只查询sheet_name，然后使用 annotate 在对sheet_name 分组。改进这里我只想知道sheet_name名字，但是不知道如何才能只查询出名字，因此加了 Count("sheet_name") 。

model

class BomChangeList(models.Model):
    bom = models.ForeignKey(Bom, on_delete=models.CASCADE, default="")
    seq = models.IntegerField()
    material = models.CharField(max_length=20, default='')
    category = models.CharField(max_length=20, default='')
    spec = models.TextField()
    status = models.CharField(max_length=20, default='')
    brand = models.CharField(max_length=20, default='')
    location = models.CharField(max_length=100)

    class Meta:
        verbose_name = 'ChangeList信息'
        verbose_name_plural = verbose_name
        ordering = ['id']

目标任务：
筛选出相同的 bom 和 location 的数据，并对 seq 进行分组，看seq是否重复。
正确，需要对在对 seq 过滤分组后，添加 order_by(),参考信息

BomChangeList.objects.filter(location=change_list['location'], bom=bom).values("seq").annotate(Count("seq")).order_by()

SELECT "bom_bomchangelist"."seq", COUNT("bom_bomchangelist"."seq") AS "seq__count" FROM "bom_bomchangelist" WHERe ("bom_bomchangelist"."bom_id" = 71-16-3-1 AND "bom_bomchangelist"."location" = PTC1,FB9) GROUP BY "bom_bomchangelist"."seq"

错误，django会在后面再追加 id 进行分组

BomChangeList.objects.filter(location=change_list['location'], bom=bom).values("seq").annotate(Count("seq"))

SELECt "bom_bomchangelist"."seq", COUNT("bom_bomchangelist"."seq") AS "seq__count" FROM "bom_bomchangelist" WHERe ("bom_bomchangelist"."bom_id" = 71-16-3-1 AND "bom_bomchangelist"."location" = PTC1,FB9) GROUP BY "bom_bomchangelist"."seq", "bom_bomchangelist"."id"