Django QuerySet: Cannot resolve expression type, unknown output_field

Question

I have a tree structure in postgres and I want to get the number of nested nodes for each visible node counting all children except the hidden child and its descendants. You can look at the SQL query to get an idea. I am trying to implement following query in Django 1.11.20:

select 
id, 
path, 
(
    select count(*)
    from tree_node
    where 
        path <@ t.path and 
        not path <@ array(
            select path
            from tree_node
            where path <@ t.path and visibility_id = 0
        )
) as nested_nodes
from tree_node t;

What I am trying:

TreeQuerySet.py

...

def annotate_visibile_nested_nodes_count(self):
    """
    Get all nested nodes for current path except the hidden ones and their descendants
    """
    from src.tree.models import Visibility
    invisible_nested_nodes_paths = self.model.objects.filter(path__descendantsof=OuterRef(OuterRef('path')))\
                                                     .filter(visibility_id=Visibility.HIDE)\
                                                     .values('path')
    visible_nested_nodes_count = self.model.objects.filter(path__descendantsof=OuterRef('path'))\
                                                    .exclude(path__descendantsin=Subquery(invisible_nested_nodes_paths))\
                                                    .annotate(count=Count('*'))\
                                                    .values('count')[:1]

    return self.annotate(
        nested_nodes=Subquery(visible_nested_nodes_count, output_field=IntegerField())
    )

I am getting an error coming from Django as:

File ".../django/db/models/expressions.py", line 237, in output_field
raise FieldError("Cannot resolve expression type, unknown output_field")
FieldError: Cannot resolve expression type, unknown output_field

I can't figure out whether the error is coming from first annotate or the second one and how to fix it. I am using nested Subquery as inner most select refers to the outermost path column. The custom lookups are just for the postgres's <@ operator.

Thanks in advance for your help.

As we don't really know where your line 237 is, I can just say you are missing an `output_field=...` somewhere, maybe in your `Subquery(invisible_nested_nodes_paths, output_field=...)` — mistiru, Apr 08 '19 at 22:46
The error I pasted is coming from the Django's `django.db.models.expression` module. I did add the `output_field` to `Subquery` as you suggested but still getting the error. — shaffooo, Apr 08 '19 at 22:51
my bad for the `expression` module ^^" don't you have a clue of the line of your file that have thrown this error? — mistiru, Apr 08 '19 at 22:54
Unfortunately, stack trace is not very helpful. This is part of DRF so stack trace is just showing that error happened while compiling the query in a call to `paginate_queryset` method in DRF which is probably the time when query actually gets executed. — shaffooo, Apr 08 '19 at 23:03
Is there a reason you don't just return `visible_nested_nodes_count`? Everything after it seems unnecessary — Hybrid, Apr 08 '19 at 23:16
Because `invisible_nested_nodes_paths` and `visible_nested_nodes_count` represent the two inner `SELECT`s in the SQL query. I want to annotate the result of these two selects to the outer most select as `nested_nodes` as shown in SQL. — shaffooo, Apr 08 '19 at 23:19
@mistiru The model uses a custom Django field for path derived from a Postgres extension so in order to make it work you may need to define the custom Django field and custom lookups for that field. I can't paste the entire thing because it is not my personal code. Sorry about that. — shaffooo, Apr 08 '19 at 23:23
I'm sorry, I created some models that could match with your code, and the error does not appear. Might it be related to your custom fields? I'm afraid I can't help you much more >< good luck! — mistiru, Apr 08 '19 at 23:38
@mistiru Thanks for your help. I doubt that it is related to custom field as that just inherits from the Django's text field and only sets `editable=False` on it. — shaffooo, Apr 08 '19 at 23:46
Appears to be related to ticket https://code.djangoproject.com/ticket/30446 (Automatically resolve Value's output_field for stdlib types.). "Specifying an explicit output_field for Value should resolve the issue." value = Value( "some string", output_field=models.TextField(), ) — Snidhi Sofpro, Jul 30 '19 at 15:00

Django QuerySet: Cannot resolve expression type, unknown output_field

0 Answers0