CODEWITHSUNDEEP
sqldjangodjango-modelsdjango-querysetdjango-orm
Horion
Horion

Reputation: 152

Last N posts per category SQL to ORM

I'm starting my first Django blog app [after completing the Django official tutorial and Django Girls tutorial]. I'm having some trouble wrapping my head around Django ORM. I have this SQL query that works, but I feel that it is quite inefficient and I rather learn the correct way before going forward. 

Post.objects.raw(('SELECT * FROM (SELECT id, category, body, slug, author, title, 
published, row_number() OVER (PARTITION BY category) as rownum FROM post) tmp 
WHERE rownum < 5'))

Basically I want to display last 5 rows for each post category. The code above is already working, The problem is that when I loop trough each post in my templates it runs additional queries per each post when Post's get_absolute_url method is being called. I fixed this using Django's {% url %} tag but still 7 additional queries are run and I want to limit this to 2-3 max.

I have a model like this :

class Post(models.Model):
    title = models.CharField(max_length=250)
    category = models.CharField(max_length=30, choices=CATEGORY_CHOICES)
    tags = TaggableManager()
    slug = models.SlugField(max_length=250, unique_for_date='published')
    author = models.CharField(max_length=50)
    body = HTMLField('body')
    published = models.DateTimeField(default=timezone.now)
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)
    status = models.CharField(max_length=10, choices=STATUS_CHOICES,
                             default='draft')

    class Meta:
        ordering = ('-published',)
        db_table = 'post'

    def __str__(self):
        return self.title

    def get_absolute_url(self):
        return reverse('post_detail', args=[self.category, self.slug])

I tried for hours to get this to work via ORM but couldn't make it work. I ended up doing it in raw SQL, but I didnt know that 20+ additional queries will be run. The main goal is to display last 5 posts per category ordered by published date. I am using PostgreSQL.

Upvotes: 1

Views: 105

Answers (2)

mohammedgqudah
mohammedgqudah

Reputation: 568

i think you want the last 3 posts, if so you can slice 

def mayview(request):
    last_posts = Post.objects.all().order_by('-created_at')[:3]  # list of last posts
    return render(request, 'path/to/template', {'last_posts': last_posts})

in your template use the for loop to unpack the list:

{%for post in last_posts%}

<h1>{{post.title}}</h1>
<p>{{post.body}}</p>
<small>{{post.created_by}}</small>

{% endfor %}

Upvotes: 0

hynekcer
hynekcer

Reputation: 15558

Your solution was almost complete.

from django.db.models.expressions import RawSQL

qs = Post.objects.filter(pk__in=RawSQL(
    'SELECT id FROM '
    '  (SELECT id, '
    '    row_number() OVER (PARTITION BY category ORDER BY published DESC) as rownum '
    '   FROM post) tmp '
    'WHERE rownum < 5',
    []
)).order_by('category', '-published')

Window functions can be used in Django 2.0 instead of RawSQL. (should be updated after 2.0 release)

Upvotes: 1

Related Questions