Django ORM query with multiple join fields

Question

How could I make this mysql query into django ORM?

SELECT search_products.model, search_products.year, search_products.size, search_avg_price.estimated_price
FROM search_products
left JOIN search_avg_price
ON search_products.model=search_avg_price.model and search_products.size=search_avg_price.size and search_products.year=search_avg_price.year

I have already tried

latest_products = Products.objects.all().prefetch_related("avg_price")

but this is translated by django as

SELECT `search_products`.`id`, `search_products`.`size`, `search_products`.`year`, , `search_products`.`model`, `search_products`.`avg_price_id`
FROM `search_products`

and

latest_products = Products.objects.all().select_related("avg_price")

This is translated by django as:

SELECT `search_products`.`id`, `search_products`.`size`, `search_products`.`year`, , `search_products`.`model`, `search_products`.`avg_price_id`, `search_avg_price`.`id`, `search_avg_price`.`model`, `search_avg_price`.`size`, `search_avg_price`.`year`, `search_avg_price`.`estimated_price` 
FROM `search_products` 
INNER JOIN `search_avg_price` ON ( `search_products`.`avg_price_id` = `search_avg_price`.`id` )

if I run the above SQL in the database I get the proper result...

Edit: Models and views

class Avg_price(models.Model):
 model = models.CharField(max_length=50, blank=True)
 size= models.IntegerField(blank=True, null=True)
 year= models.IntegerField(blank=True, null=True)
 estimated_price= models.IntegerField(blank=True, null=True)

class Products(models.Model):
 product_id =models.IntegerField(blank=True, null=True)
 link_id = models.CharField(max_length=200, blank=True)
 location = models.CharField(max_length=200, blank=True)
 model = models.CharField(max_length=50, blank=True)
 size= models.IntegerField(blank=True, null=True)
 price= models.IntegerField(blank=True, null=True)
 year= models.IntegerField(blank=True, null=True)
 type=models.ForeignKey(Type)
 avg_price = models.ForeignKey(Avg_price)

 def __unicode__(self):  # Python 3: def __str__(self):
   return self.location

Template

{% if latest_products %}
  {% for product in latest_products %}
  <ul>
    <li id="col_one">
      <h5>{{product.avg_price.estimated_price}}</h5>
      <h5>{{product.model}}</h5>

You don't want select_related or prefetch_related for this, you want to use filter to tie to the other model, assuming a Foreign Key or Many to Many relationship. Can you post your model code? — Tom
– Tom, Commented Jul 25, 2014 at 17:16

Maxim Schepelin · Accepted Answer · 2014-07-31 13:21:22Z

3

You can pass tables list to method extra() and put conditions from ON of your sql query to where section in extra()

Product.objects.extra(
    tables=['search_avg_price'],
    where=[
        '"search_products"."model"="search_avg_price"."model"',
        '"search_products"."size"="search_avg_price"."size"',
        '"search_products"."year"="search_avg_price"."year"'
   ]
)

read more about extra: https://docs.djangoproject.com/en/1.6/ref/models/querysets/#extra

answered Jul 31, 2014 at 13:21

Maxim Schepelin

4515 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ihor Pomaranskyy Over a year ago

Is there a way to access values of the search_avg_price table joined with .extra()? Official docs are very brief. :(

Aaron Lelevier · Accepted Answer · 2014-07-25 21:41:27Z

1

Try the query like this:

qs = Product.objects.filter(
    avg_price__model__isnull=True).filter(
    avg_price__size__isnull=True).filter(
    avg_price__year__isnull=True).values_list(
    'model', 'year', 'size', 'avg_price__estimated_price')

To test:

print(qs.query)

Explanation:

From your Product model, you are accessing the ForeignKey each time on avg_price and the relative field in that model that you want to be NULL each time when you return the query based on a LEFT JOIN. Here's the documentation on look ups that span relationships:

https://docs.djangoproject.com/en/dev/topics/db/queries/#lookups-that-span-relationships

Then values_list let's you specify which values to return. Here's the documentation on values_list:

https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.values_list

edited Jul 25, 2014 at 21:41

answered Jul 25, 2014 at 19:44

Aaron Lelevier

21k13 gold badges82 silver badges113 bronze badges

3 Comments

user2950162 Over a year ago

I tried and got the error: "FieldError: Cannot resolve keyword 'avg_price.estimated_price' into field". It seems django is not being able to see the other table... Also I would need to show all results from table products and add the result from table avg_price whenever present (mach of year, model and size)... thanks for the help

Aaron Lelevier Over a year ago

How about with avg_price__estimated_price instead of avg_price.estimated_price?

user2950162 Over a year ago

The resulting query is different from the one I want to built... SELECT search_productsmodel, search_products.year, search_products.size, search_avg_price.estimated_price FROM search_products INNER JOIN search_avg_price ON ( search_products.avg_price_id = search_avg_price.id ) WHERE (search_avg_price.model IS NULL AND search_avg_price.size IS NULL AND search_avg_price.year IS NULL)

Collectives™ on Stack Overflow

Django ORM query with multiple join fields

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related