Laravel 5.1 Eloquent Query Builder Pagination breaking with Raw SQL

Question

I'm trying to solve an issue with Laravel 5.1 Eloquent Query Builder, where the Pagination is breaking the SQL when the Query Builder contains RAW Selects.

Background

I'm building a web application where a user can query a list of Profiles. The front-end has a filtering UI. The Filtering UI has an option for "contains 1 or more images" and "contains 1 or more videos".

The MySQL Database has a table for the Profiles, and a separate table for the Media items (images, videos, etc). The Media table has a Profile ID column (owner) and a media type column (representing image, video, etc).

Therefore I'm trying to count the number of media items that are match the profile id, then evaluate if the count is > 1.

There are also other filtering options that are simple WHERE statements on the Profile table (however these are working fine) - eg. gender, dob, etc...

I've added the following method to my laravel Profile model:

public function scopeMediaCounts($query, $media_type, $count = null, $operator = '=') 
{
    $media_types = array();

    switch ($media_type) {
        case 'images':
            $media_types[] = 1;
            break;
        case 'videos':
            $media_types[] = 2;
            $media_types[] = 3;
            break;
    }

    // $query to build

}

I intend to use this like so: ->mediaCounts('images', 1, '>')->mediaCounts('videos', 1, '>')

---

What I've Tried

I've tried various combinations based off the laravel documentation, stack overflow answers, google searches, and trial & error. However the closest I can get are below:

public function scopeMediaCounts($query, $media_type, $count = null, $operator = '=') 
{
    $media_types = array();

    switch ($media_type) {
        case 'images':
            $media_types[] = 1;
            break;
        case 'videos':
            $media_types[] = 2;
            $media_types[] = 3;
            break;
    }

    $types = implode(',', $media_types);
    $query->leftJoin('media', 'profiles.profile_id', '=', 'media.profile_id')
        ->selectRaw("profiles.*, count(media.profile_id) as {$media_type}Count")
          ->whereIn('media.type', $media_types)
      ->groupBy('profiles.profile_id');

    if (is_int($count))
        $query->having("{$media_type}Count", $operator, $count);

    return $query;
}

The code above breaks when there is trying to use ->mediaCounts() is used more then once.

Whoops, looks like something went wrong.

2/2
QueryException in Connection.php line 662:
SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'media' (SQL: select profiles.*, count(media.profile_id) as imagesCount, profiles.*, count(media.profile_id) as videosCount from `profiles` left join `media` on `profiles`.`profile_id` = `media`.`profile_id` left join `media` on `profiles`.`profile_id` = `media`.`profile_id` where `media`.`type` in (1) and `media`.`type` in (2, 3) group by `profiles`.`profile_id`, `profiles`.`profile_id` having `imagesCount` >= 1 and `videosCount` >= 1)

---

public function scopeMediaCounts($query, $media_type, $count = null, $operator = '=') 
{
    $media_types = array();

    switch ($media_type) {
        case 'images':
            $media_types[] = 1;
            break;
        case 'videos':
            $media_types[] = 2;
            $media_types[] = 3;
            break;
    }

    $types = implode(',', $media_types);

    $query
        ->selectRaw("{$this->table}.*, ( SELECT  COUNT(*)
            FROM    ver3_data_media           media
            WHERE   media.profile_id      = {$this->table}.profile_id
            )   AS      \"{$media_type}Count\"")
        ->having("{$media_type}Count", $operator, $count);

    return $query;
}

The example above doesn't take into account the media types, but "works" until its passed into the paginate function.

Error: ->mediaCounts('images', 1, '>')->mediaCounts('videos', 1, '>')->paginate(20)

QueryException in Connection.php line 662:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'imagesCount' in 'having clause' (SQL: select count(*) as aggregate from `profiles` having `imagesCount` >= 1 and `videosCount` >= 1)

However if I dump the SQL before it hits the Paginate, the SQL query works fine in MySQL Workbench (minus that it doesnt select media types):

select profiles.*, ( SELECT  COUNT(*)
            FROM    media           media
            WHERE   media.profile_id      = profiles.profile_id
    )       AS      imagesCount, profiles.*, ( SELECT  COUNT(*)
            FROM    media           media
            WHERE   media.profile_id      = profiles.profile_id
    )       AS      videosCount from `profiles` group by `profiles`.`profile_id`, `profiles`.`profile_id` having `imagesCount` >= 1 and `videosCount` >= 1

Really not sure how to solve Paginate breaking issue. Other stack overflow questions instruct to manually create the pagination, however the examples were laravel 4, using methods that dont seem to exist in 5.1

Please help.

Thanks, Zac