Get count of records even if zero

Question

I have below query in my script which returns me the number of records added today per source name. I have added a filter created_at__gte=today. Using this information I am creating one table. Now the problem is if there are no media records for any particular source name, it doesn't appear in the output. I would like that too to appear with count as zero. Can you please guide me how can I achieve that?

media_queryset = Media.objects.filter(
            created_at__gte=today
        ).values('source__name').annotate(
            total=Count('id'), last_scraped=Max('created_at')
        ).order_by('source__name')

Models:

class Media(models.Model):
    """Media represents the medium where person(s) (entity?) can be
    identified in.
    """

    # Relations
    #
    # NOTE: persons can be generic foreign key to an 'entity', since
    # we might consider organizations/brands
    persons = models.ManyToManyField(
        Person,
        blank=True,
        related_name="media",
        help_text="Persons identified in the media",
    )

    reviews = GenericRelation(Review, related_query_name="media")

    source = models.ForeignKey(
        Source,
        on_delete=models.SET_NULL,
        blank=True,
        null=True,
        related_name="media",
        help_text="Source for this media",
    )

    # Settings
    STATUS_CHOICES = [
        ("active", "active"),
        ("inactive", "inactive"),
        ("under_review", "under review"),
    ]
    status = models.CharField(
        db_index=True,
        max_length=64,
        choices=STATUS_CHOICES,
        default="inactive",
        help_text="The status of this model, influences visibility on the "
        "platform.",
    )

    # Data
    title = models.TextField(
        blank=True,
        null=True,
        help_text="Optional title of the media.",
    )

class Source(models.Model):
    """Designates a source from which media originates
    """
    TYPE_CHOICES = [
        ("entry", "entry"),
        ("scraper", "scraper"),
        ("unknown", "unknown"),
    ]
    type = models.CharField(
        max_length=64,
        choices=TYPE_CHOICES,
        default="unknown",
        help_text="Type of source",
    )

    name = models.TextField(
        blank=False,
        null=False,
        help_text="Domain name of the media where it originated from "
        "(e.g. youtube, vimeo, mrdeepfakes)",
    )

user_67579 · Answer

You should annotate the Source model instead:

from django.db.models import Count, Max, Q

# since Django-2.0

Source.objects.annotate(
    total=Count('media', filter=Q(media__created_at__gte=today)),
    last_scraped=Max('media__created_at', filter=Q(media__created_at__gte=today))
).order_by('name')

This will generate a QuerySet of Source objects, where every Source object will have two extra attributes: .total that contains the total number of related Media objects with created_at greater than or equal to today, and last_scraped being the Maximum of the created_ats greater than today.

It will return None if there is nu related Media object that satisfies the filter.

Get count of records even if zero

1 Answers