When do I need a pivot table?

Display a favorited movie once

Use "GROUP BY rating.tmdb_id" in the SQL to only display a film once.

While that does indeed remove duplicates it doesn't seem to address the 2nd issue: how can I tell who exactly has favorited it? As it is now it just defaults to the first ID that it finds.

@benm With single query its not possible until you use some aggregate functions like group concat (etc.). SQL row consists of one set of data (by default) , i.e. a user that favourited a movie, if you wan't multiple users, there are gonna be some duplicates ... you need to handle that ...

Do you have to return the data in a single MySQL query? Seems quite heavy to me to get all that data at once. Of course, you don't have to return with a GROUP BY and instead sort and filter using standard PHP to get the favourite films for each user.

It's a little bit confusing as it sounds as though you're updating the ratings table's ratings.favorite column with a one if ANY user favourites a film. Wouldn't it make more sense to instead record the favourite film of a user in a separate cross / relational / mapping table (eg, users_favourites with a user_id and a movie_id) and then ratings.favorite could be used to give the number of favourites that particular film has received?

I think I'd approach this with a separate query to look up the users' data for films that had been favourited.

@benm Will your app always be movies? Or other types later too? If yes to the second question look at the polymorphic example in the Laravel docs for likes implementing it now can save you a huge amount of time and effort later ( https://laravel.com/docs/5.2/eloquent-relationships#polymorphic-relations ). Obviously instead of likes use favorites.

With that said, if your app will always be movies I would use a movie_user pivot table.

//Migration

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateMovieUserPivotTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('movie_user', function (Blueprint $table) {
            $table->integer('user_id')->unsigned();
            $table->integer('movie_id')->unsigned();
            $table->timestamps(); // Not required, but I use anyways
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('movie_user');
    }
}

// On user model or repo

// relationship to movies model
public function movies()
    {
        return $this->belongsToMany(Movie::class, 'movie_user', 'user_id', 'movie_id')
            ->withTimestamps(); // Overrides not required, but added for example
    }

public function addToFavorites($request) // $request object would be movie_id
    {
        if($this->checkFavorites($request))
        {
            return null;
        }
        return $this->user->movies()->attach($request->id);
    }

 public function remove($request)
    {
        if($this->checkFavorites($request))
        {
            $this->user->movies()->detach($request->id);
        }
        return null;
    }

// This check is to ensure a user can only favorite a single item, you case movies
    protected function checkFavorites($request)
    {
        return $this->user->movies->contains($request->id);
    }

// Movie model

public function users()
    {
        return $this->belongsToMany(User::class) ->withTimestamps();
    }

public function usersCount()
    {
        return $this->belongsToMany(User::class, 'movie_user', 'movie_id', 'user_id') ->withTimestamps()->count();
    }

// Queries

auth()->user()->movies will return all favorited movies by a authenticated user.
User::with('movies')->get() will return all user's favorited movies
Movie::with('users')->get(); will return all users that have favorited a movie for each movie
Movie::with('userCount')->get(); will return a count of users who favorited a movie
// Can also set up query scopes too for count if needed and/or more convenient 

Sanity check, the naming convention above is not optimal and confusing, I suggest renaming the methods to affiliate to favoriting rather then movies/users. Just down and dirty if you will.

@liamvictor Thanks for the feedback. When a user posts a review for a movie (ratings.review, where ratings.tmdb_id is attached to movies.tmdb_id) they can also select it as a favorite. I'd like to be able to list who favorited the movie in the general favorites section. The ratings table has the following structure:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user       | int(11)          | NO   |     | NULL    |                |
| tmdb_id    | varchar(255)     | NO   |     | NULL    |                |
| imdb_id    | varchar(255)     | NO   |     | NULL    |                |
| rating     | tinyint(4)       | NO   |     | NULL    |                |
| favorite   | tinyint(4)       | NO   |     | NULL    |                |
| review     | text             | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

@jekinney Thanks I will absolutely be looking into polymorphic relationships. Yes my app will always be movies and there will only be a very small userbase.