XF 2.1 Using finder and ordering based on count from related table

[asprin]

I've two tables:

FC:Teams TeamID TeamName 14 ABC 15 DEF

FC:Members PlayerID TeamID PlayerName 1 14 A 2 14 B 3 14 C 4 15 D

A TO_MANY relationship is defined on FC:Teams where FC:Teams can have multiple FC:Members.

Now if I want to use the finder system to fetch all rows from FC:Teams but order DESC by number of members present in it, how would I go about it?

Something like this:

$finder = $this->finder('FC:Teams')->setDefaultOrder('<count of members in the team>', 'DESC');

return $finder;

There is the option of using raw SQL query to fetch the data, but then I'll lose the in-built pagination feature used on the finder object (limitByPage()) and I'll have to take care of all the offsets, page numbers etc.

Is this achievable?

 

[TickTackk]

You should create a new column named member_count, add index to it and then order by it. Of course you would need to update it whenever a member is added/removed.

 

[asprin]

You should create a new column named member_count, add index to it and then order by it. Of course you would need to update it whenever a member is added/removed.

That thought had crossed my mind, but I think I'll keep it as a last resort.

 

[Lukas W.]

You don't want to order by aggregate of a joined table on a query that's performed regularly. It's horrible for performance, especially as the tables grow. A column on the original table that's also an index is very much preferred to solve this.

 

[asprin]

Alright then. Seems like the better approach so I will take the new column-index route.