Sorting on number of reversed FK

[Dave-Lopper]

Discussed in https://github.com/collerek/ormar/discussions/563

^{Originally posted by Dave-Lopper February 7, 2022} Hello!

I must be missing something basic, have been reading the docs for a long while, but I cannot find an answer to my question, it seems to be a basic use-case, which makes me think I most likely missed something:

I would like to sort a model (order_by) by the number of child relation, example:

class User(ormar.Model):
    class Meta:
        tablename = "users"
        metadata = metadata
        database = DB

    user_id: int = ormar.Integer(primary_key=True)
    customer: str = ormar.ForeignKey(Customer, name="customer_key")
    plan: str = ormar.ForeignKey(Plan, name="plan_id")

    first_name: Optional[str] = ormar.String(max_length=100, nullable=True)
    last_name: Optional[str] = ormar.String(max_length=100, nullable=True)
    ...

class Task(ormar.Model):
    class Meta:
        tablename = "tasks"
        metadata = metadata
        database = DB

    task_id: int = ormar.Integer(primary_key=True)
    user: int = ormar.ForeignKey(User, name="user_id", related_name="tasks")
    customer: str = ormar.ForeignKey(
        Customer, skip_reverse=True, name="customer_key"
    )

    rating: Optional[float] = ormar.Float(nullable=True)
    ...

I am having this kind of setup, and I would like to sort my users (before pagination) on their number of tasks, I have tried several things but haven't got it working so far, what am I missing please? Tried:

User.objects.select_related("tasks").order_by(len(User.tasks))
User.objects.select_related("tasks").order_by(User.tasks.count())
User.objects.select_related("tasks").order_by("tasks__count")
User.objects.select_related("tasks").order_by("tasks__len")

...

I also tried with a @property_field on my User class

@ormar.property_field
def task_count(self):
    return len(self.tasks)

But I didn't manage to sort on that property_field either

Please help :)

[Dave-Lopper]

Any update on this? I am sorry to follow-up, I have been digging the docs, trying what I would have done under SqlAlchemy, no success so far :/

[collerek]

Hi, sorry for the late answer. Unfortunately, right now it's not supported to order by aggregated functions, it can be an enhancement.

Right now you have to reorder them in python if you select all. If you paginate or want to limit the number of returned objects your best call is to issue a raw query to get the ids and then limit the ormar query to those ids, something like:

ids = User.Meta.database.fetch_all("select user_id from users u left join tasks t on t.user_id=u.user_id order by count(t.task_id) desc limit 10")
users = User.objects.select_related("tasks").filter(user_id__in=ids).all()

Property_fields are only present in python, the SQL server is not aware of them hence you cannot order/select property fields.