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-&gt;getRestrictedIds();
    if (empty($restrictedIdList)) {
        $restrictedIdList = array(0);
    }

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


This is for Writers:

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

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

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


As you can see the only difference is the FilterBy&lt;filter type join&gt;WithInnerJoin that I have created within my model.

Here are the joins of the model:

public function scopeFilterByWriterWithInnerJoin($query,$writerName)
{
    $query-&gt;join('movie_writers as mw', 'mw.movie_id', '=', 'movie.id')
        -&gt;join('writers as w',function($join) use ($writerName){
            $join-&gt;on('w.id', '=', 'mw.writer_id')
                -&gt;where('w.name','like','%'.$writerName.'%');
        });
}

public function scopeFilterByActorWithInnerJoin($query,$actorName)
{
    $query-&gt;join('movie_actors as ma', 'ma.movie_id', '=', 'movie.id')
        -&gt;join('actors as a',function($join) use ($actorName){
            $join-&gt;on('a.id', '=', 'ma.actor_id')
                -&gt;where('a.name','like','%'.$actorName.'%');
        });
}


I will have to do the same for producers, directors, etc.

Is there a more efficient way to do this?
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-&gt;belongsToMany('Writer');
    }

    function actors()
    {
        return $this-&gt;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')
            -&gt;join('writers as w', 'w.id', '=', 'mw.writer_id')
            -&gt;join('movie_actors as ma', 'ma.movie_id', '=', 'movie.id')
            -&gt;join('actors as a', 'a.id', '=', 'mw.actor_id')
            -&gt;where('movie.status','=','active')
            -&gt;select('movie.id', 'movie.name')
            -&gt;where($k, 'like', "%$v%")
            -&gt;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-&gt;getBy('writers.name', 'Matthew Weiner')
                -&gt;whereNotIn('movie.id', $this-&gt;restricted())
                -&gt;take($limit)
                -&gt;skip($offset)
                -&gt;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-&gt;restrictedBy(Builder $builder, $restrictedList) that also returns a Builder object.