python - Django views.py Version of SQL Join with Multi Table Query -
need django version of sql multi table query. query using 3 tables retrieve restaurant name, address restaurants table , cuisine type cuisinetypes table. based on cuisine name passed through url , cuisine id stored in cuisine table.
models.py
class restaurant(models.model): name = models.charfield(max_length=50, db_column='name', blank=true) slugname = models.slugfield(max_length=50, blank=true) address = models.charfield(max_length=100, blank=true) city = models.foreignkey('city', related_name="restaurants") location = models.foreignkey('location', related_name="restaurants") hood = models.foreignkey('hood', null=true, blank=true, related_name="restaurants") listingrole = models.foreignkey('listingrole', related_name="restaurants") cuisine_types = models.manytomanyfield('cuisinetype', null=true, blank=true, related_name="restaurants") class meta: db_table = 'restaurant' class city(models.model): name = models.charfield(max_length=50, db_column='city') state = models.charfield(max_length=50, blank=true, null=true) switch = models.smallintegerfield(null=true, blank=true, default='1') class meta: db_table = 'city' class cuisinetype(models.model): name = models.charfield(max_length=50, db_column='cuisine', blank=true) # field name made lowercase. switch = models.smallintegerfield(null=true, blank=true, default='1') class meta: db_table = 'cuisinetype' class location(models.model): name = models.charfield(max_length=50, db_column='location', blank=false, null=false) city = models.foreignkey('city', related_name="locations") switch = models.smallintegerfield(null=true, blank=true, default='1') class meta: db_table = 'location' class hood(models.model): name = models.charfield(max_length=50, db_column='hood') city = models.foreignkey('city', related_name='hoods') location = models.foreignkey('location', related_name='hoods') switch = models.smallintegerfield(null=true, blank=true, default='1') class meta: db_table = 'hood' class listingrole(models.model): id = models.autofield(primary_key=true, db_column='id') name = models.charfield(max_length=50, db_column='listingrole', blank=true) # field name made lowercase. switch = models.smallintegerfield(null=true, blank=true, default='1') class meta: db_table = 'listingrole' .... urls.py
url(r'^cuisine/(?p<cuisine>[-\w]+)/$', 'views.cuisinesearch'), views.py
def cuisinesearch(request, name='unknown'): name = name.replace('-', ' ').capitalize() return render_to_response('cuisinesearch.html', {'cuisinesearch': restaurant.objects.filter(city_id=8, switch=1, listingrole__in=[1,2,3,4], cuisine_types__name=name) .distinct().prefetch_related("cuisine_types").order_by('listingrole', 'displayorder')[:50] }) html
also correct way display query?
{% restaurant in cuisinesearch %} <h2>{{ restaurant.name }}</h2> <div class="location">{{ restaurant.location }}</div> <h3>cuisines:</h3> <ul class="cuisines">{% ct in restaurant.cuisine_types.all %} <li>{{ ct.name }}</li>{% endfor %} </ul> {% endfor %}
well, unclear table , field names, best can tell query like:
(restaurant.objects.filter(city=8, cuisine__cuisinetype__cuisine="italian").distinct().order_by('name')[:20]) but unless you're locked database schema, models 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 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 query more like:
restaurant.objects.filter(city=8, cuisines__name="italian").order_by('name')[:20] ok, let's walk through query, assuming no changes code. we'll start subquery.
select distinct res_id cuisine join cuisinetype on cuisine.cuisineid = cuisinetype.`cuisineid` cuisinetype.`cuisine` = 'italian' we @ clause , see need join. join, must declare relational field in 1 of joined models (django add reverse relation, should name). we're matching cuisine.cuisineid `cuisinetype.cuisineid. that's horrible naming.
that's many-to-many relation, need manytomanyfield. well, looking @ cuisine model, it's joining table m2m. django expects joining table have 2 foreignkey fields, 1 pointing each side of joint. it'll create save sanity. apparently you're not lucky. have manually hook up.
it seems "gid" field (useless) id field record, let's assume it's auto-increment integer. (to sure, check create table commands.) can rewrite cuisine model 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 quoted because models haven't been defined yet (they're later in file). there's no requirement django field names match column names, let's change them more readable. record id field named id, , foreign keys named after 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 @ this, let's apply same stuff our cuisinetype model. note 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 our restaurant model. note name singular; object represents 1 record.
i notice lacks dp_table or db_column stuff, i'm going out on limb , guessing django creating it. means can let create id field , can omit our code. (if that's not case, add other models. shouldn't have nullable record id.) , 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 name m2m field plural, since relation leads multiple records.
one more thing want add model names reverse relationships. in other words, how go other models restaurant. adding related_name parameters. it's not unusual them 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 set. let's @ query:
select restaurants.`name`, restaurants.`address`, cuisinetype.`cuisine` restaurants join cuisinetype on cuisinetype.cuisineid = restaurants.`cuisine` city_id = 8 , restaurants.id in ( select distinct res_id cuisine join cuisinetype on cuisine.cuisineid = cuisinetype.`cuisineid` cuisinetype.`cuisine` = 'italian') order restaurants.`name` limit 20 since from restaurants, we'll start model's default object manager, objects:
restaurant.objects the where clause in case filter() call, add first term:
restaurant.objects.filter(city=8) you can have wither primary key value or city object on right hand side of term. rest of query gets more complex, though, because needs join. join in django looks dereferencing through relation field. in query, means joining relevant field names double underscore:
restaurant.objects.filter(city=8, cuisine_type__name="italian") django knows fields join on because that's declared in cuisine table pulled in through=cuisine parameter in cuisine_types. knows subquery because you're going through m2m relation.
so gets sql equivalent to:
select restaurants.`name`, restaurants.`address` restaurants city_id = 8 , restaurants.id in ( select res_id cuisine join cuisinetype on cuisine.cuisineid = cuisinetype.`cuisineid` cuisinetype.`cuisine` = 'italian') halfway there. need select distinct don't multiple copies of same record:
restaurant.objects.filter(city=8, cuisine_type__name="italian").distinct() and need pull in cuisine types display. turns out query have inefficient there, because gets join table , need run further queries related cuisinetype records. guess what: django has covered.
(restaurant.objects.filter(city=8, cuisine_type__name="italian").distinct() .prefetch_related("cuisine_types")) django run 2 queries: 1 yours joint ids, , 1 more related cuisinetype records. accesses via query result don't need go database.
the last 2 things ordering:
(restaurant.objects.filter(city=8, cuisine_type__name="italian").distinct() .prefetch_related("cuisine_types").order_by("name")) and limit:
(restaurant.objects.filter(city=8, cuisine_type__name="italian").distinct() .prefetch_related("cuisine_types").order_by("name")[:20]) and there's query (and related query) packed 2 lines of python. mind you, @ point, query hasn't been executed. have put in something, template, before 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:
{% restaurant in cuisinesearch %} <h2>{{ restaurant.name }}</h2> <div class="location">{{ restaurant.location }}</div> <h3>cuisines:</h3> <ul class="cuisines">{% ct in restaurant.cuisine_types.all %} <li>{{ ct.name }}</li>{% endfor %} </ul> {% endfor %} 
Comments
Post a Comment