[Fixed]-Django: how to do this query?

by

1👍

✅

You can use this orm query to get the products:

products = Product.objects.prefetch_related("Tag", "TagContent").
    filter(tags__tagcontent__locale="fr")

Django will produce a SQL just like your hand written one. However, multiple JOINs within one SQL is perhaps not a good idea, especially if the joined tables are quite large. Consider breaking the query into 2 might result in better performance (depends on the DB you are using):

fr_tags = Tag.objects.filter(tagcontent__locale="fr")
products = Product.objects.filter(tags__in=fr_tags)

Read about Field lookup that spans relationships:
https://docs.djangoproject.com/en/1.8/topics/db/queries/#lookups-that-span-relationships

👤NeoWang

0👍

Since you already have the SQL query, why don’t you just send a raw query instead. And you can just pass the data into your template. It would be something similar to this:

from django.db import connections

cursor = connection.cursor()
query = (""" SELECT tc.slug, tc.name
 FROM produit p
 JOIN produit_tag pt
     ON pt.produit_id = p.id
 JOIN tag t
     ON pt.tag_id = t.id
 JOIN tag_content tc
     ON tc.tag_id = t.id
 JOIN langue l
     ON tc.langue_id=l.id
 WHERE l.locale='fr' """)
cursor.execute(query)

data = []
for row in cursor.fetchall():
    slug = row[0]
    name = row[1]
    data_point = {'name': name, 'slug': slug}
    data.append(data_point)
👤Hank

0👍

If you’re not using PostgreSQL this is useless to you, but my former lead developer now maintains the django-pgviews project. It’s useful when you have SQL queries containing complex joins that Django might not be able to do efficiently (or at all), which you can represent in a custom SQL View. You sacrifice Django’s management, but you can keep using the ORM to query data for reading. Note also the django.contrib.postgres project that is in development.

👤trojjer

Leave a comment