CONCAT columns with Laravel 5 eloquent

Question

Consider me as laravel beginner

The goal is: I have two colums, now I need the id to be prefixed with the component name of same row in the table.

For Example (Working)... I have Mysql like

SELECT CONCAT(components.name," ", components.id) AS ID FROM   `components`  

And output is

ID

|TestComp 40  |  ------------- |component 41 |  ------------- |test 42      | 

I need the same in laravel eloquent way, as here Component is Model name. So i tried something like

$comp=Component::select("CONCAT('name','id') AS ID")->get() 

but it doesn't work.
I think because the syntax is wrong.
Kindly help me with the correct syntax. Using laravel Models.

Note: I made the above query, referring this as which available on internet.

User::select(DB::raw('CONCAT(last_name, first_name) AS full_name')) 

Answer 1

You need to wrap your query in DB::raw:

$comp = Component::select(DB::raw("CONCAT('name','id') AS ID"))->get() 

Also, note because you are doing your query like this, your model might behave differently, because this select removes all other fields from the select statement. So you can't read the other fields from your model without a new query. So ONLY use this for READING data and not MODIFYING data.

Also, to make it in a nice list, I suggest you modify your query to:

$comp = Component::select(DB::raw("CONCAT('name','id') AS display_name"),'id')->get()->pluck('display_name','id'); // dump output to see how it looks. dd($comp);// array key should be the arrray index, the value the concatted value. 

Answer 2

I came to this post for answers myself. The only problem for me is that the answer didn't really work for my situation. I have numerous table relationships setup and I needed one of the child objects to have a concatenated field. The DB::raw solution was too messy for me. I kept searching and found the answer I needed and feel it's an easier solution.

Instead of DB::raw, I would suggest trying an Eloquent Accessor. Accessors allow you to retrieve model attributes AND to create new ones that are not created by the original model.

For instance, let's say I have a basic USER_PROFILE table. It contains id, first_name, last_name. I have the need to CONCAT the two name attributes to return their user's full name. In the USER_PROFILE Model I created php artisan make:model UserProfile, I would place the following:

class UserProfile extends Model {    /**    * Get the user's full concatenated name.    * -- Must postfix the word 'Attribute' to the function name    *    * @return string    */    public function getFullnameAttribute()   {       return "{$this->first_name} {$this->last_name}";   }  } 

From here, when I make any eloquent calls, I now have access to that additional attribute accessor.

| id | first_name | last_name | ------------------------------- | 1  | John       | Doe       |   $user = App\UserProfile::first();   $user->first_name;   /** John **/ $user->fullname;    /** John Doe **/ 

I will say that I did run into one issue though. That was trying to create a modified attribute with the same name, like in your example (id, ID). I can modify the id value itself, but because I declared the same name, it appears to only allow access to that field value and no other field.

Others have said they can do it, but I was unable to solve this questions EXACT problem.