CODEWITHSUNDEEP
pythondjangodjango-querysetone-to-many
Custos
Custos

Reputation: 68

Django Order QuerySet by Min Value from Child Table

I am trying to create a specific complex query in Django that I am struggling with. I am using pagination to display a list of cakes on the index page. I want to sort them by different attributes. Below is a screenshot of my index page.

index page screenshot

Name (A-Z) & Name (Z-A) are implemented by simply ordering the Cake queryset by 'name' or '-name' which I am getting as POST from the form at the top of the page.

cakes_list = Cake.objects.all().order_by('name')

However I am struggling to order the queryset by minimum price. Each cake is available in different dimensions which have different prices (dimensions and prices between cakes are different). These are stored in Dimension with a foreign key pointing to the Cake they belong to. I want to find out the cheapest option for each cake and order my list of cakes that is used in the pagination based on that (both min price asc and desc).

I have also created a method from_price which returns the price of that cake. Which I use in my template to display each cake name together with the minimum price. But I cannot seem to be able to implement that into my sorting.

I appreciate help with how I can create a query or similar that allows me to sort all of my cakes based on the minimum price for each. I am just learning Django, so my current implementations might not be ideal.

vault/models.py:

class Cake(models.Model):
    name = models.CharField(max_length=200)

    def from_price(self):
        temp = self.dimension_set.aggregate(Min('price')).get('price__min')
        if not temp:
            temp = 0
        return temp

class Dimension(models.Model):
    cake = models.ForeignKey(Cake, on_delete=models.CASCADE)
    dimension = models.CharField(max_length=50)
    price = models.DecimalField(max_digits=6, decimal_places=2)

vault/views.py

def index(request):
    #from the form on the index page
    order_by = request.POST.get('order_by')
    if not order_by:
        order_by = 'name'

    cakes_list = Cake.objects.all().order_by(order_by)
    paginator = Paginator(cakes_list, 5)

    page_number = request.GET.get('page', 1)
    page_obj = paginator.get_page(page_number)
    return render(request, 'vault/index.html', {'page_obj': page_obj, 'order_by': order_by})

vault/templates/vault/index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

{# Form for cake sorting dropdown #}
<form action="{% url 'vault:index' %}" method="post">
    {% csrf_token %}
    <label for="order_by">Order by:</label>
    <select name="order_by" id="order_by">
        <option {% if order_by == "name" %} selected="selected" {% endif %} value="name">Name (A-Z)</option>
        <option {% if order_by == "-name" %} selected="selected" {% endif %} value="-name">Name (Z-A)</option>
        {% comment %}
        New options for ordering by price
        <option {% if order_by == "" %} selected="selected" {% endif %} value="name">Price from (Low to High)</option>
        <option {% if order_by == "" %} selected="selected" {% endif %} value="-name">Price from (High to Low)</option>
        {% endcomment %}
    </select>
    <input type="submit" value="Select">
</form>

{# Code for printing the list of cakes #}
{% if page_obj %}
    <ul>
        {% for cake in page_obj %}
            <li>
                <a href="{% url 'vault:detail' cake.id %}">
                    {{ cake.name }}
                    {% if cake.from_price %}
                        - from &#163;{{ cake.from_price|floatformat:'2' }}
                    {% endif %}
                </a>
            </li>
        {% endfor %}
    </ul>
{% else %}
    <p>No cakes are available</p>
{% endif %}

{# Code for the pagination navigation elements #}
<div class="pagination">
    <span class="step-links">
        {% if page_obj.has_previous %}
            <a href="?page=1">&laquo; first</a>
            <a href="?page={{ page_obj.previous_page_number }}">previous</a>
        {% endif %}
        <span class="current">
            Page {{  page_obj.number }} of {{ page_obj.paginator.num_pages }}.
        </span>

        {% if page_obj.has_next %}
            <a href="?page={{ page_obj.next_page_number }}">next</a>
            <a href="?page={{ page_obj.paginator.num_pages }}">last &raquo;</a>
        {% endif %}
    </span>

</div>
</body>
</html>

Upvotes: 0

Views: 858

Answers (2)

Charnel
Charnel

Reputation: 4432

If you need a min price value on each Cake record then probably the easies way is to use subquery:

from django.db.models import OuterRef, Subquery

sub_q = Dimension.objects.filter(cake=OuterRef('id')).order_by('price')
qs = Cake.objects.annotate(min_price=Subquery(sub_q.values('prize')[:1]).order_by('min_price')

Upvotes: 1

Sergey Pugach
Sergey Pugach

Reputation: 5669

Try to use annotation before ordering like that:

cakes_list = Cake.objects.annotate(
    max_price=Max('dimension__price'),
).order_by('max_price')

Upvotes: 1

Related Questions