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. — zeratulmdq
– zeratulmdq, Commented Jan 15, 2016 at 16:36

twister · Accepted Answer · 2016-01-16 11:53:57Z

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, 2016 at 11:53

twister

3504 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create multiple column primary key in eloquent

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related