Using `values(..)` after `distinct(..)` over an annotated value

Using Django Using the ORM
1

Hello,

I have a fairly complex query where I add an annotation, then use distinct(..) to generate a PostgreSQL DISTINCT ON clause. A simplified example:

MyModel.objects.annotate(example_value=models.Value("example")).distinct("example_value")

Now, I’d like to use the results as a subquery; to do this, I tried to use the .values(..) method to get only the relevant component:

subquery = MyModel.objects.annotate(
  example_value=models.Value("example")
).distinct("example_value").values("another_field")

MyModel.objects.filter(
  another_field__in=subquery,
)

When running this, however, I see an error, and I’ve been able to narrow it down to the use of the values method. It seems like the use of values(..) here removes the annotation itself from query, and thus causes the distinct("example_value") to fail.

In raw SQL, I think I would just make the SELECT DISTINCT ON a subquery itself. Is there a clean way to do this in the Django ORM?

2

Welcome @justin-matmerize !

Can you post the complete error message you are getting?

I’m not anywhere where i can actually research this, but i might suggest changing values to values_list with the flat=True parameter. (I don’t seriously think it will work, something is nagging the back of my mind about this, but it’s probably worth a try. )

3

Hi @KenWhitesell ,

Sadly, switching to values_list did not work :frowning:

Here’s the full error:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/query.py", line 366, in __repr__
    data = list(self[: REPR_OUTPUT_SIZE + 1])
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/query.py", line 390, in __iter__
    self._fetch_all()
    ~~~~~~~~~~~~~~~^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/query.py", line 2000, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/query.py", line 95, in __iter__
    results = compiler.execute_sql(
        chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size
    )
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/compiler.py", line 1611, in execute_sql
    sql, params = self.as_sql()
                  ~~~~~~~~~~~^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/compiler.py", line 795, in as_sql
    self.compile(self.where) if self.where is not None else ("", [])
    ~~~~~~~~~~~~^^^^^^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/compiler.py", line 578, in compile
    sql, params = node.as_sql(self, self.connection)
                  ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/where.py", line 151, in as_sql
    sql, params = compiler.compile(child)
                  ~~~~~~~~~~~~~~~~^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/compiler.py", line 578, in compile
    sql, params = node.as_sql(self, self.connection)
                  ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/lookups.py", line 556, in as_sql
    return super().as_sql(compiler, connection)
           ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/lookups.py", line 239, in as_sql
    rhs_sql, rhs_params = self.process_rhs(compiler, connection)
                          ~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/lookups.py", line 543, in process_rhs
    return super().process_rhs(compiler, connection)
           ~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/lookups.py", line 331, in process_rhs
    return super().process_rhs(compiler, connection)
           ~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/lookups.py", line 126, in process_rhs
    sql, params = compiler.compile(value)
                  ~~~~~~~~~~~~~~~~^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/compiler.py", line 578, in compile
    sql, params = node.as_sql(self, self.connection)
                  ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/query.py", line 1323, in as_sql
    sql, params = self.get_compiler(connection=connection).as_sql()
                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/compiler.py", line 789, in as_sql
    distinct_fields, distinct_params = self.get_distinct()
                                       ~~~~~~~~~~~~~~~~~^^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/compiler.py", line 1047, in get_distinct
    _, targets, alias, joins, path, _, transform_function = self._setup_joins(
                                                            ~~~~~~~~~~~~~~~~~^
        parts, opts, None
        ^^^^^^^^^^^^^^^^^
    )
    ^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/compiler.py", line 1134, in _setup_joins
    field, targets, opts, joins, path, transform_function = self.query.setup_joins(
                                                            ~~~~~~~~~~~~~~~~~~~~~~^
        pieces, opts, alias
        ^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/query.py", line 1937, in setup_joins
    path, final_field, targets, rest = self.names_to_path(
                                       ~~~~~~~~~~~~~~~~~~^
        names[:pivot],
        ^^^^^^^^^^^^^^
    ...<2 lines>...
        fail_on_missing=True,
        ^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/tmp/temp.4lFU/.venv/lib/python3.13/site-packages/django/db/models/sql/query.py", line 1842, in names_to_path
    raise FieldError(
    ...<2 lines>...
    )
django.core.exceptions.FieldError: Cannot resolve keyword 'example_value' into field. Choices are: another_field, id

Also: thanks for the quick reply! Really appreciate this and all the work you’ve done helping people out on this forum. It’s saved me what would’ve been hours of debugging more times than I can count.

4

Ahh, this is what i get for trying to answer from my phone.

In this clause:

models.Value("example")

Are you trying to reference a field in the model or a value that exists in the context of the Python function running this query?

(A reference to a field would use the F function.)

5

Hi @justin-matmerize, could you describe what you’re trying to query here, or the sql you expect the ORM to run?

Note that MyModel.objects.annotate(example_value=models.Value("example")).distinct("example_value") annotates every row with the same value “example” and then calls distinct on that annotation – effectively collapsing all rows except the first one.

6

@KenWhitesell @cliff688 Ah, I was just trying to create a minimal reproduction of the issue. My real query annotates with a coalesce between two other fields on the model, filters down the subquery based on a value in the Python context, and filters down the outermost query again with another value from the Python context.

My goal is a SQL query which looks something like:

SELECT * FROM my_model WHERE another_field IN (
  SELECT another_field FROM (
    SELECT DISTINCT ON (example_value)
      COALESCE(field1, field2) "example_value", *
      FROM my_model
      WHERE field3 = '<a user supplied value>'
      ORDER BY field4
  )
) AND field5 = '<another user supplied value>';

I could just use RawSQL for the subquery here, but it feels awfully close to something representable in the ORM.

7

So another issue here is that you’re not defining subquery as a Subquery.

If this example is representative, then it should be:

MyModel.objects.filter(
  another_field__in=Subquery(subquery),
)

Otherwise, subquery is evaluated as a queryset and not created as a subquery to be added to the query. (See the example at Query Expressions | Django documentation | Django)

8

Until we add proper support for performing a subquery pushdown (see ticket-24462) there’s no way to achieve what you’re after without resorting to using a raw SQL primitives.

Until then your best bet it likely to define a Subquery subclass to mask a nested one like so

class SubqueryMask(Subquery):
    def __init__(self, mask: list[str], queryset: QuerySet):
        self.mask = mask
        super().__init__(queryset)

    # XXX: might have to override output_field to match mask

    def as_sql(self, compiler, connection):
        sql, params = super().as_sql(compiler, connection)
        mask = ", ".join(
            connection.quote_name(name) for name in self.mask
        )
        return "SELECT {mask} FROM ({sql})", params

And then use it like so

subquery = MyModel.objects.annotate(
  example_value=models.Value("example")
).distinct("example_value")

MyModel.objects.filter(
  another_field__in=SubqueryMask(["another_field"], subquery),
)