Django views.py Version of SQL Join with Multi Table Query

Well, those are some unclear table and field names, but best I can tell that query would look something like:

(Restaurant.objects.filter(city=8, 
     cuisine__cuisinetype__cuisine="Italian").distinct().order_by('name')[:20])

But unless you're locked into that database schema, your models would look better as:

class CuisineType(models.Model):
    name = models.CharField(max_length=50)
    class Meta:
        db_table = 'cuisinetype'

class Restaurants(models.Model):
    city = models.ForeignKey("City", null=True, blank=True) # Apparently defined elsewhere. Should be part of location?
    name = models.CharField(max_length=50)
    location = models.ForeignKey("Location", null=True, blank=True) # Apparently defined elsewhere.
    cuisines = models.ManyToManyField(CuisineType)

Then the query would be more like:

Restaurant.objects.filter(city=8, cuisines__name="Italian").order_by('name')[:20]

---

OK, let's walk through your query, assuming no changes to your code. We'll start with the subquery.

SELECT DISTINCT res_id FROM cuisine 
        JOIN    cuisinetype ON cuisine.cuisineid = cuisinetype.`cuisineid`
        WHERE   cuisinetype.`cuisine` = 'Italian'

We look at the WHERE clause and see we need a JOIN. To do a join, you must declare a relational field in one of the joined models (Django will add a reverse relation, which we should name). So we're matching up cuisine.cuisineid with `cuisinetype.cuisineid. That's some horrible naming.

That's a many-to-many relation, so we need a ManyToManyField. Well, looking at the Cuisinemodel, it's really the joining table for this M2M. Django expects a joining table to have two ForeignKey fields, one pointing to each side of the joint. Normally it'll create this for you to save sanity. Apparently you're not that lucky. So you have to manually hook it up.

It seems the "GID" field is a (useless) ID field for the record, so let's assume it's auto-increment integer. (To be sure, check the CREATE TABLE commands.) Now we can rewrite the Cuisinemodel into something approaching sane:

class Cuisine(models.Model):
    cuisinegid = models.AutoField(primary_key=True, db_column='CuisineGID')
    cuisineid = models.ForeignKey("Cuisinetype", null=True, 
        db_column='CuisineID', blank=True)
    res_id = models.ForeignKey("Restaurant", null=True, db_column='Res_ID', 
        blank=True)
    class Meta:
        db_table = 'cuisine'

The model names are quoted because the models haven't been defined yet (they're later in the file). Now there's no requirement that the Django field names match the column names, so let's change them to something more readable. The record ID field is usually just named id, and foreign keys are usually named after what they relate to:

class Cuisine(models.Model):
    id = models.AutoField(primary_key=True, db_column='CuisineGID')
    cuisine_type = models.ForeignKey("CuisineType", null=True, 
        db_column='CuisineID', blank=True)
    restaurant = models.ForeignKey("Restaurant", null=True, db_column='Res_ID', 
        blank=True)
    class Meta:
        db_table = 'cuisine'

OK, we're done defining our joint table. While we're at this, let's apply the same stuff to our Cuisinetype model. Note the corrected camel-case class name:

class CuisineType(models.Model):
    id = models.AutoField(primary_key=True, db_column='CuisineID')
    name = models.CharField(max_length=50, db_column='Cuisine', blank=True)
    class Meta:
        db_table = 'cuisinetype'

So we finally get to our Restaurant model. Note that the name is singular; an object only represents one record.

I notice that it lacks any dp_table or db_column stuff, so I'm going out on a limb and guessing Django is creating it. That means we can let it create the id field for us and we can omit it from our code. (If that's not the case, then we just add it like with the other models. But you really shouldn't have a nullable record ID.) And this is where our cuisine type ManyToManyField lives:

class Restaurants(models.Model):
    city_id = models.ForeignKey(null=True, blank=True)
    name = models.CharField(max_length=50, blank=True)
    location = models.ForeignKey(null=True, blank=True)
    cuisine_types = models.ManyToManyField(CuisineType, through=Cuisine,
        null=True, blank=True)

