How to merge the result of two queries using update

Question

I have to show the name of the category and the quantity of items in each category. The items can change the category, so I save the history of the items in the table items_history

To show all the category name I'm running this query:

category_total = Category.objects.all()

Example of result:

, , , ]>

To get the quantity of items in each category, I have to check in the table items_history:

items_history = Category.objects.filter(items_history__current=1).annotate(qtd=Count("items_history__idea_id"))

Example of result:

]> 
 items_history[0].name -> 'Food'
 items_history[0].qtd -> 5

In this case, I have only items in Food Category. So I can't do just one query in items_history, because I'm not gonna get the quantity of the items in the other categories (0, in this case).

I need to get all the categories with their respective quantity, so if a category is not persisted in items_history I should get the category name and the quantity = 0. The result expected:

        items_history[0].name -> 'Drink'
        items_history[0].qtd -> 0

        items_history[1].name -> 'Food'
        items_history[1].qtd -> 5

        items_history[1].name -> 'Fun'
        items_history[1].qtd -> 0

        items_history[1].name -> 'Suplies'
        items_history[1].qtd -> 0

I'm trying to use update to merge the result of category_total and items_history like this:

 category_total.update(items_history);

But it's not working. How can I do that? If it's not possible, do u have other idea to merge the results of those queries?

My model:

 class Category(models.Model):
        name = models.CharField(max_length=50)
        description = models.TextField(max_length=500, blank=True,  null=True)
        order = models.PositiveSmallIntegerField(default=False)

        def __str__(self):
            return self.name



    class Items_History(models.Model):  # noqa   
        current_phase = models.ForeignKey('Category', on_delete=models.DO_NOTHING)
        previous_phase = models.PositiveSmallIntegerField()
        date_change = models.DateTimeField('data da mudança')
        idea = models.ForeignKey('Idea', on_delete=models.DO_NOTHING)
        author = models.ForeignKey('users.UserProfile', on_delete=models.DO_NOTHING)
        current = models.BooleanField()

user_67579 · Answer

As of django-2.0, we can use a filter parameter in the Count function [Django-doc].

This thus allows us to write a query like:

from django.db.models import Count, Q

Category.objects.annotate(
    qtd=Count("items_history__idea_id", filter=Q(items_history__current=1))
)

This will thus shift the filtering to is done in the COUNT(..) part itself, like:

SELECT category.*, 
       COUNT(CASE WHEN items_history.current
                THEN items_history.idea_id
                ELSE NULL END) AS qtd
FROM category
LEFT OUTER JOIN items_history ON category.id = items_history.current_phase_id
GROUP BY category.id

In case for the same Item and the same Category, there can be multiple Item_Historys with current is set to True, you also might want to add the distinct=True flag to it:

from django.db.models import Count, Q

Category.objects.annotate(
    qtd=Count(
        "items_history__idea_id",
        filter=Q(items_history__current=1),
        distinct=True
    )
)

How to merge the result of two queries using update

1 Answers