Versión Django views.py de SQL Join con Multi Table Query

¿Necesitas ayuda con la versión Django de la consulta de múltiples tablas SQL? La consulta utiliza 3 tablas para recuperar el nombre del restaurante, la dirección de la Restaurants table y el tipo de Cuisinetypes table . Todo se basa en el nombre de la cocina que se pasa a través de la URL y el ID de la cocina se guarda en la tabla de Cocina.

Modelos.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[-\w]+)/$', 'views.cuisinesearch'), 

vistas.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

Además, ¿cuál sería la forma correcta de mostrar la consulta?

 {% for restaurant in cuisinesearch %} 

{{ restaurant.name }}

{{ restaurant.location }}

Cuisines:

    {% for ct in restaurant.cuisine_types.all %}
  • {{ ct.name }}
  • {% endfor %}
{% endfor %}

Bueno, esos son algunos nombres de tablas y campos poco claros, pero lo mejor que puedo decir es que la consulta sería algo así como:

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

Pero a menos que esté bloqueado en ese esquema de base de datos, sus modelos se verían mejor como:

 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) 

Entonces la consulta sería más como:

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

Bien, veamos tu consulta, asumiendo que no hay cambios en tu código. Comenzaremos con la subconsulta.

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

Miramos la cláusula WHERE y vemos que necesitamos una ÚNETE. Para hacer una unión, debe declarar un campo relacional en uno de los modelos unidos (Django agregará una relación inversa, que deberíamos nombrar). Así que estamos combinando cuisine.cuisineid con `cuisinetype.cuisineid. Eso es un nombre horrible.

Esa es una relación de muchos a muchos, por lo que necesitamos un campo ManyToManyField . Bueno, mirando el modelo de Cuisine , es realmente la tabla de unión para este M2M. Django espera que una tabla de unión tenga dos campos ForeignKey , uno de los cuales apunta a cada lado de la unión. Normalmente creará esto para que ahorres cordura. Al parecer no tienes tanta suerte. Así que tienes que conectarlo manualmente.

Parece que el campo “GID” es un campo de ID (inútil) para el registro, así que asummos que es un entero de incremento automático. (Para estar seguro, marque los comandos CREAR TABLA). Ahora podemos reescribir el modelo de Cuisine en algo que se acerque a su sano juicio:

 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' 

Los nombres de los modelos se citan porque aún no se han definido los modelos (se encuentran más adelante en el archivo). Ahora no hay ningún requisito de que los nombres de los campos de Django coincidan con los nombres de las columnas, por lo que vamos a cambiarlos a algo más legible. El campo de ID de registro suele tener el nombre de id , y las claves externas suelen tener el nombre de lo que se relaciona con:

 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, hemos terminado de definir nuestra mesa conjunta. Mientras estamos en esto, apliquemos lo mismo a nuestro modelo de Cuisinetype . Anote el nombre de la clase de caja de camello corregido:

 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' 

Así que finalmente llegamos a nuestro modelo de Restaurant . Tenga en cuenta que el nombre es singular; un objeto solo representa un registro.

Me doy cuenta de que carece de dp_table o db_column , así que me dp_table y db_column Django lo está creando. Eso significa que podemos dejar que cree el campo de id para nosotros y podemos omitirlo de nuestro código. (Si ese no es el caso, simplemente lo agregamos como con los otros modelos. Pero realmente no debería tener una identificación de registro que ManyToManyField ). Y aquí es donde vive nuestro tipo de cocina ManyToManyField :

 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) 

Tenga en cuenta que el nombre del campo M2M es plural, ya que esa relación lleva a múltiples registros.

Una cosa más que querremos agregar a este modelo son los nombres para las relaciones inversas. En otras palabras, cómo volver de los otros modelos al Restaurant . Lo hacemos agregando los parámetros related_name . No es raro que sean iguales.

 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") 

Ahora ya estamos listos. Así que echemos un vistazo a su consulta:

 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 

Como esto es FROM restaurants , comenzaremos con el administrador de objetos predeterminado de ese modelo, los objects :

 Restaurant.objects 

La cláusula WHERE en este caso es una llamada a filter() , así que la agregamos para el primer término:

 Restaurant.objects.filter(city=8) 

Puede anular un valor de clave principal o un objeto City en el lado derecho de ese término. Sin embargo, el rest de la consulta se vuelve más compleja porque necesita la JOIN . Una combinación en Django solo se parece a una desreferenciación a través del campo de relación. En una consulta, eso significa unir los nombres de campo relevantes con un subrayado doble:

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

Django sabe en qué campos unirse, ya que está declarado en la tabla de Cuisine , que se cuisine_types through=Cuisine parámetro through=Cuisine en cuisine_types . también sabe hacer una subconsulta porque estás atravesando una relación M2M.

Así que eso nos lleva a SQL equivalente a:

 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') 

A mitad de camino. Ahora necesitamos SELECT DISTINCT para que no obtengamos varias copias del mismo registro:

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

Y tienes que tirar de los tipos de cocina para mostrar. Resulta que la consulta que tienes es ineficaz allí, porque solo te lleva a la tabla de unión y necesitas ejecutar más consultas para obtener los registros de CuisineType relacionados. Adivina qué: Django te tiene cubierto.

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

Django ejecutará dos consultas: una como la suya para obtener las identificaciones conjuntas, y una más para obtener los registros de CuisineType relacionados. Luego, los accesos a través del resultado de la consulta no necesitan volver a la base de datos.

Las dos últimas cosas son el ordenamiento:

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

Y el LIMIT :

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

Y está tu consulta (y la consulta relacionada) empaquetada en dos líneas de Python. Eso sí, en este punto, la consulta ni siquiera se ha ejecutado. Tienes que ponerlo en algo, como una plantilla, antes de hacer algo:

 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]) }) 

Modelo:

 {% for restaurant in cuisinesearch %} 

{{ restaurant.name }}

{{ restaurant.location }}

Cuisines:

    {% for ct in restaurant.cuisine_types.all %}
  • {{ ct.name }}
  • {% endfor %}
{% endfor %}