如何改进这个多对多Django ORM查询和模型集?

I have a Django query and some Python code that I'm trying to optimize because 1) it's ugly and it's not as performant as some SQL I could use to write it, and 2) because the hierarchical regrouping of the data looks messy to me.

我有一个Django查询和一些要优化的Python代码，因为1)它很难看，而且不像我可以用来编写它的SQL那样出色;

So, 1. Is it possible to improve this to be a single query? 2. How can I improve my Python code to be more Pythonic?

所以,1。是否可以将其改进为单个查询?2。如何改进Python代码使其更符合Python语言?

Background

背景

This is for a photo gallery system. The particular view is attempting to display the thumbnails for all photos in a gallery. Each photo is statically sized several times to avoid dynamic resizing, and I would like to also retrieve the URLs and "Size Type" (e.g. Thumbnail, Medium, Large) of each sizing so that I can Lightbox the alternate sizes without hitting the database again.

这是为相片画廊系统。这个特定的视图试图显示一个图库中所有照片的缩略图。为了避免动态调整大小，每张照片都被静态地调整了几次大小，我还想检索每个大小的url和“Size类型”(例如，缩略图、中号、大号)，这样我就可以在不影响数据库的情况下选择不同的大小。

Entities

实体

I have 5 models that are of relevance:

我有5个相关模型:

class Gallery(models.Model):
    Photos = models.ManyToManyField('Photo', through = 'GalleryPhoto', blank = True, null = True)

class GalleryPhoto(models.Model):
    Gallery = models.ForeignKey('Gallery')
    Photo = models.ForeignKey('Photo')
    Order = models.PositiveIntegerField(default = 1)

class Photo(models.Model):
    GUID = models.CharField(max_length = 32)

class PhotoSize(models.Model):
    Photo = models.ForeignKey('Photo')
    PhotoSizing = models.ForeignKey('PhotoSizing')
    PhotoURL = models.CharField(max_length = 1000)

class PhotoSizing(models.Model):
    SizeName = models.CharField(max_length = 20)
    Width = models.IntegerField(default = 0, null = True, blank = True)
    Height = models.IntegerField(default = 0, null = True, blank = True)
    Type = models.CharField(max_length = 10, null = True, blank = True)

So, the rough idea is that I would like to get all Photos in a Gallery through GalleryPhoto, and for each Photo, I want to get all the PhotoSizes, and I would like to be able to loop through and access all this data through a dictionary.

大概的想法是，我想通过GalleryPhoto在一个图库中获取所有的照片，对于每一张照片，我想获取所有的照片大小，我希望能够通过字典循环访问所有的数据。

A rough sketch of the SQL might look like this:

SQL的粗略描述可能是这样的:

Select PhotoSize.PhotoURL
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
Where Gallery.id = 5
Order By GalleryPhoto.Order Asc

I would like to turn this into a list that has a schema like this:

我想把它变成一个有这样一个模式的列表:

(
    photo: {
        'guid': 'abcdefg',
        'sizes': {
            'Thumbnail': 'http://mysite/image1_thumb.jpg',
            'Large': 'http://mysite/image1_full.jpg',
            more sizes...
        }
    },
    more photos...
)

I currently have the following Python code (it doesn't exactly mimic the schema above, but it'll do for an example).

我目前有以下Python代码(它并不完全模仿上面的模式，但它将作为一个示例)。

gallery_photos = [(photo.Photo_id, photo.Order) for photo in GalleryPhoto.objects.filter(Gallery = gallery)]
photo_list = list(PhotoSize.objects.select_related('Photo', 'PhotoSizing').filter(Photo__id__in=[gallery_photo[0] for gallery_photo in gallery_photos]))

photos = {}
for photo in photo_list:
    order = 1
    for gallery_photo in gallery_photos:
        if gallery_photo[0] == photo.Photo.id:
            order = gallery_photo[1] //this gets the order column value

            guid = photo.Photo.GUID
            if not guid in photos:
                photos[guid] = { 'Photo': photo.Photo, 'Thumbnail': None, 'Sizes': [], 'Order': order }

            photos[guid]['Sizes'].append(photo)

    sorted_photos = sorted(photos.values(), key=operator.itemgetter('Order'))

The Actual Question, Part 1

实际问题，第1部分

So, my question is first of all whether I can do my many-to-many query better so that I don't have to do the double query for both gallery_photos and photo_list.

首先，我的问题是，我是否可以更好地执行多对多查询，这样我就不必为gallery_photos和photo_list执行双查询了。

The Actual Question, Part 2

实际问题，第2部分

I look at this code and I'm not too thrilled with the way it looks. I sure hope there's a better way to group up a hierarchical queryset result by a column name into a dictionary. Is there?

我看了这段代码，我对它的样子并不感到兴奋。我当然希望有一种更好的方法将分层的查询集结果按列名分组到字典中。是吗?

3 个解决方案

#1

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:

当您有sql查询时，这很难使用orm编写——您可以使用postgresql视图。不确定mysql。在这种情况下，您将拥有:

Raw SQL like:

原始SQL:

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:

Django模型:

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

ORM Queryset like:

ORM Queryset:

PhotoUrls.objects.filter(gallery_id=5)

Hope it will help.

希望它会有所帮助。

#2

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

Django有一些内置函数，可以清理代码的外观。这将导致子查询，因此我认为这取决于性能。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.

调用list()将立即评估queryset，如果您有大量数据，这可能会影响性能。

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.

此外，如果gallery_photo[0] = photo.Photo，应该有一种相当简单的方法可以去掉。id:这似乎可以通过另一个查询轻松解决，获取所有照片的gallery_photos。

#3

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....

使用Photo__将创建一个内部加入照片表虽然PhotoSize Photo__Photo__将创建内连接(通过反向关系)和Photo__Photo__PhotoSizing__将内连接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...

您可以选择您需要的行，并将所有值作为字典列表。然后，您可以编写一个循环函数或迭代器来循环这个列表，并创建一个新的字典对数据进行分组……

#1