id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30540	Django left join with AND condition.	chiragsoni2401	nobody	"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


"	Bug	closed	Database layer (models, ORM)	dev	Normal	invalid	FilteredRelation		Unreviewed	0	0	0	0	0	0