Sorting by a nullable ForeignKey

[Alex Rades]

Hi,
In the admin site (using latest newforms-admin), it seems not possible to sort a table by a ForeignKey which is blank=True, null=True.
See this little example:

class Group(models.Model):
    name = models.CharField(max_length=200, primary_key=True)

class Person(models.Model):
    name = models.CharField(max_length=200, primary_key=True)
    group = models.ForeignKey(Group, blank=True, null=True)

Is there a valid reason for this limitation? At least on pgsql sorting by a nullable field is perfectly valid.

Thank you

[Karen Tracey]

Looking at the source code history, the lines of code that prevent null=True ForeignKey columns in a change list table from being sortable appears to have been there since the dawn of time.  So it's an old restriction with no ticket to refer to to see why it was added. 

However, I know that prior to queryset-refactor ordering by foreign key fields was notoriously fragile.  I think you needed to ensure select_related() was used on the QuerySet to force the join to the related table, but select_related() would not follow null=True ForeignKeys so trying to oder by a null=True ForeignKey probably just did not work or possibly dropped results from the list due to an inner join being used.  As recently as last week there were still problems with this exact scenario (see http://code.djangoproject.com/ticket/7181).

Assuming all such problems have all been fixed now, though, it would seem this restriction could be removed from admin.  I did some experimenting and if I remove the code in admin that prevents such sorting, everything seems to work.  I tried with sqlite, mysql, and postgres backends and included records with null values in my tests.

Anyone else know any reason why this restriction should be maintained?  So far as I can tell if the base queryset code now supports this kind of ordering admin ought to take advantage of it?

Karen

[Alex Rades]

Hi Karen,
could you point me to the code in admin which is responsible for this behaviour? I could produce a patch, do some testing and submit a ticket.

Thank you

[Alex Rades]

http://code.djangoproject.com/ticket/7582

On Tue, Jul 1, 2008 at 11:03 AM, Alex Rades <aler...@gmail.com> wrote:

Ok just tested and works. Lets open a ticket.
Thank you

On Tue, Jul 1, 2008 at 12:26 AM, Karen Tracey <kmtr...@gmail.com> wrote:

On Mon, Jun 30, 2008 at 5:51 PM, Alex Rades <aler...@gmail.com> wrote:

Hi Karen,
could you point me to the code in admin which is responsible for this behaviour? I could produce a patch, do some testing and submit a ticket.

I've attached the patch from the changes I made while experimenting with this (sent direct since I don't know if the group supports posts with attachments).  If you test it out and it works for you as well and nobody chimes in with a reason this is a bad idea, I'd say go ahead and open a ticket with the patch.

Karen

[nood...@gmail.com]

On Jul 1, 4:29 am, "Karen Tracey" <kmtra...@gmail.com> wrote:
> Assuming all such problems have all been fixed now, though, it would seem
> this restriction could be removed from admin.  I did some experimenting and
> if I remove the code in admin that prevents such sorting, everything seems
> to work.  I tried with sqlite, mysql, and postgres backends and included
> records with null values in my tests.
>
> Anyone else know any reason why this restriction should be maintained?  So
> far as I can tell if the base queryset code now supports this kind of
> ordering admin ought to take advantage of it?

There may be slight differences between the way the backends order
records with NULLs, e.g. some may put NULLs first, others last. (From
a quick glance it seems MySQL puts them first[1] and Oracle puts them
last[2] when sorting in ascending order).

I'll admit I'm not familiar enough with the respective backends to
know if these differences are already handled or not.

Also having records with NULL entries first/last varying across
different backends might not be a deal breaker for the admin.

Just my 2c.

[1] http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html
[2] http://www.orafaq.com/wiki/NULL