There are 'images' that are attached to 'objects' through a ForeignKey, they can be several at each 'object'. There are 'subjects' that are also attached to 'objects' through ForeignKey. How to attach 'subject' one image from the 'object', noticed "select=1"? Through annotation, I can get either the number of images or all images.

Options that work but that's not what you need

Subject.objects.filter(object_id__hromada_id=hromada.id, state=3).annotate(image=Count('object__objectimages__image', filter=Q(object__objectimages__select=1)))

or

Subject.objects.filter(object_id__hromada_id=hromada.id, state=3).annotate(image=F('object__objectimages__image'))

class Object(models.Model):
    hromada = models.ForeignKey(Hromady, on_delete=models.CASCADE)
    state = models.IntegerField(choices=SELECT_CHOICES, default=SELECT_CHOICES[1][0])
...

class ObjectImages(models.Model):
    object = models.ForeignKey(Object, on_delete=models.CASCADE)
    image = models.ImageField(upload_to=upload_path_img)
    select = models.BooleanField(default=False)
...

class Subject(models.Model):
    object = models.ForeignKey(Object, on_delete=models.CASCADE)
    state = models.IntegerField(choices=SELECT_CHOICES, default=SELECT_CHOICES[1][0])
...

list subjects in Subject => Object => 1 image in ImageObject (select=1) or None

There are 'images' that are attached to 'objects' through a ForeignKey, they can be several at each 'object'. There are 'subjects' that are also attached to 'objects' through ForeignKey. How to attach 'subject' one image from the 'object', noticed "select=1"? Through annotation, I can get either the number of images or all images.

Options that work but that's not what you need

Subject.objects.filter(object_id__hromada_id=hromada.id, state=3).annotate(image=Count('object__objectimages__image', filter=Q(object__objectimages__select=1)))

or

Subject.objects.filter(object_id__hromada_id=hromada.id, state=3).annotate(image=F('object__objectimages__image'))

class Object(models.Model):
    hromada = models.ForeignKey(Hromady, on_delete=models.CASCADE)
    state = models.IntegerField(choices=SELECT_CHOICES, default=SELECT_CHOICES[1][0])
...

class ObjectImages(models.Model):
    object = models.ForeignKey(Object, on_delete=models.CASCADE)
    image = models.ImageField(upload_to=upload_path_img)
    select = models.BooleanField(default=False)
...

class Subject(models.Model):
    object = models.ForeignKey(Object, on_delete=models.CASCADE)
    state = models.IntegerField(choices=SELECT_CHOICES, default=SELECT_CHOICES[1][0])
...

list subjects in Subject => Object => 1 image in ImageObject (select=1) or None

• django

• 1 Can you share the corresponding models, and explain what you want to retrieve? It is not entirely clear to me. – willeM_ Van Onsem Commented Feb 4 at 17:50
• Added models. Need to add image field to queryset. Maybe there is another way. – Patrick Bond Commented Feb 4 at 21:10

1 Answer 1

You could try using a query like this.

queryset = (
    Subject.objects
    .filter(object__hromada_id=hromada.id, state=3)
    .annotate(
        image=models.Subquery(
            ObjectImages.objects
            .filter(object_id=models.OuterRef('object_id'), select=1)
            .values_list('image', flat=True)[:1]
        ),
    )
)

This should give you the expected results - for each subject, select by subquery one image that has select=1 and image.object_id=subject.object_id.