如何在Django中的extra（）。values（）调用中获取相关对象？

Thank to this post I'm able to easily do count and group by queries in a Django view:

感谢这篇文章我能够轻松地在Django视图中对查询进行计数和分组:

Django equivalent for count and group by

Django相当于count和group by

What I'm doing in my app is displaying a list of coin types and face values available in my database for a country, so coins from the UK might have a face value of "1 farthing" or "6 pence". The face_value is the 6, the currency_type is the "pence", stored in a related table.

我在我的应用程序中正在显示的是一个国家数据库中可用的硬币类型和面值列表,因此来自英国的硬币可能具有“1个”或“6便士”的面值。 face_value是6,currency_type是“便士”,存储在相关表中。

I have the following code in my view that gets me 90% of the way there:

我在视图中有以下代码,它让我90%的方式:

def coins_by_country(request, country_name):
    country = Country.objects.get(name=country_name)
    coin_values = Collectible.objects.filter(country=country.id, type=1).extra(select={'count': 'count(1)'},
                               order_by=['-count']).values('count', 'face_value', 'currency_type')
    coin_values.query.group_by = ['currency_type_id', 'face_value']
    return render_to_response('icollectit/coins_by_country.html', {'coin_values': coin_values, 'country': country } )

The currency_type_id comes across as the number stored in the foreign key field (i.e. 4). What I want to do is retrieve the actual object that it references as part of the query (the Currency model, so I can get the Currency.name field in my template).

currency_type_id作为存储在外键字段中的数字(即4)而出现。我想要做的是检索它作为查询的一部分引用的实际对象(货币模型,所以我可以在我的模板中获取Currency.name字段)。

What's the best way to do that?

最好的方法是什么?

3 个解决方案

#1

You can't do it with values(). But there's no need to use that - you can just get the actual Collectible objects, and each one will have a currency_type attribute that will be the relevant linked object.

你不能用values()来做。但是没有必要使用它 - 你可以只获得实际的Collectible对象,并且每个对象都有一个currency_type属性,它将是相关的链接对象。

And as justinhamade suggests, using select_related() will help to cut down the number of database queries.

正如justinhamade建议的那样,使用select_related()将有助于减少数据库查询的数量。

Putting it together, you get:

把它放在一起,你得到:

coin_values = Collectible.objects.filter(country=country.id, 
                    type=1).extra(
                    select={'count': 'count(1)'}, 
                    order_by=['-count']
                ).select_related()

#2

select_related() got me pretty close, but it wanted me to add every field that I've selected to the group_by clause.

select_related()让我非常接近,但它希望我将我选择的每个字段添加到group_by子句中。

So I tried appending values() after the select_related(). No go. Then I tried various permutations of each in different positions of the query. Close, but not quite.

所以我尝试在select_related()之后追加values()。不行。然后我尝试了查询的不同位置中的每个的各种排列。关闭,但不完全。

I ended up "wimping out" and just using raw SQL, since I already knew how to write the SQL query.

我最终“懦弱”并且只使用原始SQL,因为我已经知道如何编写SQL查询。

def coins_by_country(request, country_name):
    country = get_object_or_404(Country, name=country_name)
    cursor = connection.cursor()
    cursor.execute('SELECT count(*), face_value, collection_currency.name FROM collection_collectible, collection_currency WHERE collection_collectible.currency_type_id = collection_currency.id AND country_id=%s AND type=1 group by face_value, collection_currency.name', [country.id] )
    coin_values = cursor.fetchall()
    return render_to_response('icollectit/coins_by_country.html', {'coin_values': coin_values, 'country': country } )

If there's a way to phrase that exact query in the Django queryset language I'd be curious to know. I imagine that an SQL join with a count and grouping by two columns isn't super-rare, so I'd be surprised if there wasn't a clean way.

如果有一种方法可以用Django查询集语言来表达那个确切的查询,我很想知道。我想,一个带有计数和两列分组的SQL连接并不是非常罕见的,所以如果没有一个干净的方法,我会感到惊讶。

#3

Have you tried select_related() http://docs.djangoproject.com/en/dev/ref/models/querysets/#id4

您是否尝试过select_related()http://docs.djangoproject.com/en/dev/ref/models/querysets/#id4

I use it a lot it seems to work well then you can go coin_values.currency.name.

我经常使用它似乎工作得很好然后你可以去coin_values.currency.name。

Also I dont think you need to do country=country.id in your filter, just country=country but I am not sure what difference that makes other than less typing.

另外我不认为你需要在你的过滤器中做country = country.id,只有country = country但是我不确定除了更少的输入之外还有什么区别。

#1