Django ORM query to get data from multiple tables

Question

I need to retrieve data from multiple tables to display shop name, shop location, shop monthly sales volume(unit), monthly sales amount, and sales person name who take orders from the shop. My sample table example below:

Order

id      total_price   added_by_id(employee id)   customer_id
1       42480         2                          1
2       74920         3                          2
3       21000         3                          2
4       42480         2                          1

OrderItem

order_id      unit
1             1
1             2
2             2
2             2
3             2
4             5

Customer

id    full_name      location  
1     Shop1          Example location1
2     Shop2          Example location2

Employee

id    full_name      
1     Employee Name1          
2     Employee Name2

And the resultant table should be

shop name    location     salesperson        sales volume   monthly sales
Shop1        location1    Employee Name1     8              43630
Shop2        location2    Employee Name2     6              95920

My Try:

Order.objects.select_related('account_customer').all()
.values('customer_id')
.annotate(total_sales=Sum('total_price'))
.filter(created_at__year=today.year, created_at__month=today.month)

It will return

<QuerySet [{'customer_id': 1, 'total_sales': Decimal('43630.00')}, {'customer_id': 2, 'total_sales': Decimal('95920.00')}]>

I can't retrieve other necessary information like shop name, location, salesperson name sales volume.

Related models:

class Order(models.Model):
    added_by = models.ForeignKey(Employee, on_delete=models.CASCADE)
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    total_price = models.DecimalField(max_digits=18, decimal_places=2)

class OrderItem(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE)
    unit = models.FloatField()

class Customer(models.Model):
    shop_name = models.CharField(max_length=100)
    address = models.CharField(max_length=100)

class Employee(AbstractUser):
    full_name = models.CharField(max_length=100)

How can I retrieve all of my necessary information?

can you add your related models ?

JPG
– JPG

2018-07-13 15:51:05 +00:00
Commented Jul 13, 2018 at 15:51 — JPG
– JPG, Commented Jul 13, 2018 at 15:51
added @JerinPeterGeorge

shafikshaon
– shafikshaon

2018-07-13 15:56:34 +00:00
Commented Jul 13, 2018 at 15:56 — shafikshaon
– shafikshaon, Commented Jul 13, 2018 at 15:56

JPG · Accepted Answer · 2018-07-13 18:15:27Z

1

You can add the required fields to values() as,

Order.objects.select_related('account_customer').all() \
    .values('customer_id', 'other_field_1', ...) \
    .annotate(total_sales=Sum('total_price')) \
    .filter(created_at__year=today.year, created_at__month=today.month)

This is equivalent to a SQL GROUP BY statement. For more details read the official django doc

UPDATE-1

Order.objects.annotate(
    location=F('customer__address'),
    salesperson=F('added_by'),
    shop_name=F('customer__shop_name')
).values('shop_name', 'salesperson', 'location').annotate(sales_volume=Count('orderitem__unit'),
                                                          monthly_sales=Count('total_price'))

edited Jul 13, 2018 at 18:15

answered Jul 13, 2018 at 17:30

JPG

89.4k24 gold badges164 silver badges235 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

shafikshaon Over a year ago

This query not return total volume. See my resultant table sales volume column

JPG Over a year ago

Your model fields and resultant table fields seem different. Please do correct it, or provide a sample SQL statement you wish to generate

shafikshaon Over a year ago

My resultant table sales volume should calculate from OrderItem unit column. Is there any confusion to you.?

JPG Over a year ago

location and sales volume are confusing

shafikshaon Over a year ago

Location are specified in customer model named as address and the unit column in orderitem model need to calculate based on order foreign key

|

Collectives™ on Stack Overflow

Django ORM query to get data from multiple tables

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related