Laravel: getting an array inside an objet bei joinig two tables

Question

I have two tables: films and actors. Thy have a n-m relation. I have a connection table actor_film with two columns film_id and actor_id.

In order to get the list of films added in the current month with the actors that play in each film I did this:

    $current_month_film = DB::table('films')
        ->join('actor_film', 'actor_film.film_id', '=', 'films.id')
        ->join('actors', 'actors.id', '=', 'actor_film.actor_id')
        ->select('films.*', 'actors.name as actors_name')
        ->whereMonth('films.created_at', '=', Carbon::now()->month)
        ->orderBy('films.created_at', 'desc')
        ->groupBy('films.name')
        ->get();

    return view('statistics.month')->withCurrent_month_film($current_month_film);

I get 40 films each of it showing just ONE actor, even if I know there are several actors in one film.

If I delete the "->groupBy('films.name')" I get 132 record: one film several times and each time one actor.

How could I get a list of actors inside each film, joinig the two tables? something like:

[
 {
   film.id: 1,
   film.name, 
   film.actors 
    {
        actor[1].name: Morgan Freeman,
        actor.[2].name: Meryl Streep
        actor [n].name: ....
    }
 },
 {
   film.id: 2,
   film.name, 
   film.actors
    {
        actor[1].name: Morgan Freeman,
        actor.[2].name: Meryl Streep
        actor [n].name: ....
    }
 }
]

Answer 1

If you want to use Eloquent and you have your relation in place then your can do it as:

Film::whereMonth('created_at', Carbon::now()->month)
    ->orderBy('created_at', 'desc')
    ->with(['actors' => function($q) {
        $q->select('name');
    }])
    ->get()

Docs

Answer 2

As you're using Laravel, you should make the use of Laravel Relationships. You should have a models like this:

class Actor extends Model
{

    public function films() {
        return $this->belongsToMany(Film::class, 'actor_film');
    }

}

class Film extends Model
{

    public function actors() {
        return $this->belongsToMany(Actor::class, 'actor_film');
    }

}

and to get the film with their respective actors, you can use with method like this:

$films = Film::with(['actors' => function($q) {
                $q->select('name');
            }])
            ->whereMonth('created_at', Carbon::now()->month)
            ->orderBy('created_at', 'desc')
            ->get();

foreach($films as $film) {
    $film_name = $film->name;
    $actors_arr = $film->actors->toArray();
}

See more about Laravel's Eloquent Relationships & Eager Loading

Hope this helps!

Answer 3

In Controller

$current_month_film = Film::select('*')->with(['actor_film','actors'])
                   ->whereMonth('films.created_at', '=', Carbon::now()->month)
                   ->orderBy('films.created_at', 'desc')
                   ->groupBy('films.name')
                   ->get();

In App\Model\Film Model

namespace App;

use Illuminate\Database\Eloquent\Model;

class Film extends Model
{
//
protected $primaryKey = "id"; //primary key
protected $table = 'film';

public function actor_film(){
    return $this->belongsTo('App\Model\ActorFilm','film_id','id');
}
public function actors(){
    return $this->hasMany('App\Model\Actors','id','actor');
 }

}