Django query returning empty data after order_by()

301 Views Asked by At

I'm making queries with filter() and exclude() depending on the search criteria of the user. I'm also sorting the query according to the search if specified. Everything works fine except when the user use the exclude() function and try to sort in a related_field. It works with filter(), though.

I'm using Django 1.10 and Postgresql

The model simplified is the following:

class Project(models.Model):
    name = models.CharField(max_length=175)
    class Meta:
        ordering = ['name']
        db_table = 'project'

class Summary(models.Model):
    mykey = models.CharField(max_length=20, primary_key=True)
    external_order_id = models.ManyToManyField('Project', blank=True)

The query

project = InventoryProject.objects.get(name='')
# with exclude
# this one returns [], but if I do .count() it returns the correct number of rows
queryset.annotate(num_projects=Count('external_order_id')).exclude(external_order_id=project, num_projects=1).order_by('external_order_id')

# with filter
# this one works like a charm
queryset = queryset.annotate(num_projects=Count('external_order_id')).filter(external_order_id=project, num_projects=1)

I checked the .query attribute before and after the order by and they seem very different

// before (it shows all the data)
SELECT "summary"."mykey", 
    COUNT("summary_external_order_id"."project_id") AS "num_projects" 
    FROM "summary" 
    LEFT OUTER JOIN "summary_external_order_id" ON ("summary"."mykey" = "summary_external_order_id"."summary_id") 
    GROUP BY "summary"."mykey" 
    HAVING NOT ("summary"."mykey" IN 
        (SELECT U1."summary_id" AS Col1 FROM "summary_external_order_id" U1 WHERE U1."project_id" = 1) AND COUNT("summary_external_order_id"."project_id") = 1)

// after (doesn't show any data)
SELECT "summary"."mykey", 
    COUNT("summary_external_order_id"."project_id") AS "num_projects" 
    FROM "summary" 
    LEFT OUTER JOIN "summary_external_order_id" ON ("summary"."mykey" = "summary_external_order_id"."summary_id") 
    LEFT OUTER JOIN "project" ON ("summary_external_order_id"."project_id" = "project"."id") 
    GROUP BY "summary"."mykey", "project"."name" 
    HAVING NOT ("summary"."mykey" IN 
        (SELECT U1."summary_id" AS Col1 FROM "summary_external_order_id" U1 WHERE U1."project_id" = 1) AND COUNT("summary_external_order_id"."project_id") = 1) 
    ORDER BY "project"."name" ASC 

I'm not sure why django automatically group by 'mykey'. Also, if I remove "project"."name" from the group by it works...

EDIT: As requested here is the data for the "Project" table where ID is 1

id [pk] - integer | name - character varying(175)
1                 |   
0

There are 0 best solutions below