How to make sorting in DB by the number of items in the ListField?

Question

I have the next model:

class Departments(Document):
    _id = fields.ObjectIdField()
    name = fields.StringField(blank=True, null=True)
    department_id = fields.StringField(blank=True, null=True)  # Added
    list_of_users = fields.ListField(blank=True, null=True)
    list_of_workstations = fields.ListField(blank=True, null=True)

As you can see list_of_users and list_of_workstations are lists of items.

I wrote a code in Python, which takes all data from DB, put it into dict and then sorts as I need, but it works too slow.

How can I sort Departments right in the DB by the length of list_of_users or list_of_workstations or by ratio of list_of_users/list_of_workstations, something like:

departments = DepartmentStats.objects.order_by(len(list_of_users)).dsc

or

departments = DepartmentStats.objects.order_by(len(list_of_users)/len(list_of_workstations)).dsc

?

Read this documentation, probably you will find what you want. https://docs.djangoproject.com/en/dev/topics/db/aggregation/ — Umut Gunebakan, Jan 24 '17 at 07:28

Wilfried · Answer 1 · 2017-01-24T12:43:32.753

For your first request, use annotation like Umut Gunebakan told you in his comment. But I'm know sure about Count() on ListField

departments = DepartmentStats.objects.all().annotate(num_list_users=Count('list_of_users')).order_by('-num_list_users')

For a desc order by, you just need to add the sign '-' (minus). https://docs.djangoproject.com/en/1.10/ref/models/querysets/#order-by

The second request will be :

departments = DepartmentStats.objects.all().annotate(user_per_workstation=(Count('list_of_users')/Count('list_of_workstations')).order_by('-user_per_workstation')

UPDATE: (Mongoengine used)

With mongoengine you need to get item frequencies and sorted the result : Check this part of documentation - futher aggregation

list_user_freqs = DepartmentStats.objects.item_frequencies('list_of_users', normalize=True)

from operator import itemgetter
list_user_freqs_sroted = sorted(list_user_freqs.items(), key=itemgetter(1), reverse=True)

Mongoengine's QuerySets do not have such attribute as `annotate` — TitanFighter, Jan 24 '17 at 11:36
I edit my answer after some research in mongoengine documentation. Can you try it ? — Wilfried, Jan 24 '17 at 12:44

TitanFighter · Accepted Answer · 2017-02-07T13:21:48.950

If someone needs raw query:

departments = DepartmentStats._get_collection().aggregate([
    {"$project": {
        "department_id": 1,
        "name": 1,
        "list_of_users": 1,
    }},
    {"$sort": {"list_of_users": -1}},
])

and the case, when the result must be sorted by the ratio list_of_users/list_of_workstations

departments = DepartmentStats._get_collection().aggregate([
    {"$project": {
        "department_id": 1,
        "name": 1,
        "list_of_users": 1,
        "len_list_of_items": {"$divide": [{"$size": "$list_of_users"},
                                          {"$size": "$list_of_workstations"}]}
    }},
    {"$sort": {"len_list_of_items": -1}},
])

How to make sorting in DB by the number of items in the ListField?

2 Answers2