How to make composite/multiple primary key in Laravel >= 5.6?

Question

I have a many-to-many relationship created between USERS and COURSES. The models and migrations are made as follows.

User Model:

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

Course Model:

public function users()
    {
        return $this->belongsToMany(User::class, 'course_user', 'course_id', 'user_id')->withTimeStamps();
    }

The problem is I cannot find a way to implement a composite primary key into my migration file or pivot table in Laravel like this,

Schema::create('course_user', function (Blueprint $table) {
        $table->increments('id');
        $table->timestamps();

        $table->integer('course_id')->unsigned()->index()->primary();
        $table->foreign('course_id')->references('id')->on('courses');

        $table->integer('user_id')->unsigned()->index()->primary();
        $table->foreign('user_id')->references('id')->on('users');
    });

After coding the above, when I call php artisan migrate:refresh an error appears showing multiple primary keys performed in the linked table. So I did a research on this matter and found out that ELOQUENT does not support composite primary keys as stated in this forum: link to the forum

Is there any other way I can go around this? I have somehow managed to write a piece of code to detect a existing entries in the pivot table or the many-to-many relationship, inside the controller and avoid new duplicate entries.

score 0 · Answer 1 · answered Aug 31 '21 at 22:46

To counter attack this problem I wrote a piece code in the controller to detect an existing tuple in the pivot table as follows. NOTE: I have used Resources to retrieve data, you can avoid the Resource functions and use the normal method also.

The userId and courseId are passed as parameters in the request:

public function index(Request $request) {
        $target = new AcademicPaymentResource(User::with('courses')->findOrFail($request['userId']));
        if ($target != null) 
        {
            foreach ($target as $query){
                // search for existing tuples
                foreach ($query->courses as $subQuery => $key){
                    // Check if courseId in existing tuple is equal to the courseId of request
                    if ( $query->courses[$subQuery]->pivot->course_id == $request['courseId']){
                        
                        // Do anything here..
                        // display data to test if it works
                        return $query->courses[$subQuery];
                    }
                    
                }

                // update the pivot table if the tuple doesnt exist
                $user = User::findOrFail($request['userId']);
                $user->courses()->attach($request['courseId']);
                
                // read tuple data again to display if it works
                $target = new AcademicPaymentResource(User::with('courses')->findOrFail($request['userId']));
                return $target;
            }
        }
    }

This method actually works fine, since I have used it flawlessly so far. But if there is any other proper method please don't hesitate to answer..

score 0 · Answer 2 · answered Apr 27 '23 at 16:10

0

for people who are still landing here:

$table->primary(['user_id', 'role_id']); // Composite primary key

answered Apr 27 '23 at 16:10

AJ Zack

205
1
8

How to make composite/multiple primary key in Laravel >= 5.6?

2 Answers2