martsberger / django-sql-utils Goto Github PK

Hi, is it possible to use the SubqueryAggregate class you provide in a context where the aggregate function's value is an aggregate containing a computed result as a value? Here is a stripped down version of the query I am trying to use your SubqueryAggregate class with.

        SELECT
            JSON_OBJECT_AGG(
                "id",
                JSON_BUILD_OBJECT(
                    'perc',
                    ROUND(
                        COALESCE(("dividend" / "divisor"), 0) * 100,
                        2
                    ),
                )
            )
        FROM
            (
                SELECT
                    "id",
                    (
                        SELECT
                            NULLIF(COUNT(*) :: decimal, 0)
                        FROM
                            "table_2" AS P
                        WHERE "id" = t1."id"
                    ) AS "divisor",
                    (
                        SELECT
                            COUNT(*)
                        FROM
                            "table_3" AS P
                        WHERE "id" = t1."id"
                    ) AS "dividend",
                FROM
                    "table_1" AS t1
                GROUP BY
                    t1."id"
            ) AS "totals"

Thanks

When used to annotate a PolymorphicQuerySet from django-polymorphic, this error is raised:

'NoneType' object has no attribute 'query'

in

django/db/models/expressions.py in get_source_expressions at line 1051
    def get_source_expressions(self):
        return [
            x for x in [
                getattr(expr, 'lhs', None)
                for expr in self.queryset.query.where.children
            ] if x
        ]
    def relabeled_clone(self, change_map):
        clone = self.copy()

summary:
we have three models
1-Student
2-Subject
3-Registration

in the subject model there is an attribute 'lecture_duration' that contains the duration of each subject in positive integers

the Registration will be the model that contains the students enrollments to subjects

what we want in our subquery is to calculate the sum of each student lecture_duration in the subjects they are enrolled in

e.g: if student A is enrolled in two subjects and each subject's lecture_duration is 1, then the answer that we want is 2

however, it seems that the package is not compatible with

related_query_name

and is causing the error below:

Error:

FieldError at /polls/
Related Field got invalid lookup: enrollment

models.py:

from django.db import models

class Student(models.Model):
    name = models.CharField(max_length=50)

    def __str__(self):
        return self.name


class Subject(models.Model):
    name = models.CharField(max_length=50)

    lecture_duration = models.PositiveIntegerField()

    def __str__(self):
        return self.name


class Registration(models.Model):
    student = models.ForeignKey(
        related_name="enrollments",
        related_query_name="enrollment",
        on_delete=models.CASCADE,
        to=Student,
    )

    subject = models.ForeignKey(
        related_name="enrollments", on_delete=models.CASCADE, to=Subject
    )

views.py:

from django.http import HttpResponse
from sql_util.utils import SubquerySum

from polls.models import Student

def bug_test(request):
    query = Student.objects.all().annotate(
        total_duration=SubquerySum("enrollment__subject__lecture_duration")
    )

    for i in query:
        print(i.total_duration)

    return HttpResponse("Ok")

the subquery will work if we remove

related_query_name="enrollment"

from

class Registration(models.Model):
    student = models.ForeignKey(
        related_name="enrollments",
        related_query_name="enrollment",
        on_delete=models.CASCADE,
        to=Student,
    )

and used

total_duration=SubquerySum("enrollments__subject__lecture_duration")

See

This makes the ordering argument in ArrayAgg impossible to use. Ditto for any custom Aggregation a user may want to write

Subqueries only work on annotate but not update. For example

Blog.objects.update(rating=SubqueryAvg('entries__rating'))

raises a FieldError: Joined field references are not permitted in this query

However, this works without any problems

Blog.objects.update(entry_count=SubqueryCount('entries'))

I expected django-sql-utils to have the same capabilities as the approach described at https://www.paulox.net/2018/10/01/updating-a-django-queryset-with-annotation-and-subquery/, but it doesn't.

While trying to tidy up some queries in an existing 1.11 project, came across an interesting bug and hopefully tracked it a little way down.

When trying to use postgres' ARRAY_AGG to collect some m2m names together as described in the readme, for certain queries it would give a
django.db.utils.ProgrammingError: more than one row returned by a subquery used as an expression

It seems that it's related to those models having a

class Meta:
    ordering = ('name',)

(where name is the field I want to aggregate)

tfh-galbraith@1682721

is a quick test based on yours which just sets that property on the Authors model to demonstrate.

Not sure I fully understand all the magic going on, but the issue seems to be that it adds the final target to the aggregation group by, when it potentially shouldn't?

Explicitly setting the unordered property/attr seems to fix this test, but I'm not sure if that is sufficient or if its just masking the problem except in the simple tests I've tried.

This packages is a lifesaver! Thank you.

I am getting this error when installing from pip.

Collecting django-sql-utils
  Using cached https://files.pythonhosted.org/packages/f1/ac/8ea2b8202de85c59560e3f7f5f4b57de461c387e512eb40574de11d4fa38/django-sql-utils-0.2.1.tar.gz
    ERROR: Complete output from command python setup.py egg_info:
    ERROR: Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/private/var/folders/y4/qbz9b6cx4z5b48vc_y6k_cbr0000gn/T/pip-install-iFRBHZ/django-sql-utils/setup.py", line 13, in <module>
        long_description=read_file('README.rst'),
      File "/private/var/folders/y4/qbz9b6cx4z5b48vc_y6k_cbr0000gn/T/pip-install-iFRBHZ/django-sql-utils/setup.py", line 5, in read_file
        with open(name) as fd:
    IOError: [Errno 2] No such file or directory: 'README.rst'
    ----------------------------------------
ERROR: Command "python setup.py egg_info" failed with error code 1 in /private/var/folders/y4/qbz9b6cx4z5b48vc_y6k_cbr0000gn/T/pip-install-iFRBHZ/django-sql-utils/

I think the README.rst is not uploaded to pip. Is there anyway to fix this?

I just wanted to say THANK YOU for this library. I have been noodling away on StackOverflow questions to figure out how to make a SubqueryCount work the way I want it, for two hours, with various cryptic exceptions, and stumbled upon a link for this library. It just works.

Thank you for building and maintaining it!

This commit appears to have broken it: django/django@96b6ad9

Passing None as the queryset parameter now crashes with AttributeError: 'NoneType' object has no attribute 'query' at https://github.com/martsberger/django-sql-utils/blob/master/sql_util/aggregates.py#L22

Now that Django 3.0 is released, a version bump and pypi release would be great.

Thanks