How to get all Movies that are playing on a MovieTheater?

Question

I have the following eloquent entities:

• Movie
• MovieSession
• MovieTheater
• MovieTheaterRoom

Table structure:

Schema::create('movies', function (Blueprint $table) {
    $table->increments('id');
});

Schema::create('movie_theaters', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name')->index();
    $table->string('slug')->index()->unique();
    $table->string('description')->index()->nullable();
    $table->longText('content')->nullable();

    $table->unsignedInteger('picture_id')->nullable();
    $table->foreign('picture_id')
         ->references('id')
         ->on('media');

    $table->boolean('activated')->default(true);

    $table->timestamps();
});

Schema::create('movie_theater_rooms', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name')->index();
    $table->string('description')->index()->nullable();
    $table->longText('content')->nullable();

    $table->boolean('activated')->default(true);

    $table->unsignedInteger('movie_theater_id');
    $table->foreign('movie_theater_id')
        ->references('id')
        ->on('movie_theaters')
        ->onDelete('cascade');

    $table->unsignedInteger('picture_id')->nullable();
    $table->foreign('picture_id')
        ->references('id')
        ->on('media');

    $table->timestamps();
});

Schema::create('movie_sessions', function (Blueprint $table) {
    $table->increments('id');

    $table->dateTime('starts_in');
    $table->dateTime('ends_in');

    $table->unsignedInteger('movie_theater_room_id');
    $table->foreign('movie_theater_room_id')
        ->references('id')
        ->on('movie_theater_rooms')
        ->onDelete('cascade');

    $table->unsignedInteger('movie_id');
    $table->foreign('movie_id')
        ->references('id')
        ->on('movies')
        ->onDelete('cascade');

    $table->boolean('activated')->default(true);

    $table->timestamps();
});

where a MovieTheater hasMany MovieTheaterRooms, each MovieTheaterRoom hasMany MovieSessions and each MovieSession belongsTo a Movie.

What I'm trying, is to get all Movies that are playing today on a MovieTheater by its $movieTheaterId, but since it is a long relation, I'm unable of retrieve such collection.

This is what I'have tried:

public function scopeGetMoviesShowingTodayOnMovieTheater($movieTheaterId)
{
    return Movie::whereHas('sessions', function ($query) use ($movieTheaterId) {
        // $query->whereDate('starts_in', Carbon::today());

        $query->whereHas('movieTheaterRoom', function ($query) use ($movieTheaterId) {
            $query->where('movie_theater_id', $movieTheaterId);
        });
    });
}

When calling App\Models\Movie::getMoviesShowingTodayOnMovieTheater(1)->get() this is what I got:

PHP Recoverable fatal error: Object of class Illuminate/Database/Eloquent/Builder could not be converted to string in .../vendor/illuminate/support/Str.php on line 338

I have also tried to use this package staudenmeir/eloquent-has-many-deep like this:

class MovieTheater extends Model {
    use HasRelationships;

    public function movies()
    {
        return $this->hasManyDeep(Movie::class, [MovieSession::class, MovieTheaterRoom::class]);
    }
}

And when calling App\Models\MovieTheater::find(1)->movies()->get()

This is the output:

Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'movie_sessions.movie_theater_id' in 'field list' (SQL: select movies.*, movie_sessions.movie_theater_id from movies inner join movie_theater_rooms on movie_theater_rooms.id = movies.movie_theater_room_id inner join movie_sessions on movie_sessions.id = movie_theater_rooms.movie_session_id where movie_sessions.movie_theater_id = 1)'

Where I'm wrong?

Answer 1

The exception

Object of class Illuminate/Database/Eloquent/Builder could not be converted to string in .../vendor/illuminate/support/Str.php on line 338

is thrown because you didn't provide the $query parameter in your scope definition. Your code will try to convert the movie theater id to a Builder instance, which fails.

Try to refactor your code to this:

/**
 * Scope a query to only select movies that are showing in a certain theater.
 *
 * @param  \Illuminate\Database\Eloquent\Builder  $query
 * @param  int  $movieTheaterId
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeGetMoviesShowingTodayOnMovieTheater($query, $movieTheaterId)
{
    return $query
        ->whereHas('sessions.movieTheaterRoom', function ($query) use 
        ($movieTheaterId) {
            $query->whereMovieTheaterId($movieTheaterId);
        });
}

I've also added dot notation in the whereHas statement to query a nested relation.

Answer 2

If you want to use staudenmeir/eloquent-has-many-deep you should make the order like this

class MovieTheater extends Model {
    use HasRelationships;

    public function movies()
    {
        return $this->hasManyDeep(Movie::class, [MovieTheaterRoom::class, MovieSession::class]);
    }
}

Example order: