Select one record from related table if exists, else return None

user_4569596 0
Viewed 1006

I have this two models:

  class Box(models.Model):

  class BoxImages(models.Model):
     box=models.ForeignKey(Box)
     img=models.ImageField()
     cover=models.IntegerField(default=0)

Only one image can be a cover image for a box and a box might not have any images at all. What I want now is to get list of boxes along with their cover image. But django uses inner join and brings boxes that have corresponding cover images only.

    Box.objects.filter(box_boximages__cover=1).values('id','box_label')

How do I force it to go with left join or left outer join since i read the ORM decides on its own which join to use?

django
1 Answers

If you want to have all boxes, no matter whether they have a cover image or not, just drop the filter. The filter effectively reduces your queryset to only those Boxes that have a cover image.

Box.objects.select_related('box_boximages').values('id','box_label', 'box_boximages__cover')

select_related will fetch the related BoxImages in the same query (if they exist). The queryset will contain all Box objects, even if they don't have BoxImages.

EDIT:

It returns all images; the box appears 5 items cos there are 5 pictures for it.

Yes, you are correct. values will add entries per fkey relation. There are several ways to cope with that.

(a) simplest is, not to use values but simply iterate over the queryset and use the Box instances with their related BoxImages. The code will be easy to understand. With the select_related you should also be fine performance wise.

(b) use aggregation to only fetch the box images that are covers. Have a look at the aggregate function. You can use it in combination with Exists to find the cover images (or Max).

(c) Create two QuerySets, one with Boxes without BoxImages and the other using the BoxImage Covers to get all the related Box instances. Combine those two.

Personally, I think (a) is the simplest and therefore best to understand when reading the code at a later point or for other developers.

If you always end up selecting based on cover versus not-a-cover image and you have the freedom to change the models then you could think about using two models + model inheritance and create BoxCover (OneToOneRelation to Box) and BoxImage (n:1 to Box) models. This would be the easiest to understand and handle.

user_6216900
Related