CODEWITHSUNDEEP
djangodjango-modelsdjango-querysetdjango-orm
şuayip üzülmez
şuayip üzülmez

Reputation: 826

Get Topics ordered by recent Entries

Suppose that I have models:

class Entry(models.Model):
    topic = models.ForeignKey(Topic)
    date_created = models.DateTimeField()

class Topic(models.Model):
    title = models.CharField(max_length=100)

I want to create a queryset that returns Topics which has Entries whose creation date is in last 24 hours. Topics should be ordered so that the first topic has the latest Entry.

I tried this with Postgres, but the ordering is lost:

Topic.objects.filter(entry_set__date_created__gte=timezone.now() - datetime.timedelta(hours=24))\
               .order_by('entry_set__topic', '-entry_set__date_created').distinct('entry_set__topic')

I found a solution to a similar problem but it is written in raw sql, and I couldn't convert it to django-orm.

Update:

So here is a working solution that achieves what I want in 2 querysets (I'm looking for a solution that can achieve this in one queryset):

desired_date = timezone.now() - datetime.timedelta(hours=24)
distinct_ids = Topic.objects.filter(entries__date_created__gte=desired_date).distinct().values_list('id', flat=True)
ordered_qs = Topic.objects.filter(id__in=distinct_ids).annotate(lts=Max("entries__date_created")).order_by('-lts')

Upvotes: 0

Views: 147

Answers (3)

JPG
JPG

Reputation: 88549

Have you tried this?


from datetime import datetime, timedelta
from django.db.models import Max

latest_entry_time = dict(last_entry_dt=Max('entries__date_created'))
last_24_hour_filter = dict(entries__date_created__gte=datetime.utcnow() - timedelta(hours=24))

result = Topic.objects.annotate(**latest_entry_time).filter(**last_24_hour_filter).order_by('-last_entry_dt')

Explanation

  1. latest_entry_time : Used to annotate the latest date_created value into each Topic object
  2. last_24_hour_filter : used to filter out the Entry instances
  3. .order_by('-last_entry_dt') : Since we do have the annotated field (see explanation 1), we can use that here.

    Hope its clear !!

Upvotes: 0

ANFAS PV
ANFAS PV

Reputation: 453

Topics can be ordered so that the first topic has the latest Entry by giving a model metadata.

Here -date_added will sort your topic as your wish. Give your model metadata by using an inner class Meta, like so:

class Topic(models.Model):
    title = models.CharField(max_length=100)

    class Meta:
        ordering = ('-date_added')

    def __unicode__(self):
        return str(self.name)

Model metadata is “anything that’s not a field”, such as ordering options (ordering), database table name (db_table), or human-readable singular and plural names (verbose_name and verbose_name_plural). None are required, and adding class Meta to a model is completely optional.

This official django documentation explains all the possible metadata options that you can give your model

Upvotes: 0

Art Vandelay
Art Vandelay

Reputation: 183

Edited:

desired_date = timezone.now() - datetime.timedelta(hours=24)
topics=Entry.objects.filter(date_created__gte=desired_date).order_by('date_created').values_list('topic', flat=True).distinct()

Upvotes: 1

Related Questions