Repository navigation
Sorting on number of reversed FK #566
Description
Discussed in #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 :)
Activity
Dave-Lopper commentedon Feb 22, 2022
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 commentedon Feb 22, 2022
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:
Property_fields are only present in python, the SQL server is not aware of them hence you cannot order/select property fields.