[Django]-How can I improve this many-to-many Django ORM query and model set?

by

3👍

✅

When you have sql query, that is hard to write using orm – you can use postgresql views. Not sure about mysql. In this case you will have:

Raw SQL like: 

CREATE VIEW photo_urls AS
Select
photo.id, --pseudo primary key for django mapper
Gallery.id as gallery_id, 
PhotoSize.PhotoURL as photo_url
From PhotoSize
Inner Join Photo On Photo.id = PhotoSize.Photo_id
Inner Join GalleryPhoto On GalleryPhoto.Photo_id = Photo.id
Inner Join Gallery On Gallery.id = GalleryPhoto.Gallery_id
Order By GalleryPhoto.Order Asc

Django model like:

class PhotoUrls(models.Model):
    class Meta: 
         managed = False 
         db_table = 'photo_urls'
    gallery_id = models.IntegerField()
    photo_url = models.CharField()

ORM Queryset like:

PhotoUrls.objects.filter(gallery_id=5)

Hope it will help.

👤Nikolay Fominyh

1👍

Django has some built in functions that will clean up the way your code looks. It will result in subqueries, so I guess it depends on performance. https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.values

gallery_photos = GalleryPhoto.objects.filter(Gallery=gallery).values('Photo_id', 'Order')
photo_queryset = PhotoSize.objects.selected_related('Photo', 'PhotoSizing').filter(
                 Photo__id__in=gallery_photos.values_list('Photo_id', flat=True))

calling list() will instantly evaluate the queryset, this might affect performance if you have a lot of data.

Additionally, there should be a rather easy way to get rid of if gallery_photo[0] == photo.Photo.id: This seems like it can be easily resolved with another query, getting gallery_photos for all photos.

👤dm03514

1👍

You can retrieve all data with a single query, and get a list of data dictionaries. Then you can manage this dictionary or create a new one to form your final dictionary… You can use reverse relations in filtering and selecting specific rows from a table… So:

Letx be your selected Galery…

GalleryPhoto.objexts.filter(Galery=x).values('Order', 'Photo__GUID', 'Photo__Photo__PhotoURL', 'Photo__Photo__PhotoSizing__SizeName', 'Photo__Photo__PhotoSizing__Width', 'Photo__Photo__PhotoSizing__Height', 'Photo__Photo__PhotoSizing__Type')

Using Photo__ will create an inner join to Photo table while Photo__Photo__ will create inner join to PhotoSize (via reverse relation) and Photo__Photo__PhotoSizing__ will inner join to PhotoSizing….

You get a list of dictionaries:

[{'Order':....,'GUID': ..., 'PhotoURL':....., 'SizeName':...., 'Width':...., 'Height':..., 'Type':...}, {'Order':....,'GUID': ..., 'PhotoURL':....., 'SizeName':...., 'Width':...., 'Height':..., 'Type':...},....]

You can select rows that you need and get all values as a list of dictionaries… Then you can Write a loop function or iterator to loop through this list and create a new dictionary whit grouping your data…

👤Mp0int

Leave a comment