join 3 tables queryset Python Django

Question

I need join 3 tables in a query on Django.

I have two query's in MySQL, both return the same result, it doesn't matter to use one or the other query:

Query with where

SELECT dv.Division,
    COUNT(or.`ct_id`) AS `orders`,
    COUNT(CASE WHEN gr.`Status` = 'on going' THEN 1 ELSE NULL END) AS `ge_on_going`,
FROM order or, zone zo, cctt ct, division dv
WHERE or.zone_id = zo.id
AND or.cctt_id = ct.id
AND zo.division_id = dv.id
GROUP BY dv.Division

Query with join

SELECT dv.Division,
COUNT(or.`ct_id`) AS `orders`,
COUNT(CASE WHEN gr.`Status` = 'on going' THEN 1 ELSE NULL END) AS `ge_on_going`,
FROM order or
    INNER JOIN cctt ct ON (or.cctt_id = ct.id)
    INNER JOIN zone zo ON (or.zone_id = zo.id)
    INNER JOIN division dv ON (zo.division_id = dv.id)
GROUP BY dv.Division

How can that query be translated to django, I have this, but it doesn't work:

MODELS

class DivisionModel(models.Model):
    id = models.PositiveIntegerField(db_column='Id', primary_key=True)
    division = models.CharField(db_column='Division', max_length=50)
    
class ZoneModel(models.Model):
    id = models.AutoField(db_column='Id', primary_key=True)
    zone = models.CharField(db_column='Zone', max_length=50)
    division = models.ForeignKey(DivisionModel, on_delete=models.DO_NOTHING, db_column='Division_id')

class OrderModel(models.Model):
    zone = models.ForeignKey(ZonaModel, on_delete=models.DO_NOTHING, db_column='Zone_Id')
    cctt = models.ForeignKey(CCTTModel, on_delete=models.DO_NOTHING, db_column='CCTT_Id')
    fservice = models.DateTimeField(db_column='FServicio')

class CCTTModel(models.Model):
    status = models.CharField(db_column='Status', max_length=23)

Where is this `Status` field in models? – ruddra Aug 04 '20 at 10:56 — ruddra, Aug 04 '20 at 10:56
Where is the `Order` model defined? – Willem Van Onsem Aug 04 '20 at 11:00 — Willem Van Onsem, Aug 04 '20 at 11:00

ruddra · Accepted Answer · 2020-08-05T10:13:15.987

1

You can try like this with annotation:

from django.db.model import Q, Count

DivisionModel.objects.values('division').annotate(orders=Count('zonemodel__ordermodel__cctt'),  ge_on_going=Count('zonemodel__ordermodel__cctt', filter=Q(zonemodel__ordermodel__cctt__status="on going"))).values('division', 'order', 'ge_on_goin')

edited Aug 05 '20 at 10:13

answered Aug 04 '20 at 11:15

ruddra

50,746
7
78
101

Your solution is close, returns: `[{'id': 1, 'division': 'Spain', 'order': 44}, {'id': 2, 'division': 'Italy', 'order': 15}]>` But I need this return: `[{'division': 'Spain', 'order': 44, 'ge_on_goin': 4}, {'division': 'Italy', 'order': 15, 'ge_on_goin': 7}]>` – Manuel Aug 04 '20 at 12:05
Updated my answer. Please try again – ruddra Aug 04 '20 at 12:56
Perfect!! but only a detail, I don't need the id, it could be removed `[{'id': 1, 'division': 'Spain', 'order': 48, 'ge_on_goin': 0}, {'id': 2, 'division': 'Italy', 'order': 15, 'ge_on_goin': 4}]`. It´s posible? – Manuel Aug 05 '20 at 09:59
then just use `values('division', 'order', 'ge_on_goin')` instead of `values()` – ruddra Aug 05 '20 at 10:01
`QuerySet [{'division': 'Spain', 'order': 10, 'ge_on_goin': 10}, {'division': 'Spain', 'order': 22, 'ge_on_goin': 22}, {'division': 'Spain', 'order': 4, 'ge_on_goin': 4}, {'division': 'Spain', 'order': 0, 'ge_on_goin': 0}, {'division': 'Spain', 'order': 10, 'ge_on_goin': 10}, {'division': 'Spain', 'order': 2, 'ge_on_goin': 2}, {'division': 'Italy', 'order': 6, 'ge_on_goin': 6}, {'division': 'Italy', 'order': 1, 'ge_on_goin': 1}, {'division': 'Italy', 'order': 3, 'ge_on_goin': 3}, {'division': 'Italy', 'order': 1, 'ge_on_goin': 1}, {'division': 'Italy', 'order': 4, 'ge_on_goin': 4}]` – Manuel Aug 05 '20 at 10:11
Is not posible group_by('division') – Manuel Aug 05 '20 at 10:12
Hi @ruddra, I need change something. in OrderModel, field zone now is orderzone: `class OrderModel(models.Model): orderzone = models.ForeignKey(ZoneModel, related_name='OrderZone', on_delete=models.DO_NOTHING, db_column='Zone_Id')` When I run the query, return: `FieldError at /orderctdivision/ Unsupported lookup 'ordermodel' for AutoField or join on the field not permitted.` How would the query look? What do i have to change? – Manuel Aug 06 '20 at 09:23
then replace `ordermodel` with `OrderZone` in the query. – ruddra Aug 06 '20 at 10:19
Yeah, it´s perfect!!! And if I wanted to add a filter like .filter(fservice__lte=start_date)? Sorry for so many questions, but they come out as I go OrderModel: ` fservicio = models.DateTimeField(db_column='FServicio')` – Manuel Aug 06 '20 at 12:51
Add `.filter(OrderZone__fservice__lte=start_date)`. If you have further questions, I would recommend asking new question. Thank you for understanding :) – ruddra Aug 06 '20 at 12:55
You are right, here I leave the new question, https://stackoverflow.com/questions/63285324/i-join-3-tables-with-filter-in-a-query-on-django – Manuel Aug 06 '20 at 14:05

join 3 tables queryset Python Django

1 Answers1