Choosing the appropriate relationship for given data and view

Hey folks. I'm banging my head to the table for quite some time now but still can't solve my problem.

The reason is, I don't fully understand how to extend the given examples of the docs for "Has many through" and "polymorphic many to many" relationships.

I will describe my issue like the laravel docs. My tables are:

cinemas
    id integer
    name string
    address string

movies
    id integer
    name
    rating

showtimes
    id integer
    cinema_id integer
    movie_id integer
    showtime timestamp

showtimes is my lookup table which brings all information together. My problem is, to handle the variation of possible showtimes for each movie and cinema. Example: A movie can be shown 3x a day in cinema A, but only 1x a day in cinema B. And each show got a different timestamp. So I need 3 rows in showtimes for cinema A and 1 row for cinema B.

Which kind of relationship would be the best choice for this case? With my current understanding, nothing seems to fit. I think the two best choices might be 'Has Many Through' or polymorphic many to many. But with the examples, I can't figure out, what the correct table design would look like.

IMPORTANT NOTE I almost forgot: This is the desired, final display of data:

Movie title
    Cinema
        timestamp
        timestamp
        timestamp
    Cinema
        timestamp
        timestamp
    Cinema
        timestamp

ex.

La La Land
    MovieTheatre
        3 pm
        6 pm
        8 pm
    MoviePalace
        5 pm
        6.30 pm
    Cinedome
        8 pm

Any kind of help is much appreciated! Thank you!

StefanVoinea

9 years ago

Level 26

Don't get stuck! It is better if you do what you know best.

I would do so :

Showtime hasOne movie hasOne cinema Movie hasMany showtimes hasMany cinema Cinema hasMany showtimes hasMany movies

and then you can call

movie->cinema()->showtimes()

1 like

SaeedPrez

9 years ago

Level 50

Hi @crslp.

If I've understood your structure correctly, all you need is a simple many to many relationship..

A Movie belongs to many Cinema
A Cinema belongs to many Movie
Showtime is the pivot table that holds additional information, such as the start time

I would stick to Laravel convention and name my tables:

movies
cinemas
cinema_movie

And I would then create a model for the pivot table called Showtime and set

protected $table = 'cinema_movie';

I would also define relationships in the Showtime model:

A Showtime belongs to a Cinema
A Showtime belongs to a Movie

That's it.. now you can do queries like..

$result = Showtime::with('movie', 'cinema')->whereHas('movie' => function($query) {
        $query->where('name', 'Top Gun');
})->get();

...which should return all showtimes (with related cinema and movie ofc) that has a relationship to a movie called Top Gun.

Please or to participate in this conversation.