Note that the name for the M2M field is plural, since that relation leads to multiple records.

One more thing we will want to add to this model is names for the reverse relationships. In other words, how to go from the other models back to Restaurant. We do this by adding related_name parameters. It's not unusual for them to be the same.

class Restaurant(models.Model):
    city_id = models.ForeignKey(null=True, blank=True, 
        related_name="restaurants")
    name = models.CharField(max_length=50, blank=True)
    location = models.ForeignKey(null=True, blank=True, 
        related_name="restaurants")
    cuisine_types = models.ManyToManyField(CuisineType, through=Cuisine,
        null=True, blank=True, related_name="restaurants")

Now we're finally set. So let's look at your query:

SELECT  restaurants.`name`, restaurants.`address`, cuisinetype.`cuisine`
FROM    restaurants
JOIN    cuisinetype ON cuisinetype.cuisineid = restaurants.`cuisine`
WHERE   city_id = 8 AND restaurants.id IN (
        SELECT DISTINCT res_id FROM cuisine 
        JOIN    cuisinetype ON cuisine.cuisineid = cuisinetype.`cuisineid`
        WHERE   cuisinetype.`cuisine` = 'Italian')
ORDER BY restaurants.`name`
LIMIT 20

Since this is FROM restaurants, we'll start with that model's default object manager, objects:

Restaurant.objects

The WHERE clause in this case is a filter() call, so we add it for the first term:

Restaurant.objects.filter(city=8)

You can have wither a primary key value or a City object on the right hand side of that term. The rest of the query gets more complex, though, because it needs the JOIN. A join in Django just looks like dereferencing through the relation field. In a query, that means joining the relevant field names with a double underscore:

Restaurant.objects.filter(city=8, cuisine_type__name="Italian")

Django knows which fields to join on because that's declared in the Cuisine table which is pulled in by the through=Cuisine parameter in cuisine_types. it also knows to do a subquery because you're going through an M2M relation.

So that gets us SQL equivalent to:

SELECT  restaurants.`name`, restaurants.`address`
FROM    restaurants
WHERE   city_id = 8 AND restaurants.id IN (
        SELECT res_id FROM cuisine 
        JOIN    cuisinetype ON cuisine.cuisineid = cuisinetype.`cuisineid`
        WHERE   cuisinetype.`cuisine` = 'Italian')

Halfway there. Now we need SELECT DISTINCT so we don't get multiple copies of the same record:

Restaurant.objects.filter(city=8, cuisine_type__name="Italian").distinct()

And you need to pull in the cuisine types for display. Turns out that the query you have is inefficient there, because it only gets you to the join table and you need to run further queries to get the related CuisineType records. Guess what: Django has you covered.

(Restaurant.objects.filter(city=8, cuisine_type__name="Italian").distinct()
    .prefetch_related("cuisine_types"))

Django will run two queries: one like yours to get the joint IDs, and one more to get the related CuisineType records. Then accesses via the query result don't need to go back to the database.

The last two things are the ordering:

(Restaurant.objects.filter(city=8, cuisine_type__name="Italian").distinct()
    .prefetch_related("cuisine_types").order_by("name"))

And the LIMIT:

(Restaurant.objects.filter(city=8, cuisine_type__name="Italian").distinct()
    .prefetch_related("cuisine_types").order_by("name")[:20])

And there's your query (and the related query) packed into two lines of Python. Mind you, at this point, the query hasn't even been executed. You have to put it in something, like a template, before it does anything:

def cuisinesearch(request, cuisine):
    return render_to_response('cuisinesearch.html', {
        'restaurants': (Restaurant.objects.filter(city=8, 
             cuisine_type__name="Italian").distinct()
             .prefetch_related("cuisine_types").order_by("name")[:20])
        })

Template:

{% for restaurant in cuisinesearch %}
<h2>{{ restaurant.name }}</h2>
<div class="location">{{ restaurant.location }}</div>
<h3>Cuisines:</h3>
<ul class="cuisines">{% for ct in restaurant.cuisine_types.all %}
<li>{{ ct.name }}</li>{% endfor %}
</ul>
{% endfor %}