What will be the most efficient way to filter the objects in django

Question

I am working on a basic ecommerce website.I have created a superuser page in which I would like to see all the orders as well as the order details like customer who ordered, their address etc. These are my models.py:

class ShippingAddress(models.Model):
    customer=models.ForeignKey(Customer,on_delete=models.SET_NULL,null=True)
    order=models.ForeignKey(Order,on_delete=models.SET_NULL,null=True)
    address=models.CharField(max_length=200,null=False)
    email=models.EmailField(null=False)

class Customer(models.Model):
    user=models.OneToOneField(MyUser,null=True,blank=True,on_delete=models.CASCADE)
    email=models.CharField(max_length=100)

class Order(models.Model):
    customer=models.ForeignKey(Customer,on_delete=models.SET_NULL,null=True,blank=True)
    complete=models.BooleanField(default=False,null=True,blank=False)

class OrderItem(models.Model):
    product=models.ForeignKey(Product,on_delete=models.SET_NULL,null=True)
    order=models.ForeignKey(Order,on_delete=models.SET_NULL,null=True,)

And this is my views.py:

def superuser(request):
    user=User.objects.all()
    customer=Customer.objects.all()
    order=Order.objects.filter(complete=True)
    items=OrderItem.objects.all()
    shipping=ShippingAddress.objects.all()
    return render(request,"superuser.html",{'order':order,'items':items,'customer':customer,'shipping':shipping})

Currently in my template I am unable to iterate over the above context such that I can get all the orders and with the every order I can print their orderitems as well as shipping details as well as customer details. I tried in one way which was really in efficient that was to iterate over all orders then iterate over all orderitems and check if orderitem.order.id was equal to order.id . Please tell me what is the best method to pass the objects in context which are the most efficient for my need . And how to iterate over them in my template. Thanks

Answer 1

How about this?

# view
from django.shortcuts import render
from .models import Order

def superuser(request):
    orders = Order.objects.select_related('customer__user')
    orders = orders.prefetch_related('shippingaddress_set')
    orders = orders.prefetch_related('orderitem_set')
    return render(request,"superuser.html",{'orders':orders})

You can of course chain the .select_related and .prefetch_related calls on the same lines, I've split them up here for increased readability. You can read about select_related and prefetch_related in the docs. You can now use the 'orders' QuerySet in a template like this:

<!--template-->
{% if orders %}
<ul>
{% for order in orders %}
    <li>order id: {{order.id}}</li>
    <li>customer name: {{order.customer.id}}</li>
    <li>customer email: {{order.customer.email}}</li>
    {% if order.shippingaddress_set.all %}
    <li>Shipping addresses:
    <ul>
    {% for shipadd in order.shippingaddress_set.all %}
        <li>shipping address: {{shipadd.address}}</li>
    {% endfor %}
    </ul>
    </li>
    {% endif %}
    {% if order.orderitem_set.all %}
    <li>Order items:
    <ul>
    {% for item in order.orderitem_set.all %}
        <li>orderitem id: {{item.id}}</li>
    {% endfor %}
    </ul>
    </li>
    {% endif %}
{% endfor %}
</ul>
{% endif %}

Using Django Debug Toolbar and going to the page above, I'm informed that 3 SQL queries are made:

• One fetches data from the 'order' table, doing a LEFT OUTER JOIN with the 'customers' table ON customer id.
• One fetches data from the 'shippingaddress' table, with a WHERE clause specifying rows that have a 'order_id' IN (<list of all order id's>).
• A similar query is made for the 'orderitem' table.

This way, Django preemptively fetches all the required data from the database, rather than e. g. doing another query for every iteration.

You might find it strange that I included a loop for the shipping addresses in the HTML template. This is however necessary, because the way you've set up your models, there is a many-to-one relationship from shipping addresses to orders. This doesn't make a lot of sense IMO, so you will probably want to redefine the ShippingAddress model's relationships to the other models.

If you want to use additional information related to each order, you can of course add more prefetch_related/select_related calls before putting the QuerySet in your context.