Opened 9 years ago

Closed 9 years ago

#2121 closed defect (worksforme)

Invalid SQL generated when using list_filter with search_fields

Reported by: noah@… Owned by: adrian
Component: Database layer (models, ORM) Version: master
Severity: major Keywords: sql, admin, search, list_filter, search_fields
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

I have a model as such

class Discussion(models.Model):
    topic = models.CharField(maxlength=200)
    body = models.TextField()
    person = models.ForeignKey(Person, null=False)
    team = models.ForeignKey(Team, null=False)
    
    def __str__(self):
       return self.team.__str__() + " thread " + self.topic
    
    class Admin:
       list_per_page = 100
       list_display = ('team', 'topic', 'person', 'body', )
       list_filter = ('team', )
       ordering = ('team', 'topic', 'person')
       search_fields = ('body', 'topic')

If I do a search for anything I get an error as such

ProgrammingError at /admin/mrt/discussion/
ERROR: missing FROM-clause entry for table "mrt_team" SELECT "mrt_discussion"."id","mrt_discussion"."topic","mrt_discussion"."body","mrt_discussion"."person_id","mrt_discussion"."team_id" FROM "mrt_discussion" WHERE ((("mrt_discussion"."body" ILIKE '%tim%' OR "mrt_discussion"."topic" ILIKE '%tim%'))) ORDER BY "mrt_team"."id" ASC

Because it's trying to order by mrt_team which is not part of the select. This needs to be remedied

Change History (5)

comment:1 Changed 9 years ago by noah@…

It would seem that it's sensitive to the ordering clause ordering = ('topic', 'team',) works where as ordering = ('team', 'topic', ) does not.

comment:2 Changed 9 years ago by noah@…

It would also seem the error rears it's head when using ordering = ('topic', 'team',) you then click on the team column to sort.

Checking my other models it seems you can't use list_filter with search_fields at the same time, as in you can't do a search and then click the filter or click the filter then search.

comment:3 Changed 9 years ago by anonymous

  • Keywords search list_filter search_fields added
  • Severity changed from major to critical
  • Summary changed from Invalid SQL generated when using ordering with search_fields to Invalid SQL generated when using list_filter with search_fields

comment:4 Changed 9 years ago by Link

  • Type defect deleted

comment:5 Changed 9 years ago by Gary Wilson <gary.wilson@…>

  • Resolution set to worksforme
  • Severity changed from critical to major
  • Status changed from new to closed
  • Type set to defect

Seems that this might have been fixed at some point along the way.

Using the models:

class Person(models.Model):
    name = models.CharField(maxlength=50)
    
    class Admin:
        pass
    
    def __str__(self):
        return self.name

class Team(models.Model):
    name = models.CharField(maxlength=50)
    
    class Admin:
        pass
    
    def __str__(self):
        return self.name

class Discussion(models.Model):
    topic = models.CharField(maxlength=200)
    body = models.TextField()
    person = models.ForeignKey(Person, null=False)
    team = models.ForeignKey(Team, null=False)
    
    def __str__(self):
       return self.team.__str__() + " thread " + self.topic
    
    class Admin:
       list_per_page = 100
       list_display = ('team', 'topic', 'person', 'body', )
       list_filter = ('team', )
       ordering = ('topic', 'team')
       search_fields = ('body', 'topic')

I don't get any errors when using any combinations of sorting columns, searching, and using the filter.

Note: See TracTickets for help on using tickets.
Back to Top