Effective usage of multiple joins in Eloquent dependent on variable

I have an Eloquent model that has multiple types of joins that is used to filter out different request to grab the data of movies e.g.: Actors, Directors, Writers, etc...

The joins are necessary because they use unconventional pivot tables. All the queries and eloquent here works and gives the desired returned data - however I'm breaking DRY (Don't Repeat Yourself) principle for each join filters I am creating within a controller.

Here are the code segments that I would like to tweak:

This is a call for all movies by actors:

public function getByActor($where, $limit, $offset)
{
    $movieData = array();

    $restrictedIdList = $this->getRestrictedIds();
    if (empty($restrictedIdList)) {
        $restrictedIdList = array(0);
    }

    $movieData = Movie::
        FilterByActorWithInnerJoin(urldecode($where['actorName']))
        ->selectRaw('movie.*')
        ->where('movie.status','=','active')
        ->whereNotIn('movie.id', $restrictedIdList)
        ->take($limit)
        ->skip($offset)
        ->get()
        ->toArray();
    return $movieData;
}

This is for Writers:

public function getByWriter($where, $limit, $offset)
{
    $movieData = array();

    $restrictedIdList = $this->getRestrictedIds();
    if (empty($restrictedIdList)) {
        $restrictedIdList = array(0);
    }

    $movieData = Movie::
        FilterByWriterWithInnerJoin(urldecode($where['writerName']))
        ->selectRaw('movie.*')
        ->where('movie.status','=','active')
        ->whereNotIn('movie.id', $restrictedIdList)
        ->take($limit)
        ->skip($offset)
        ->get()
        ->toArray();
    return $movieData;
}

As you can see the only difference is the FilterByWithInnerJoin that I have created within my model.

Here are the joins of the model:

```
public function scopeFilterByWriterWithInnerJoin($query,$writerName)
{
$query->join('movie_writers as mw', 'mw.movie_id', '=', 'movie.id')
->join('writers as w',function($join)

I don't see a way to do it with eager loads. I wonder if you could reduce it to getBy($k, $v) using distinct though?

class Movie extends Eloquent
{

function writers()
{
return $this->belongsToMany('Writer');
}

function actors()
{
return $this->belongsToMany('Actor');
}

/**
* Get a list of Movies with the matching the key-value.
*
* @param string $k Key (movie attribute) name
* @param string $v Value of the movie attribute
*
* @return \Illuminate\Database\Query\Builder
*/
function getBy($k, $v) {
return Movie::join('movie_writers as mw', 'mw.movie_id', '=', 'movie.id')
->join('writers as w', 'w.id', '=', 'mw.writer_id')
->join('movie_actors as ma', 'ma.movie_id', '=', 'movie.id')
->join('actors as a', 'a.id', '=', 'mw.actor_id')
->where('movie.status','=','active')
->select('movie.id', 'movie.name')
->where($k, 'like', "%$v%")
->distinct();
}
}


With this in place, getBy will return a Query Builder object that you can filter by restricted and limit.

$movie = new Movie();
$movies = $movie->getBy('writers.name', 'Matthew Weiner')
->whereNotIn('movie.id', $this->restricted())
->take($limit)
->skip($offset)
->get();


Then you can take the a step further by pulling this method out into a MovieRepository that implements MovieRepositoryInterface. Check out this Laracasts episode for more on that. It could have a MovieRepository->restrictedBy(Builder $builder, $restrictedList) that also returns a Builder object.

StackExchange Code Review Q#60485, answer score: 2