I am trying to write a query that retrieves all the categories that have at least one post attached to it. In other words, I want to 'exclude' any category that doesn't have any post.
These are my models for Category and Post:
class Category(models.Model):
title = models.CharField(max_length=250)
slug = models.SlugField(max_length=250, unique=True)
class Post(models.Model):
title = models.CharField(max_length=250)
slug = models.SlugField(max_length=250, unique=True)
body = models.TextField()
category = models.ForeignKey(Category, blank=True, default="")
This is the code I am using in my query, currently it fetches all the categories, even if no Post is 'attached' to it:
categories = Category.objects.all()
What I want is something like this:
categories = Category.objects.filter(
# Only ones that have at least one Post that has it's 'category' field set to it.
)
I've searched the docs and everywhere else, but I can't figure out a solution.
Please let me know how this can be done.
You can use following query:
categories = Category.objects.filter(post__isnull=False).distinct()
This will get all the categories where post is not null. Since, one category could have multiple posts, you will get duplicate instances with same id. Take a distinct to remove duplicate categories.
Note, that distinct(*fields) is postgresql specific. If you are using a different database, just use distinct().
Get all unique category ids by querying Post for distinct category and then filter Category by ids.
id_list = Post.objects.values_list('category_id').distinct()
catgories = Category.objects.filter(id__in=id_list)