Column already exists error

[RemiHin]

I get an error when migrating

error:

 SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name

migration:

Schema::createMergeViewWithoutDuplicates(
            'movie_recommended_view',
            [(new Movie())->recommended(), (new Movie)->recommendedFor()]
        );

relationships:

    public function recommended(): BelongsToMany
    {
        return $this->belongsToMany(static::class, 'movie_recommended', 'movie_id', 'recommended_id' ,'tmdb_id' , 'tmdb_id');
    }

    public function recommendedFor(): BelongsToMany
    {
        return $this->belongsToMany(static::class, 'movie_recommended', 'recommended_id', 'movie_id' ,'tmdb_id' , 'tmdb_id');
    }

[staudenmeir]

Hi @RemiHin, Please share the whole error message.

[RemiHin]

   Illuminate\Database\QueryException 

  SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'adult' (Connection: mysql, SQL: create view `recommended_movie_view` as (select `movies`.*, (select count(*) from `likes` where `movies`.`id` = `likes`.`likeable_id` and `likes`.`likeable_type` = 'App\\Models\\Movie') as `likes_count`, `movies`.`adult`, `movies`.`backdrop_path`, `movies`.`budget`, `movies`.`created_at`, `movies`.`homepage`, `movies`.`id`, `movies`.`imdb_id`, `movies`.`imdb_rating`, `movies`.`original_language`, `movies`.`original_title`, `movies`.`overview`, `movies`.`popularity`, `movies`.`poster_path`, `movies`.`release_date`, `movies`.`revenue`, `movies`.`runtime`, `movies`.`status`, `movies`.`tagline`, `movies`.`title`, `movies`.`tmdb_id`, `movies`.`trending`, `movies`.`updated_at`, `movies`.`vote_average`, `movies`.`vote_count`, `movie_recommended`.`movie_id` as laravel_foreign_key, 'App\\Models\\Movie' as laravel_model, '' as laravel_placeholders, '' as laravel_with from `movies` inner join `movie_recommended` on `movies`.`tmdb_id` = `movie_recommended`.`recommended_id`) union (select `movies`.*, (select count(*) from `likes` where `movies`.`id` = `likes`.`likeable_id` and `likes`.`likeable_type` = 'App\\Models\\Movie') as `likes_count`, `movies`.`adult`, `movies`.`backdrop_path`, `movies`.`budget`, `movies`.`created_at`, `movies`.`homepage`, `movies`.`id`, `movies`.`imdb_id`, `movies`.`imdb_rating`, `movies`.`original_language`, `movies`.`original_title`, `movies`.`overview`, `movies`.`popularity`, `movies`.`poster_path`, `movies`.`release_date`, `movies`.`revenue`, `movies`.`runtime`, `movies`.`status`, `movies`.`tagline`, `movies`.`title`, `movies`.`tmdb_id`, `movies`.`trending`, `movies`.`updated_at`, `movies`.`vote_average`, `movies`.`vote_count`, `movie_recommended`.`recommended_id` as laravel_foreign_key, 'App\\Models\\Movie' as laravel_model, '' as laravel_placeholders, '' as laravel_with from `movies` inner join `movie_recommended` on `movies`.`tmdb_id` = `movie_recommended`.`movie_id`))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:801
    797▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    798▕                 );
    799▕             }
    800▕ 
  ➜ 801▕             throw new QueryException(
    802▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    803▕             );
    804▕         }
    805▕     }

      +9 vendor frames 
  10  database/migrations/2023_10_04_151141_create_merged_recommended_movies_view.php:14
      Illuminate\Support\Facades\Facade::__callStatic("createMergeViewWithoutDuplicates")

      +24 vendor frames 
  35  artisan:35
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

[RemiHin]

@staudenmeir anything else you need to be able to get to the bottom of this?

[staudenmeir]

Please also share the whole Video model.

[RemiHin]

Are you talking about the Movie model? There is no Video model

<?php

namespace App\Models;

use App\Models\Pivots\MoviePerson;
use App\Models\Pivots\MovieUser;
use App\Models\Traits\HasLikes;
use App\Models\Traits\HasReviews;
use App\Models\Traits\HasUrl;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Staudenmeir\LaravelMergedRelations\Eloquent\HasMergedRelationships;

class Movie extends Model
{
    use HasFactory;
    use HasLikes;
    use HasReviews;
    use HasUrl;
    use HasMergedRelationships;

    protected $guarded = [];

    protected $withCount = ['likes'];

