Laravel Pivot table with two relationships to the same model not working as expected

Question

I'm running a Laravel 7 application and I have 2 models in relation to each other. A User model and a Playlist.

....
App\Playlist

public function user()
{
     return $this->belongsTo(User::class);
}
....

Which is working properly and of course my Playlist migration has a foreign key on user_id

Now, I'm trying to create a scenario where there's a pivot table that allows other users to be 'invited' to a playlist. In this scenario, I want to be able to access all of the playlists which a user has been 'invited' and on a playlist, I want to be able to see all of the users that have been invited to the playlist.

I created a pivot table using standard Laravel best practices

class CreatePlaylistUserTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('playlist_user', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('playlist_id');
            $table->foreign('playlist_id')->references('id')->on('playlists')->onDelete('cascade');

            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

            $table->unique(['playlist_id', 'user_id']);

            $table->timestamps();
        });
    }

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

On my User's model I have a belongsToMany relationship

App\User

    /**
     * The Playlists associated with the user.
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function playlists()
    {
        return $this->hasMany(Playlist::class);
    }

    /**
     * The Playlists that this user has been invited to.
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsMany
     */
    public function invites()
    {
        return $this->belongsToMany(Playlist::class)->withTimestamps();
    }

and on my Playlist model I have

App\Playlist

    public function user()
    {
        return $this->belongsTo(User::class);
    }

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

After attaching users to playlists ($playlist->invites()->attach($user)) in my test's I'm able to see all of the playlists for my user ($user->invites) but when I go to my playlist to see all the users ($playlist->invites) I see an empty collection even though the record exists in my DB

I'm also checking to see if a record exists by doing something like

App\User

public function isInvitedToPlaylist($playlist) {
    return $this->invites()->where('playlist_id', $playlist->id)->exists();
}

But I keep getting an ambiguous column name : user_id error

lluminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 ambiguous column name: user_id (SQL: select exists(select * from "playlists" inner join "playlist_user" on "playlists"."id" = "playlist_user"."playlist_id" where "playlist_user"."user_id" = 3 and "playlist_id" = 1 and "user_id" = 1) as "exists")

In my head, I'm querying the "playlistUser list (ie. user_id = 3)" but it's also adding in "user_id = 1" which is the User id of the 'owner' for the playlist

Does anyone have experiencing setting up a belongsToMany relationship on a model that has 2 relationships to the same model?

Any advice would be appreciated! I think I've done as much searching and testing as I can!

Thanks!

user_10547185 · Answer

You are facing the error because you have user_id column in both playlists and playlist_user tables, so you have to mention from which table to select that column, for example where('playlists.user_id', $id)

I think adding custom select will help:

// Playlist model
public function invites()
{
    return $this->belongsToMany(User::class)
                ->select([
                    'playlist.id',
                    'playlist.user_id',
                     ...
                    'playlist_user.id',
                    'playlist_user.user_id',
                    'playlist_user.playlist_id',
                     ...
                    'users.id',
                    'users.other_column'
                ])
                ->withTimestamps();
}

Laravel Pivot table with two relationships to the same model not working as expected

1 Answers