CODEWITHSUNDEEP
databasedjangodjango-queryset
gtek
gtek

Reputation: 88

Django QuerySet - Multiple Order By when there is a single field per record

I am accessing an API where the number of fields can change at any time, but I must store and display the data as a table. Therefore, each record from the API is stored as a single record per field. My problem is that I am having trouble working out how I would order by multiple columns at a time. Putting all of the data into a 2D array (list of lists) before sorting is not a viable option as the number of records could be too large to feasibly hold in memory.

I've put together a simple example to explain. If anyone has an idea on how to overcome the problem, or how I could redesign my approach, I'd be very grateful.

| record_id    | field       | data        |
| 1            | x           | 2           |
| 1            | y           | 1           |
| 1            | z           | 3           |
| 2            | x           | 30          |
| 2            | y           | 42          |
| 2            | z           | 7           |
| 3            | x           | 53          |
| 3            | y           | 2           |
| 3            | z           | 7           |

If ordering by fields 'z' then 'x' (both ascending), the record order would be 1,2,3

If ordering by fields 'z' then 'y' (both ascending), the record order would be 1,3,2

I am using models in django to store and I am using QuerySets to retrieve the data. I don't have any control over the API or database from which I am originally accessing the data.

Upvotes: 1

Views: 360

Answers (1)

gtek
gtek

Reputation: 88

After a fair amount of research I realised I was going about this all wrong - I am now using an hstore field in postgres and django-hstore to utilise it, for a schema-less approach. I now have a single row per original record and I can order_by after casting the required field in an 'extra' method.

Upvotes: 1

Related Questions