Re: [Django] #6498: Add case insensitive model ordering
Django
-------------------------------------+-------------------------------------
Reporter: Bastian | Owner: nobody
Kleineidam <calvin@…> | Status: new
Type: New | Component: Database layer
feature | (models, ORM)
Milestone: | Severity: Normal
Version: SVN | Keywords:
Resolution: | Has patch: 1
Triage Stage: Accepted | Needs tests: 0
Needs documentation: 1 | Easy pickings: 0
Patch needs improvement: 0 |
UI/UX: 0 |
-------------------------------------+-------------------------------------
Changes (by Alex):
* ui_ux: => 0
* easy: => 0
* stage: Design decision needed => Accepted
Comment:
The idea of case insensitive ordering is accepted, some more thought
should be put into the API, * for case-sensitive isn't particularly
intuitive.
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:10>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Bastian | Owner: nobody
Kleineidam <calvin@…> | Status: new
Type: New | Component: Database layer
feature | (models, ORM)
Milestone: | Severity: Normal
Version: SVN | Keywords:
Resolution: | Has patch: 1
Triage Stage: Accepted | Needs tests: 0
Needs documentation: 1 | Easy pickings: 0
Patch needs improvement: 0 |
UI/UX: 0 |
-------------------------------------+-------------------------------------
Comment (by Alex):
I've closed #7580 as a dupe of this. This should be used to discuss a
general way to order by alternate things.
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:11>
Django
-------------------------------------+-------------------------------------
<calvin@…> | Status: new
Type: New feature | Version: SVN
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by mitar):
* cc: mmitar@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
¿What about a parameter like 'iexact'?
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Type: New feature | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by garrypolley):
Any reason this can't just work like this:
{{{#!python
my_model.objects.all().order_by(my_field__iexact)
}}}
I don't think that would cause any issues, it'll just be a bit of a chore
to get it added to all the backends.
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Type: New feature | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by garrypolley):
I'm thinking of doing this for the DjangoCon sprint, will it get accepted
if I do it?
My proposal is still the same
{{{#!python
my_model.objects.all().order_by('my_field__iexact')
}}}
This would also include a doc update about the change.
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Type: New feature | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by garrypolley):
I'd expect this python
{{{#!python
my_model.objects.all().order_by('my_field_iexact')
}}}
to become this SQL
{{{#!sql
SELECT * FROM my_model ORDER BY LOWER(my_field);
}}}
I'm also fine using UPPER, I'm not too particular either way. Seems most
people in raw SQL prefer UPPER.
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:16>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Type: New feature | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by aaugustin):
I don't find `my_field__iexact` much better than the original proposal of
`'*my_field'`... Try grabbing a core dev or two at the sprints to get a
design decision on the API :)
Otherwise, the most difficult part is to get this working on all four
databases supported by Django. Yes, this includes Oracle :) (I have the
infrastructure to test it if you don't and I'll be a the sprints
tomorrow.)
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:17>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Type: New feature | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
If custom lookups supports gets added, doing this for your project will be
much easier. See
https://github.com/akaariai/django/commit/89c4765044787ca8084541da0c31c2d47956c720#L1R73
for an example.
Of course, there is that if above...
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:18>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Type: New feature | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: garrypolley (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:19>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Type: New feature | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by shai):
I believe `my_model.objects.all().extra(select={'imf': 'UPPER(my_field)'},
order_by=['imf'])` should work on all core backends.
I am not sure a dedicated API is justified.
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:20>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Type: New feature | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
what about in case of foreign key field .... ?
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:21>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> | Status: new
Type: New feature | Version: master
Component: Database layer | Resolution:
(models, ORM) | Triage Stage: Accepted
Severity: Normal | Needs documentation: 1
Keywords: | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by mjtamlyn):
A dedicated API for this is likely not justified, but we should be able to
order by any `Transform` which can be applied to the field. At the moment,
case insensitive searches are not done as a `Transform`, although it would
be easy to create an `__upper` transform and use `__iexact` as a proxy to
it (also `__icontains` would proxy to `__upper__contains` for example).
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:22>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by JanMalte):
Any news about case insensitive ordering?
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:23>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> |
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
Yes, I think it's addressed in Django 1.8 with the ability to `order_by()`
[https://docs.djangoproject.com/en/dev/ref/models/expressions/
expressions]. For example:
{{{
>>> from django.db.models.functions import Lower
>>> MyModel.objects.order_by(Lower('myfield'))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:24>
Django
-------------------------------------+-------------------------------------
Reporter: Bastian Kleineidam | Owner: nobody
<calvin@…> |
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Pascal Polleunus):
It would still be useful to have `__upper` for the `ordering` options in
models and admin.
In my `ModelAdmin`…
I tried `ordering = [Lower("name_lower")]` but it crashes as not handled.
I tried by overriding `get_queryset()` using
`qs.order_by(Lower("name_lower"))`.
My method is executed and returns the correct query (i.e. `ORDER BY
LOWER("name")`) but it doesn't work in the admin (weird, the list is
sorted as "-name").
That query works correctly in PostgreSQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/6498#comment:25>