    public function scopeForApi($query, $user)
    {
        return $query
            ->with([
                'watchedBy' => fn($query) => $query->without('movie'),
                'watchlistedBy' => fn($query) => $query->without('movie'),
                'likes' => fn($query) => $query->where('user_id', $user->id),
                'movieUsers' => fn($query) => $query->where('user_id', $user->id),
                'reviews' => function ($query) use ($user) {
                    $query->where('user_id', $user->id)->without('reviewable')->with('comments', fn($query) => $query->without('commentable')->where('user_id', $user->id));
                },
                'genres',
                'recommended',
            ]);
    }

    public function scopeWithCastAndCrew($query)
    {
        return $query
            ->with([
                'castMoviePerson',
                'crewMoviePerson'
            ]);
    }

    public function genres(): BelongsToMany
    {
        return $this->belongsToMany(Genre::class, 'genre_movie', 'movie_id', 'genre_id', null, 'tmdb_id');
    }

    public function castMoviePerson(): HasMany
    {
        return $this->hasMany(MoviePerson::class)
            ->whereRole('main_cast')
            ->orderBy('order')
            ->with('person');
    }

    public function crewMoviePerson(): HasMany
    {
        return $this->hasMany(MoviePerson::class)
            ->whereRole('crew')
            ->orderBy('order')
            ->with('person');
    }

    public function cast(): BelongsToMany
    {
        return $this->belongsToMany(Person::class, 'movie_person')
            ->using(MoviePerson::class)
            ->withPivot('movie_person.id');
    }

    public function getCast()
    {
        return MoviePerson::where('movie_id', $this->id)
            ->where('role', 'main_cast')
            ->orderBy('order');
    }

    public function getCrew()
    {
        return MoviePerson::where('movie_id', $this->id)
            ->where('role', 'crew')
            ->orderBy('order');
    }

    public function crew(): BelongsToMany
    {
        return $this->belongsToMany(Person::class, 'movie_person')
            ->using(MoviePerson::class)
            ->withPivot('id');
    }

    public function movieUsers(): HasMany
    {
        return $this->hasMany(MovieUser::class);
    }

    public function watchedBy(): HasMany
    {
        return $this->hasMany(MovieUser::class)->where('watched', true);
    }

    public function watchlistedBy(): HasMany
    {
        return $this->hasMany(MovieUser::class)->where('watchlist', true);
    }

    public function recommended(): BelongsToMany
    {
        return $this->belongsToMany(Movie::class, 'movie_recommended', 'movie_id', 'recommended_id' ,'tmdb_id' , 'tmdb_id');
    }

    public function recommendedFor(): BelongsToMany
    {
        return $this->belongsToMany(Movie::class, 'movie_recommended', 'recommended_id', 'movie_id' ,'tmdb_id' , 'tmdb_id');
    }

    public function recommendedMovies()
    {
        return $this->mergedRelationWithModel(Movie::class, 'movie_recommended_view');
    }
}

[staudenmeir]

Thanks. Yeah, not sure why I wrote Video.

Please share the migrations/structure of movies and movie_recommended.

[RemiHin]

Sorry for the late response. Hadn't noticed you replyed:

migration for movies

Schema::create('movies', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('tmdb_id')->unique();
            $table->string('imdb_id')->nullable();
            $table->boolean('adult')->default(false);
            $table->string('backdrop_path')->nullable();
            $table->string('budget')->nullable();
            $table->text('homepage')->nullable();
            $table->string('original_language')->nullable();
            $table->string('original_title')->nullable();
            $table->string('tagline')->nullable();
            $table->string('title')->nullable();
            $table->text('overview')->nullable();
            $table->string('popularity')->nullable();
            $table->string('poster_path')->nullable();
            $table->string('release_date')->nullable();
            $table->string('revenue')->nullable();
            $table->string('runtime')->nullable();
            $table->string('status')->nullable();
            $table->string('vote_average')->nullable();
            $table->string('vote_count')->nullable();
            $table->timestamps();
        });

migration for movie_recommended:

Schema::create('movie_recommended', function (Blueprint $table) {
            $table->foreignIdFor(Movie::class, 'movie_id')->constrained('movies', 'tmdb_id')->cascadeOnDelete();
            $table->foreignIdFor(Movie::class, 'recommended_id')->constrained('movies', 'tmdb_id')->cascadeOnDelete();
        });

[staudenmeir]

The issue is caused by$withCount which isn't supported (yet).

However, you can make it work for your view with a workaround:

$recommended = (new Movie())->recommended();
$recommended->getQuery()->getQuery()->columns = array_slice($recommended->getQuery()->getQuery()->columns, 1);

$recommendedFor = (new Movie())->recommendedFor();
$recommendedFor->getQuery()->getQuery()->columns = array_slice($recommendedFor->getQuery()->getQuery()->columns, 1);

Schema::createMergeViewWithoutDuplicates(
    'movie_recommended_view',
    [$recommended, $recommendedFor]
);

[RemiHin]

Fantastic! Thank fixed it. Thanks!