DataMapper: Sorting results by association count (number of related objects)

Question

Given are movies and actors in an m:n relation. What I want to do is retrieve a list of actors, ordered by the number of movies they played in.

class Movie
  include DataMapper::Resource
  property :id,     Serial
  property :title,  String
  has n,   :actors, through: Resource
end

class Actor
  include DataMapper::Resource
  property :name,   String, key: true
  has n,   :movies, through: Resource
end

In pseudo-DM what I want is this:

Actor.all order: [ :movies.count ]

I found another question about sorting by a single attribute of an association but this approach only worked for real properties. Any usable solution would be helpful. Thx!

Answer 1

Taking the answer by Sean Larkin as a starting point I ended up with something like this:

actors = repository(:default).adapter.select(
  "SELECT actors.name, count(actor_movies.actor_name) AS count " +
  "FROM actors " +
  "JOIN actor_movies WHERE actors.name = actor_movies.actor_name " +
  "GROUP BY actors.name " +
  "ORDER BY count(actor_movies.actor_name) desc " +
  "LIMIT 5;"
)

=> [
  #<struct name="Samuel L. Jackson", count=66>,
  #<struct name="Michael Caine", count=64>,
  #<struct name="Robert De Niro", count=59>,
  #<struct name="Harvey Keitel", count=58>,
  #<struct name="Gene Hackman", count=57>
]