Django queryset - Add HAVING constraint after annotate(F())

user_14823395 0
Viewed 263

I had a seemingly normal situation with adding HAVING to the query. I read here and here, but it did not help me

I need add HAVING to my query

MODELS :

class Package(models.Model):
    status = models.IntegerField()


class Product(models.Model):
    title = models.CharField(max_length=10)
    packages = models.ManyToManyField(Package)

Products:

|id|title|
| - | - |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |

Packages:

|id|status|
| - | - |
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 2 |

Product_Packages:

|product_id|package_id|
| - | - |
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
| 3 | 2 |
| 2 | 3 |
| 4 | 3 |
| 4 | 4 |

visual

  • pack_1 (A, B) status OK
  • pack_2 (B, C) status not ok
  • pack_3 (B, D) status OK
  • pack_4 (D) status not ok

My task is to select those products that have the latest package in status = 1

Expected result is : A, B

my query is like this

SELECT prod.title, max(tp.id)
FROM "product" as prod
INNER JOIN "product_packages" as p_p ON (p.id = p_p.product_id)
INNER JOIN "package" as pack ON (pack.id = p_p.package_id)
GROUP BY prod.title
HAVING pack.status = 1

it returns exactly what I needed

|title|max(pack.id)|
| - | - |
| A | 1 |
| B | 3 |

BUT my orm does not work correctly I try like this

p = Product.objects.values('id').annotate(pack_id = Max('packages')).annotate(my_status = F('packages__status')).filter(my_status=1).values('id', 'pack_id')

p.query

SELECT "product"."id", MAX("product_packages"."package_id") AS "pack_id"
FROM "product" LEFT OUTER JOIN "product_packages" ON ("product"."id" = "product_packages"."product_id") LEFT OUTER JOIN "package" ON ("product_packages"."package_id" = "package"."id")
WHERE "package"."status" = 1
GROUP BY "product"."id"

please help me to make correct ORM

filter orm django python
3 Answers

How the query looks like when you remove

.values('id', 'pack_id')

At the end?

If I remember correctly then:

p = Product.objects.values('id').annotate(pack_id = Max('packages')).annotate(my_status = F('packages__status')).filter(my_status=1)

and

    p = Product.objects.annotate(pack_id = Max('packages')).annotate(my_status = F('packages__status')).filter(my_status=1).values('id')

Will result with different queries

user_171859270

Haki Benita has an excellent site that is made for Database Gurus and how to make to most of Django.

You can take a look at this post: https://hakibenita.com/django-group-by-sql#how-to-use-having

Django has a very specific way of adding the "HAVING" operator, i.e. your query set needs to be structured so that your annotation is followed by a 'values' call to single out the column you want to group by, then annotate the Max or whatever aggregate you want.

Also this annotation seems like it won't work annotate(my_status = F('packages__status') you want to annotate multiple status to a single annotation.

You might want to try a subquery to annotate the way you want.

e.g.

Product.objects.annotate(
    latest_pack_id=Subquery(
        Package.objects.order_by('-pk').filter(status=1).values('pk')[:1]
    )
).filter(
    packages__in=F('latest_pack_id')
)

Or something along those lines, I haven't tested this out

user_11841810

I think you can try like this with subquery:

from django.db.models import OuterRef, Subquery
sub_query = Package.objects.filter(product=OuterRef('pk')).order_by('-pk')
products = Product.objects.annotate(latest_package_status=Subquery(sub_query.values('status')[0])).filter(latest_package_status=1)

Here first I am preparing the subquery by filtering the Package model with Product's primary key and ordering them by Package's primary key. Then I took the latest value from the subquery and annotating it with Product queryset, and filtering out the status with 1.

user_26961650
Related