[FIXED] Django Make a Left Join with new col or empity field

Issue

i need print all items but also i need know if current user request.user have that item in list with SQL is just something like

select * from item as i
Left JOIN UserItems as ui ON ui.item=i.id
left Join user as u ON ui.user=u.id

so i just need check if u.id is not null, how i have to do with django for get all item list?

class User(AbstractUser):
    email = models.EmailField(unique=True)
    birthday = models.DateField(blank=True, null=True)

class UserItems(models.Model):
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    date = models.DateTimeField(auto_now_add=True)

class Item(models.Model):
    name = models.CharField(max_length=250 )
    created = models.DateTimeField(default=timezone.now)

obviously UserItems.objects.filter(useritem__user=request.user) dosen't get all items

Solution

You can annotate with an Exists subquery [Django-doc]:

from django.db.models import Exists, OuterRef

Item.objects.annotate(
    user_added=Exists(
        UserItems.objects.filter(item_id=OuterRef('pk'), user=request.user)
    )
)

The Items that arise from this will have an extra attribute user_added that is True if there exists a UserItems for the request.user and that Item and False otherwise.

Answered By - Willem Van Onsem