Django с MySQL: 'Подзапрос возвращает более 1 строки'

Используя django с БД MySQL и заданными моделями:

ModelB   ---FK--->   ModelA
 - c_id
 
ModelC

Я хочу получить все ModelC для каждой ModelA через аннотацию.

Я перепробовал множество вариантов, просматривая существующие решения, но не смог заставить его работать. Следующий код работает, когда есть только одна ModelC для каждой ModelA, но как только их становится больше одной, я получаю ошибку Subquery returns more than 1 row и не знаю, как получить список моделей ModelC. Я пытался построить список JSON объектов ModelC без успеха.

qs = ModelA.objects.all()

c_ids = (
    ModelB.objects \
        .filter(modela_id=OuterRef(OuterRef('id'))) \
        .values('c_id')
)
all_c = (
    ModelC.objects \
        .filter(id__in=Subquery(c_ids)) \
        .values('id')
)

qs1 = qs.annotate(all_c=Subquery(all_c ))
for p in qs1:
    print(p, p.all_c)

МодельВ похожа на таблицу перекрестков. Имея идентификатор, указывающий на A и C

Django поддерживает таблицы перекрестков.

Но когда дело доходит до аннотации объектов со списком идентификаторов, я не совсем уверен, что это возможно чисто средствами ORM.

class ModelA(models.Model):
    model_c_objects = models.ManyToManyField("ModelC", through="ModelB") 

class ModelB(models.Model):
    model_a = models.ForeignKey(ModelA, on_delete=models.CASCADE)
    model_b = models.ForeignKey(ModelB, on_delete=models.CASCADE)

class ModelC(models.Model):
    ...


# This one here I have no idea if it would work or not
ModalA.objects.prefetch_related("models_c_objects").annotate(model_c_object_ids=ArrayAgg("model_c_objects__id")

# If it doesn't:
class ModelA(models.Model):
    model_c_objects = models.ManyToManyField("ModelC", through="ModelB") 
    
    @property
    def model_c_object_ids(self):
        return list(self.model_c_objects.values("id", flat=True))

# And you can then use it like you wished
for model_a_object in ModelA.objects.prefetch_related("models_c_objects"):
    model_a_object.model_c_object_ids # list of model_c ids like: [1,4,12,63]

Я чувствую себя немного ленивым, но любое из двух решений должно работать, и оба они используют один запрос.

Я исхожу из предположения, что модель B действительно является сквозным столом для M2M отношений между моделью A и моделью C, как предложил Ишык Каплан.

В Postgres вы можете использовать ArrayAgg, как предложил Işık Kaplan. Эквивалент в MySQL в GROUP_CONCAT, но он не присутствует в ORM из коробки. Также из личного опыта я бы не рекомендовал его использовать, так как в моем случае он показал себя ужасно.

В итоге я объединил 2 запроса с помощью Python, что оказалось намного быстрее, чем 1 сложный запрос с GROUP_CONCAT (около 60K записей "Model A" и 20K "Model B" в моем случае). В вашем случае это выглядело бы так:

a_qs = ModelA.objects.all()
c_ids_dict = defaultdict(list)
c_ids = a_qs.values("id", "models_c_objects__id")
for item in c_ids:
    if item["models_c_objects__id"]:
        c_ids_dict[item["id"]].append(item["models_c_objects__id"])
for p in a_qs:
    print(p, c_ids_dict.get(p.id, []))

Следует сделать следующее

from django.db.models.aggregates import Aggregate

class JSONArrayAgg(Aggregate):
   function = "JSON_ARRAYAGG"
   
ModelA.objects.annotate(
    all_c=Subquery(
        ModelB.objects.filter(
            ref_type="c",
            modela_id=OuterRef("id"),
        ).values(
            "modela_id"
        ).values_list(
            JSONArrayAgg("ref_id")
        )
    )
)

что переводится как

SELECT
    model_a.*,
    (SELECT JSON_ARRAYAGG(model_b.ref_id)
     FROM model_b
     WHERE model_b.ref_type = "c" AND model_b.modela_id = model_a.id
     GROUP BY model_b.modela_id
    ) all_c
FROM model_a

Но было бы намного проще, если бы вы предоставили точное определение модели, так как это, скорее всего, всего лишь вопрос выполнения чего-то вроде

ModelA.objects.annotate(
   all_c=JSONArrayAgg(
      "modelb_set__ref_id", filter=Q(modelb_set__ref_type="c")
   )
)

которые переводятся как

SELECT
    model_a.*,
    JSON_ARRAYAGG(
       CASE WHEN model_b.ref_type = "c" THEN model_b.ref_id END
    )
FROM model_a
LEFT JOIN model_b ON (model_b.modela_id = model_a.id)
GROUP BY model_a.id
Вернуться на верх