skip to Main Content

I have a table with 3 columns:
firstname
lastname
fullname

in migration:

Schema::create('owners', function (Blueprint $table) {
 $table->id(); 
$table->string('firstname',20);
 $table->string('lastname', 20);
 $table->string('fullname')->storedAs('CONCAT(firstname,lastname)');
 $table->timestamps(); 
});

the problem is that i want to change the concatenation order in the controller i tried to use db statement but it doesn’t work

-in the controller:

$owners= Owner::findOrFail($id);
$owners->update([
'firstname'=>$request['firstname'],
 'lastname' =>$request['lastname'],
 ]); 
DB::statement('UPDATE owners SET fullname AS CONCAT(lastname,firstname) STORED WHERE ID=1 ');

I don’t want to just use a simple concatenation because the user can change the firstname or the lastname and the order that’s why I used storedAs()

any ideas please?

3

Answers


  1. The storedAs method in the migration creates a generated column in mysql. The value is automatically generated from the column values of the firstname and the lastname. There’s no way you can change this via an UPDATE statement. You’d have to use an ALTER TABLE statement, which would be horrifically bad practice.

    If I were you, I’d keep full name display as a model method so you could access it by using $owner->fullNameFirstLast() or $owner->fullNameLastFirst()

    Login or Signup to reply.
  2. What you should do is create a new migration in order to change the column, the code would be something like this:

    Schema::table('owners', function (Blueprint $table) {
     $table->string('fullname')->storedAs('CONCAT(lastname,firstname)');
    });
    

    This way the column will be changed on a database level, and no need for the controller query you have added

    Login or Signup to reply.
  3. Simply try this
    1- update your migration to

    Schema::create('owners', function (Blueprint $table) {
         $table->id(); 
         $table->string('firstname',20);
         $table->string('lastname', 20);
         $table->string('fullname', 56);
         $table->timestamps(); 
        });
    

    2- in your controller

    $owners= Owner::findOrFail($id);
    $first_name = $request->firstname ?? $owners->firstname;
    $last_name = $request->lastname ?? $owners->lastname;
    $full_name = $first_name.' '.$last_name;
    
    $owners->update([
      'firstname'=>$first_name,
      'lastname' =>$last_name,
      'fullname' =>$full_name,
      ]); 
    

    You can also write it this way

    DB::statement(DB::raw("UPDATE owners SET firstname = '".$first_name."', lastname = '".$last_name."', fullname = '".$full_name."' WHERE id = $id"));
    

    And the same way for your Create function as well

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search