Django ORM One-To-Many relationships problem with multiple tables

Question

Models:

class Mark(models.Model):
  name = models.CharField(max_length=200, verbose_name="Марка", unique=True, null=True)

  #...


class Model(models.Model):
  mark = models.ForeignKey(Mark, on_delete=models.CASCADE, verbose_name="Марка")
  name = models.CharField(max_length=200, verbose_name="Модель", unique=True, null=True)

  #...


class Car(models.Model):
  vin = models.CharField(max_length=100, null=True)

  #...

class Image(models.Model):
  car = models.ForeignKey(Car, related_name='images', on_delete=models.CASCADE)
  image = models.ImageField(upload_to=get_upload_path, verbose_name="Фото")

  #...

How to make the following query in Django ORM without raw:

query = "SELECT c.*, m.name mark_name, JSON_ARRAYAGG(img.image) image FROM incarcatalog_car c \
LEFT JOIN incarcatalog_mark m ON m.id=c.mark_id \
LEFT JOIN incarcatalog_image img ON img.car_id=c.id \
WHERE c.id>%s \
GROUP BY c.id;"
queryset = Car.objects.raw(query, [id])

My problem... I did a left join for the Mark, Model tables, but I don’t know how to connect the car table with the Image table

queryset = Car.objects.select_related("mark", "model").all()

But i don't know how to create realtionship with Image table.

Answer 1

But i don't know how to create realtionship with Image table.

You don't. The query you propose is problematic: it repeats the same data, which results in a lot of bandwidth from the database to the Django server, and also can increase memory usage.

You use .prefetch_related(…) ^[Django-doc] to fetch the images:

queryset = Car.objects.select_related('mark', 'model').prefetch_related('images')

this will fetch the images in a second request, and thus you can then for example print the images with:

for item in queryset:
    for image in item.images.all():
        print(image.image.url)

If the database supports aggregating data as a list, you can use:

from django.contrib.postgres.aggregates import ArrayAgg

queryset = Car.objects.select_related('mark', 'model').annotate(
    imgs=ArrayAgg('images__image')
)