[Django]-How do I get the related objects In an extra().values() call in Django?

by

1đź‘Ť

âś…

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

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.

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

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.

👤Jason Champion

2đź‘Ť

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.

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

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()
👤Daniel Roseman

0đź‘Ť

Have you tried 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.

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.

👤Justin Hamade

Leave a comment