[Answered ]-Django equivalent of sub query

by

1👍

✅

You can achieve the same result using Django ORM without using a raw SQL query for your project.

from django.db.models import Count, F
from django.shortcuts import render
from django.utils import timezone

from .models import Book

def book_list(request):
    n_books = 10
    books = Book.objects.annotate(page_count=Count('page')).annotate(
        timestamp_formatted=F('timestamp').strftime('%Y/%m/%d')
    ).order_by('-timestamp').values('id', 'title','timestamp_formatted','page_count')[:n_books]
    return render(request, 'books/list.html', {'books': books})

Please note the 3 new modules I imported Count, F & timezone. You need to add these as your query has a timestamp and count functions and we used these functions to fetch and format data.

Try to render this code provided above and see what output you get and if this works for you. You can use python shell to test this if you feel comfortable. But make sure to import count & timezone modules before running on the shell.

Updated code (without using F class):

from django.db.models import Count, Func, Value, CharField
from django.shortcuts import render
from django.utils import timezone

from .models import Book

def book_list(request):
    n_books = 10
    books = Book.objects.annotate(
        page_count=Count('page'),
        timestamp_formatted=Func(
            Cast('timestamp', output_field=CharField()),
            Value('YYYY/MM/DD'),
            function='to_char',
        ),
    ).order_by('-timestamp').values('id', 'title', 'timestamp_formatted', 'page_count')[:n_books]
    return render(request, 'books/list.html', {'books': books})
👤dostogircse171

Leave a comment