Laravel 5.7 Eloquent多表查询：获取含导演演员的电影数据

Hey there! Let's fix this issue for you. The main problems with your current query are that you're using inner joins (which exclude movies with no directors/actors like Titanic) and you aren't aggregating the multiple directors/actors into comma-separated strings. Here's how to implement this properly with Laravel Eloquent:

Step 1: Define Eloquent Models & Relationships #

First, make sure you have these models set up with the correct relationships (since a single movie can have multiple directors and actors):

Movie Model (app/Movie.php) #

namespace App;

use Illuminate\Database\Eloquent\Model;

class Movie extends Model
{
    protected $table = 'movies';
    protected $fillable = ['title', 'release', 'img_url', 'status'];

    // Relationship: A movie has many directors
    public function directors()
    {
        return $this->hasMany(MovieDirector::class, 'movie_id');
    }

    // Relationship: A movie has many actors
    public function actors()
    {
        return $this->hasMany(MovieActor::class, 'movie_id');
    }
}

MovieDirector Model (app/MovieDirector.php) #

namespace App;

use Illuminate\Database\Eloquent\Model;

class MovieDirector extends Model
{
    protected $table = 'movie_directors';
    protected $fillable = ['movie_id', 'dir_name', 'add_date'];
}

MovieActor Model (app/MovieActor.php) #

namespace App;

use Illuminate\Database\Eloquent\Model;

class MovieActor extends Model
{
    protected $table = 'movie_actors';
    protected $fillable = ['movie_id', 'act_name', 'add_date'];
}

Step 2: Implement the Query (Two Options) #

Option 1: Eloquent with Collection Processing (Clean & Flexible) #

This approach uses Eloquent's eager loading to avoid N+1 queries, then processes the results in a collection to create the comma-separated strings:

$movie_list = Movie::with(['directors', 'actors'])
    ->where('status', 1)
    ->paginate(50)
    ->map(function ($movie) {
        // Combine director names into a single string
        $movie->directors = $movie->directors->pluck('dir_name')->implode(', ');
        // Combine actor names into a single string
        $movie->actors = $movie->actors->pluck('act_name')->implode(', ');
        
        // Remove raw relationship data if you don't need it in the final output
        unset($movie->relations['directors'], $movie->relations['actors']);
        
        return $movie;
    });

Option 2: Query Builder with Database Aggregation (Better Performance) #

If you're working with large datasets, this approach handles the string concatenation directly in MySQL using GROUP_CONCAT, which is more efficient:

$movie_list = DB::table('movies')
    ->select(
        'movies.id',
        'movies.title',
        'movies.release',
        'movies.img_url',
        // Aggregate director names into a comma-separated string
        DB::raw('GROUP_CONCAT(DISTINCT movie_directors.dir_name SEPARATOR ", ") as directors'),
        // Aggregate actor names into a comma-separated string
        DB::raw('GROUP_CONCAT(DISTINCT movie_actors.act_name SEPARATOR ", ") as actors')
    )
    // Use LEFT JOIN to include movies with no directors/actors
    ->leftJoin('movie_directors', 'movies.id', '=', 'movie_directors.movie_id')
    ->leftJoin('movie_actors', 'movies.id', '=', 'movie_actors.movie_id')
    ->where('movies.status', 1)
    // Group by movie fields to avoid duplicate movie records
    ->groupBy('movies.id', 'movies.title', 'movies.release', 'movies.img_url')
    ->paginate(50);

Why Your Original Query Failed #

• Inner Joins: Your join clauses excluded movies that have no linked directors or actors (like Titanic), since inner joins only return records where matches exist in all joined tables. Using leftJoin fixes this.
• No Aggregation: You were selecting all fields from all tables, which created duplicate movie records for every director/actor linked to it. Using GROUP_CONCAT and groupBy ensures each movie only appears once with all directors/actors combined.

内容的提问来源于stack exchange，提问作者Tariqul Islam