CODEWITHSUNDEEP
djangodjango-models
Olivier Pons
Olivier Pons

Reputation: 15778

Django: how to do this query?

I have 3 models:

The model TagContent is for multilanguage: one Product may have many Tag, but those Tag show up in the Web page through TagContent:

class Tag(BaseModel):
    name = models.CharField(max_length=60, unique=True, default='')

class TagContent(BaseTranslatableModel):
    tag = models.ForeignKey(Tag, null=True, default=None)
    slug = models.SlugField(max_length=60, unique=True)
    locale = models.CharField(max_length=2)  # (e.g. "fr")

class Produit(BaseModel):
    name = models.CharField(max_length=60, unique=True)
    tags = models.ManyToManyField(Tag, related_name='produits')

Here's what I want to do: in my main page, the customer choose the language. Thus I will display all the products based on the language the customer has chosen.

In my main view, I want to display all the products, and all the tags so the user can click on a tag, and i'll filter the product.

The problem is that the tag should be translated in the current language. This means I have to filter the TagContent with the current locale, then get all Tag that are linked to those TagContent then get all the Product that are linked to those tags.

This should give something like (but it doesn't work because the foreign key is in the TagContent, and this is the main problem that blocks me):

Product.objects.filter(tags__in=
    Tag.objects.filter(contents__in=
        TagContent.objects.filter(langue__locale__exact=locale)
    )
)

And in the templating model I need something like:

        {% for p in products %}
            {% for tag. in p.tags.all %}
                {{ tag.name }}
            {% endfor %}
        {% endfor %}

In other words, I'd like to do this SQL 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'

-> 2 minutes to write this SQL, 3 hours that I'm looking for the solution.

Upvotes: 0

Views: 85

Answers (3)

NeoWang
NeoWang

Reputation: 18513

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

Upvotes: 1

trojjer
trojjer

Reputation: 639

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.

Upvotes: 0

Hank
Hank

Reputation: 3497

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)

Upvotes: 0

Related Questions