How to create multiple column primary key in eloquent

Question

I store Managers, Course_Type and Course Years in database.

I will name my courses depending on incrementing values such as

    Course_type    course_order      course_year

    Course_type[0]      1              2015 
    Course_type[0]      2              2015
    Course_type[0]      3              2015
    Course_type[0]      4              2015
    Course_type[0]      1              2016
    Course_type[1]      1              2015
    Course_type[2]      1              2015
    Course_type[2]      2              2015

So I create database like

Schema::create('courses', function (Blueprint $table) {
            $table->increments('id');
            $table->enum('course_type', [
                'Yos',
                'TomerABC',
                'TomerA1A2',
                'TomerB1B2',
                'TomerC1C2',
                'TomerAB',
                'TomerBC',
                'SatMatGeo',
                'SatCriRea',
                'SatCriReaMat',

            ]);

            $table->integer('added_by');
            $table->integer('start');
            $table->integer('year_code');
            $table->integer('end');
            $table->integer('course_order');
            $table->unique( array('added_by','year_code','course_order') );

and this is my controller on createprocess

$courseorder = \App\Courses::where('added_by', \Auth::id())
            ->where('year_code', $input['year_code'])->max('course_order');

$course->course_order = $courseorder + 1;

but since I look for max('course_order') in database if I delete lets say Course order 2. Next added course will be 4 instead of 3.

So my question is how can I check missing values between course_order's to fill position after removing one?

P.S a mysql solution will be faster and better than php I think.

It's a common SQL problem, to search for missing numbers. I'd go with the tally table approach. [Some info here.](http://sql-developers.blogspot.com.ar/2012/10/how-to-find-missing-identitysequence.html) — zeratulmdq, Jan 15 '16 at 16:36
http://stackoverflow.com/questions/1057389/sql-query-to-find-missing-sequence-numbers — The Alpha, Jan 15 '16 at 18:45

score 1 · Accepted Answer · answered Jan 16 '16 at 11:53

1

You can use a new table to store deleted records. When you need a new course_order number first you should check DeletedCourses table. If DeletedRecord has a matching record delete it from DeletedRecords table and use it for new course_order.

answered Jan 16 '16 at 11:53

twister

320
4
9

How to create multiple column primary key in eloquent

1 Answers1