Django ORM Left Join query

[Razib Hossain Shuvo]

HI all,

I have the follow models . I want to find all the categories from the UserCategory table along with the budget for each category from the UserBudget model. I tried with select_related and prefetch and filtered with user_id but then it only returns budget that has matched with user_id. But i want to retrieve all the categories whether user has budget or not.

class UserCategory(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    cat = models.ForeignKey('preference.Category', null=True, on_delete=models.CASCADE)
    is_active = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

class Category(models.Model):
    name = models.CharField(max_length=255)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    is_active = models.BooleanField(default=True)

class UserBudget(models.Model):
    cat = models.ForeignKey('preference.Category', on_delete=models.CASCADE)
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    budget = models.DecimalField(default=0.00, decimal_places=2, max_digits=20)
    is_active = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

I tried following approach :

user_cats = UserCategory.objects.prefetch_related('cat__userbudget_set__user').filter(user_id=user_id)

Enter code here...

But the above query only returns UserCategory that matched with the user_id. But i want the following result.

select uc.id as cat_id, uc.user_id cat_user, ub.user_id as budget_user, ub.cat_id as budget_cat from user_categories as uc
left outer join user_budget as ub on uc.id = ub.cat_id
where uc.user_id = 2

Please help. Thanks.