CODEWITHSUNDEEP
phpmysqllaraveleloquent
user1392902
user1392902

Reputation:

Laravel Eloquent increment a column based on a relation

I currently have a table of image links associate with another model as follows

+----+-------------+----------------+
| id | property_id |      url       |
+----+-------------+----------------+
|  1 |           2 | /example.jpg   |
|  2 |           7 | /example-2.jpg |
|  3 |           5 | /example-3.jpg |
+----+-------------+----------------+

I would like to add a column to the table to order the images as follows

+----+-------------+----------------+------------+
| id | property_id |      url       | sort_order |
+----+-------------+----------------+------------+
|  1 |           2 | /example.jpg   |          1 |
|  2 |           7 | /example-2.jpg |          1 |
|  3 |           2 | /example-3.jpg |          2 |
+----+-------------+----------------+------------+

Is there way to make a database level constraint (i.e. put it in the migration) that the sort_order value auto_increments but is dependent on the propery_id value, such that the sort_order value is essentially an index with each property_id having it's own index?

Upvotes: 3

Views: 1122

Answers (1)

Axalix
Axalix

Reputation: 2871

<table_name> should be replaced with your table name.

The key is in this line

\DB::unprepared('CREATE TRIGGER <table_name>_sort_order BEFORE INSERT ON <table_name> FOR EACH ROW SET NEW.sort_order = (SELECT IFNULL(MAX(sort_order), 0) FROM <table_name> WHERE property_id = NEW.property_id) + 1;');

<?php

use Illuminate\Database\Migrations\Migration;

class AddSortOrderTo<table_name_camelized> extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table(<table_name>, function(Blueprint $table) {
            $table->unsignedInteger('sort_order')->default(0);
        });

        \DB::unprepared('CREATE TRIGGER <table_name>_sort_order BEFORE INSERT ON <table_name> FOR EACH ROW SET NEW.sort_order = (SELECT IFNULL(MAX(sort_order), 0) FROM <table_name> WHERE property_id = NEW.property_id) + 1;');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        \DB::statement('DROP TRIGGER <table_name>_sort_order');
        
        Schema::table(<table_name>, function (Blueprint $table) {
            $table->dropColumn('sort_order');
        });
    }
}

Upvotes: 1

Related Questions