# How to write order function based on Dict value

**URL:** <https://discuss.tryton.org/t/how-to-write-order-function-based-on-dict-value/5737>\
**Category:** Developer\
**Created:** [December 1, 2022, 11:13am UTC](https://discuss.tryton.org/t/how-to-write-order-function-based-on-dict-value/5737 "2022-12-01T11:13:03Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\
**Post date:** [December 1, 2022, 11:13am UTC](https://discuss.tryton.org/t/how-to-write-order-function-based-on-dict-value/5737/1 "2022-12-01T11:13:03Z")

</div>

I’m walking into a bit more complex one.

I have a Function field defined as `DateTime` which gets it’s contents from a `Dict` field. It all works well, but now I want to be able to sort / order the column so the most recent date is at the top.

I get to the `information` field in the table which is the `Dict` field with the JSON data. But how do I get the specific key `start_time` out of that data?

```python
@staticmethod
def order_datetime(tables):
    pool = Pool()
    Event = pool.get('event.information.record')
    table, _ = tables[None]
    if 'event' not in tables:
        event = Event. __table__ ()
        tables['event'] = {
            None: (event, table.event == event.id),
            }
    else:
        event, _ = tables['event'][None]
    return [event.information, event.information]

```

In PostgreSQL you can add something like

```auto
select id, information->'start_time' from party_information_record order by information->'start_time' DESC;

```

but using the `information->'start_time'` in the code will fail. The same for `event.information.start_time`.

Is this kind of query supported?

---

<div class="post-metadata">

**Author:** ![ced](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/ced/32/1237_2.png) [@ced](https://discuss.tryton.org/u/ced)\
**Post date:** [December 1, 2022, 12:29pm UTC](https://discuss.tryton.org/t/how-to-write-order-function-based-on-dict-value/5737/2 "2022-12-01T12:29:16Z")

</div>

The `Database` backend provide the [`json_get` method](https://docs.tryton.org/projects/server/en/latest/ref/backend.html#trytond.backend.Database.json_get) that can be used build a SQL expression to retrieve the value of a `Dict` field.

---

<div class="post-metadata">

**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\
**Post date:** [December 1, 2022, 2:51pm UTC](https://discuss.tryton.org/t/how-to-write-order-function-based-on-dict-value/5737/3 "2022-12-01T14:51:31Z")

</div>

Thanks! That worked. Solution:

```auto
@staticmethod
def order_datetime(tables):
    pool = Pool()
    Event = pool.get('event.information.record')
    table, _ = tables[None]
    if 'event' not in tables:
        event = Event. __table__ ()
        tables['event'] = {
            None: (event, table.event == event.id),
            }
    else:
        event, _ = tables['event'][None]
    return [Transaction().database.json_get(event.information, 'start_time'),
            event.information]

```

---

<div class="post-metadata">

**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\
**Post date:** [December 1, 2022, 9:01pm UTC](https://discuss.tryton.org/t/how-to-write-order-function-based-on-dict-value/5737/4 "2022-12-01T21:01:49Z")

</div>

Hmm, I’m not there yet. I came across the weird situation that ordering on date is working, but not on time. When ordered on date, the time start from the earliest hour of that date instead of the most recent hour. So 06:00 comes before 17:00.

The thing is that in PostgreSQL the same happens and I suppose it has to do with how the `DateTime` is stored. It’s not stored like a SQL timestamp but as the JSON object used by Tryton. So the `order by` of PostgreSQL doesn’t know what to do and I think it’s more luck that it will order the way it does.

As I’m working with a `Function` field, I am thinking to replace the `DateTime` with an `Integer` and use the epoch as timestamp. In my functions I can convert that back to the date and time.

Maybe there are other options?

---

<div class="post-metadata">

**Author:** ![ced](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/ced/32/1237_2.png) [@ced](https://discuss.tryton.org/u/ced)\
**Post date:** [December 1, 2022, 9:40pm UTC](https://discuss.tryton.org/t/how-to-write-order-function-based-on-dict-value/5737/5 "2022-12-01T21:40:08Z")

</div>

JSON does support any of the datetime types so we use a custom encoding.  
So storing epoch may be a solution or you may try to rebuild the datetime with SQL but this will probably be complicated.

But in general you should not store in a Dict data that you need for other part of the code.

---

<div class="post-metadata">

**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\
**Post date:** [December 2, 2022, 10:00am UTC](https://discuss.tryton.org/t/how-to-write-order-function-based-on-dict-value/5737/6 "2022-12-02T10:00:07Z")

</div>

> [@ced](#):
>
> But in general you should not store in a Dict data that you need for other part of the code.

That wasn’t the case first. But I’m throwing everything away and create a dedicated field for it.

---

<div class="post-metadata">

**Author:** ![system](https://discuss-cdn.tryton.org/uploads/default/original/1X/c6f8ec0a40525cdcd50058c734283450a4b3d38b.png) [@system](https://discuss.tryton.org/u/system)\
**Post date:** [January 1, 2023, 10:00am UTC](https://discuss.tryton.org/t/how-to-write-order-function-based-on-dict-value/5737/7 "2023-01-01T10:00:16Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.