Extra Select field in JSON

Fields from extra select query

hotels = Hotel.objects.filter(city = "Paris").extra(
    select={
        'review_count': 'SELECT COUNT(*) FROM hotel_review WHERE hotel_review.is_active = 1 AND hotel_review.hotel_id = hotel_hotel.id',
    },
)

could be used in templates:

{% for hotel in hotels %}
    {{hotel.name}} has {{hotel.review_count}} reviews
{% endfor %}

But when you try to serialize hotels QuerySet, you will have only fields from model and you won’t be able to access extra select field named “review_count” in resulting JSON file.

The solution is to serialize hotels.value() object:

from django.utils import simplejson

hotels_json = simplejson.dumps(list(hotels.values()))

And your JSON will include “review_count” field. It will look like this:

[
   {
      "name":"Hilton",
      "city":"Paris",
      "review_count":3
   }
]

Filter annotate() COUNT()

If you use annotate() function in your Django query and want to apply filters for it, you won’t be able to do it.
This is where extra() function can be used.

For example, here is my hotel app:

from django.db import models
class Hotel(models.Model):
    name = models.CharField(max_length=100)
    city = models.CharField(max_length=100)

class Review(models.Model):
    is_active = models.BooleanField(default =  False)
    content = models.CharField(max_length=500)
    hotel = models.ForeignKey(Hotel)

If I make search for hotels, that are situated in Paris and in my result view I want to display number of reviews for each object. I do this way:

from django.db.models import Count
hotels = Hotel.objects.filter(city = "Paris").annotate(review_count = Count(review))

But if I want to count only those reviews, that are active, I can not add one more filter() function, because conditions in all filter() function are combined together in WHERE statement of resulting SQL query, but I do not want to filter Hotel objects by hotel_review.is_active = True condition.

To tackle this we can do the following:

hotels = Hotel.objects.filter(city = "Paris").extra(
    select={
        'review_count': 'SELECT COUNT(*) FROM hotel_review WHERE hotel_review.is_active = 1 AND hotel_review.hotel_id = hotel_hotel.id',
    },
)

How to use review_count parameter in template and JSON objects see article about Extra Select Field in JSON

Export QuerySet to CSV

Converting a queryset to CSV:

# available from python 2.3+
import csv 

def export_to_csv(request):
    # get the response object, this can be used as a stream.
    response = HttpResponse(mimetype='text/csv')
    # force download.
    response['Content-Disposition'] = 'attachment;filename="export.csv"'

    # the csv writer
    writer = csv.writer(response)

    # just any model...
    cars = Car.objects.filter()        

    for car in cars:
        writer.writerow([car.model, car.year])

    return response