How to order by a count of relations on a pivot table - Laravel 4 / Eloquent

Question

I have a table of videos, a table of users and a pivot table of "favourites" (people who favourited the videos). I want to order the results of the query ranking by the most favourited first.

I will define the relevant fields of the tables and models below.

Videos Table: id, title

Users Table: id

Favourites Table: id, video_id, user_id

Videos Model:

public function favourited()
{

    return $this->belongsToMany('User', 'favourites', 'video_id', 'user_id'); 

}

This is an sql example of what I am trying to do with Eloquent

SELECT
  videos.title,
  COUNT(favourites.id) AS FavouriteCount
FROM videos
LEFT JOIN favourites ON videos.id = favourites.video_id
GROUP BY videos.id
ORDER BY FavouriteCount desc

Answer 1

DB::table('videos')
->leftJoin('favourites', 'videos.id', '=', 'favourites.video_id')
->select('videos.title',DB::raw('count(favourites.id) as FavouriteCount'))
->groupBy('videos.id')
->orderBy('FavouriteCount', 'desc')
->get();

For more information see here http://laravel.com/docs/4.2/queries