Django left join with AND condition.

[chiragsoni2401]

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

[Mariusz Felisiak]

You should use a name of filtered relation in the values_list('productlikedislike_product_liked_by_id') (see ​documentation).

Closing per TicketClosingReasons/UseSupportChannels.