[Django] #30540: Django left join with AND condition

8 views

Skip to first unread message

Django

unread,

Jun 3, 2019, 8:37:24 AM6/3/19

to django-...@googlegroups.com

#30540: Django left join with AND condition
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
chiragsoni2401 |
Type: Bug | Status: new
Component: | Version: 2.1
Uncategorized |
Severity: Normal | Keywords: FilteredRelation
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I have 4 models Category, Product,Photo,ProductLikeDislike. I am left
joining 3 of them except Category.

**Models:**

{{{
class Category(models.Model):
name = models.CharField(max_length = 200, db_index = True)
slug = models.SlugField(max_length = 200, db_index = True, unique =
True)

class Meta:
ordering = ('name',)
verbose_name = 'category'
verbose_name_plural = 'categories'

class Product(models.Model):
category = models.ForeignKey(Category ,on_delete=models.CASCADE)
name = models.CharField(max_length = 200, db_index = True)
slug = models.SlugField(max_length = 200, db_index = True)
description = models.TextField(blank = True)
price = models.DecimalField(max_digits = 10, decimal_places = 2 )
created = models.DateTimeField(auto_now_add=True)
updated = models.DateTimeField(auto_now=True)
contact= models.BigIntegerField(default=None,blank=True, null=True)
created_by = models.CharField(max_length = 200,
default=None,blank=True, null=True)
uploaded_by_id = models.IntegerField(default=0)
status = models.IntegerField(default=0)
mark_as_sold = models.IntegerField(default=0)

class Meta:
ordering = ('-created',)
index_together = (('id','slug'),)

class Photo(models.Model):

reference_id = models.ForeignKey(Product,
null=True,on_delete=models.CASCADE)
photo_type = models.CharField(max_length = 70, db_index = True)
file = models.FileField(upload_to='photos/',default='NoImage.jpg')
cover_photo_flag = models.CharField(default=0,max_length = 5, db_index
= True)
uploaded_at = models.DateTimeField(auto_now_add=True)
uploaded_by_id = models.IntegerField(default=0)
status = models.IntegerField(default=0)

class Meta:
ordering = ('-uploaded_at',)

class ProductLikeDislike(models.Model):
product_id = models.ForeignKey(Product,models.SET_DEFAULT,default=0)
product_liked_by_id =
models.ForeignKey(User,models.SET_DEFAULT,default=0)
status = models.BooleanField(default=False)
}}}

For left joining I wrote this query:
**Query:**

{{{
x=Product.objects.values_list('name','photo','productlikedislike')
}}}

Through this I am getting correct left join I printed and checked like
this:

**Note**: 'olx' is the name of my Django app.

{{{

print(x.query)

SELECT "olx_product"."name", "olx_photo"."id",
"olx_productlikedislike"."id"
FROM "olx_product" LEFT OUTER JOIN "olx_photo" ON ("olx_product"."id" =
"olx_photo"."reference_id_id") LEFT OUTER JOIN "olx_productlikedislike" ON
("olx_product"."id" = "olx_productlikedislike"."product_id_id") ORDER BY
"olx_product"."created" DESC
}}}

Now I want to add extra AND condition along with ON statement like this:

{{{
ON ("olx_product"."id" =
"olx_productlikedislike"."product_id_id"
AND "olx_productlikedislike"."product_liked_by_id"=2)
}}}

So for this somebody suggested me that use Django's
{{{
FilteredRelation
}}}
. I used but it is not adding extra AND condition along with ON

I used **FilteredRelation** like this:

{{{
x=Product.objects.annotate(
productlikedislike_product_liked_by_id=FilteredRelation('productlikedislike',
condition=Q(productlikedislike__product_liked_by_id=2))).values_list('name',
'photo','productlikedislike')
}}}

but getting the same sql query no extra AND condition. I am using Django
2.1.5

--
Ticket URL: <https://code.djangoproject.com/ticket/30540>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jun 3, 2019, 9:41:38 AM6/3/19

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* status: new => closed
* resolution: => invalid
* version: 2.1 => master
* component: Uncategorized => Database layer (models, ORM)

Comment:

You should use a name of filtered relation in the
`values_list('productlikedislike_product_liked_by_id')` (see
[https://docs.djangoproject.com/en/2.2/ref/models/querysets
/#filteredrelation-objects documentation]).