updateOrCreate - Duplicate entry

I'm using Youtube v3 API to fetch all videos from my Youtube channel and store them in mysql db. When I'm first storing the videos with updateOrCreate method, there are no issues at all.

Also, I have written a Job class which constantly runs the store method on the VideoController which on the other hand tries to update each video statistics (likes count, comments count etc.).

The problem is when updateOrCreate method notice that there is a change in the number of likes for example, it tries to CREATE NEW entry in my database instead of just updating the likes counter on the old entry.

This is causing the following error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'XXXXX' for key 'videos.videos_video_id_unique'

I have already found some solutions online and I tried to change the primary key from id to video_id on my Video model:

	protected $primaryKey = 'video_id';
    public $incrementing = false;
    protected $keyType = 'string';

This doesn't seems to be working though.

tykus

4 years ago

Level 104

The problem is when updateOrCreate method notice that there is a change in the number of likes for example

In that case, you are using updateOrCreate incorrectly. What you have not shared is the relevant code where you use updateOrCreate, that would be helpful to point you in the right direction.

The updateOrCreate method takes two arrays as arguments; the key/value pairs in the first will uniquely identify an existing record, so you really only want the video_id key and value in there, so for example:

Model::updateOrCreate(
   ['video_id' => $youtubeData['video_id']],
   $youtubeData
);

k1op0ly

4 years ago

Level 1

I am pulling the videos into $videos variable, then I am pulling the details for each video and trying to save it into my db like this:

	foreach ($videos as $video) {
            $details = Youtube::getVideoInfo($video->id->videoId);
            
            Video::updateOrCreate([
                'user_id' => auth()->id(),
                'channel_id' => $user->channel->id,
                'video_id' => $details->id,
                'title' => $details->snippet->title,
                'description' => $details->snippet->description,
                'views' => $details->statistics->viewCount,
                'likes' => $details->statistics->likeCount,
                'comments' => $details->statistics->commentCount,
                'url' => 'https://www.youtube.com/watch?v=' . $details->id,
                'thumbnail' => 'https://i.ytimg.com/vi/' . $details->id . '/sddefault.jpg',
                'published_at' => Carbon::parse($details->snippet->publishedAt),
                'created_at' => Carbon::parse($details->snippet->publishedAt)
            ]);
        }

tykus

4 years ago

Best Answer

Level 104

The video_id should be enough to uniquely identify a video (although you could also include the user_id and/or channel_id in that first array argument):

foreach ($videos as $video) {
    $details = Youtube::getVideoInfo($video->id->videoId);
        
    Video::updateOrCreate([
        'video_id' => $details->id,
    ], [
        'user_id' => auth()->id(),
        'channel_id' => $user->channel->id,
        'title' => $details->snippet->title,
        'description' => $details->snippet->description,
        'views' => $details->statistics->viewCount,
        'likes' => $details->statistics->likeCount,
        'comments' => $details->statistics->commentCount,
        'url' => 'https://www.youtube.com/watch?v=' . $details->id,
        'thumbnail' => 'https://i.ytimg.com/vi/' . $details->id . '/sddefault.jpg',
        'published_at' => Carbon::parse($details->snippet->publishedAt),
        'created_at' => Carbon::parse($details->snippet->publishedAt)
    ]);
}

k1op0ly

4 years ago

Level 1

I just saw you edited your first comment and I already did that. This solved my issue. Thank you very much!

Please or to participate in this conversation.