How to write order function based on Dict value

[edbo]

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?

@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

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?

[ced (Cédric Krier)]

The Database backend provide the json_get method that can be used build a SQL expression to retrieve the value of a Dict field.

[edbo]

Thanks! That worked. Solution:

@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]

[edbo]

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?

[ced (Cédric Krier)]

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.

[edbo]

@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.