order_by HStore Key/Value Pair

[David Muller]

I am currently trying out Django 1.8's HStore implementation, but am having trouble using the ORM's order_by function.

Say I have HStore dictionaries stored in my database like

{'key': 'value', 'en-US': 'english'}

and would like to order a queryset by the strings pointed to by one particular key.

I run into 2 distinct errors trying to 'order_by' either key ('key' or 'en-US'):

1.

MyModel.objects.order_by('name__key')

/usr/local/etc/virtualenvs/gears/local/lib/python2.7/site-packages/django/db/models/sql/query.py in names_to_path(self, names, opts, allow_many, fail_on_missing)
   1427                     raise FieldError(
   1428                         "Cannot resolve keyword %r into field. Join on '%s'"
-> 1429                         " not permitted." % (names[pos + 1], name))
   1430                 break
   1431         return path, final_field, targets, names[pos + 1:]

FieldError: Cannot resolve keyword 'key' into field. Join on 'name' not permitted.

2.

MyModel.objects.order_by('name__en-US')

/usr/local/etc/virtualenvs/gears/local/lib/python2.7/site-packages/django/db/models/sql/query.py in add_ordering(self, *ordering)
   1717                 errors.append(item)
   1718         if errors:
-> 1719             raise FieldError('Invalid order_by arguments: %s' % errors)
   1720         if ordering:
   1721             self.order_by.extend(ordering)

FieldError: Invalid order_by arguments: ['name__en-US']

Is ordering by a particular key/value pair not something the ORM supports (or plans on supporting)?

[Josh Smeaton]

This is definitely something we'd like to support, but I'm not sure if we're there just yet. To elaborate, I don't think we have support for Transforms in either order_by or values. To a user that might not mean much (since we're conceptually just spanning relationships using the double underscore __ notation), but I'm not sure the implementation was very aware of transforms when we updated order_by.

There are possibly two ways to fix this situation.

1. Make all Transforms into Expressions. That is, we need to consolidate the very close APIs of Transforms/Lookups and Expressions. We have and we are discussing this elsewhere (though I can't find where just at the moment). This is the most likely solution we'll converge on.

2. Make .values and .order_by understand the transformation/lookup API. We'll probably steer clear of this as it helps to widen the gap between transforms/expressions.

You can work around this in user code by implementing an Expression that emulates the KeyTransform transformation. Something like:

# UNTESTED!

class KeyAccessor(Func):
    function = ''
    arg_joiner = ' -> '
    output_field = TextField()

    def __init__(self, lookup, **extra):
        key = lookup.split('__')[-1]
        field = lookup.rstrip('__' + key)
        super(KeyAccessor, self).__init__(F(field), Value(key), **extra)

Model.objects.order_by(KeyAccessor('related__hstore__keyname').desc())

[David Muller]

Thanks for the very helpful reply @jarshaw. Looking forward to seeing support in a later Django release

[Marc Tamlyn]

This is heavily related to #23709

[Josh Smeaton]

Closing as a duplicate of https://code.djangoproject.com/ticket/24747