Laravel use of concat with pluck method

Question

I am using Laravel.5.3 and below is my query

$ProjectManagers = Employees::where("designation" , 1)
->pluck(DB::raw('CONCAT(first_name," ",last_name) AS name'),'id');

which throws an error that

Illegal offset type in isset or empty

May i know if this is the correct method ?

if i dont use contact and use like

$ProjectManagers = Employees::where("designation" , 1)->pluck('first_name','id');

which is working correct and giving me result

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [8] => Punit
        )

)

Expected Result :

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [8] => Punit Gajjar
        )

)

where first name and last name are concatenated.

change `->pluck('first_name','id');` to `->pluck('name','id');? — martincarlin87, Oct 19 '16 at 13:00
@martincarlin87 : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'field list' — Punit Gajjar, Oct 19 '16 at 13:01
hmm, was just a guess off the top of my head, I imagine `name` would have to be in there somewhere since that's the alias that is used in the query. The only other thing I can think of is `$ProjectManagers = Employees::select([DB::raw("CONCAT(first_name," ",last_name) AS name")])->where('designation', 1)->pluck('name', 'id');`. I haven't used pluck so not sure if you can ask for more than one column, an alternative seems to be to use `->only("id", "name")->toArray()` — martincarlin87, Oct 19 '16 at 13:05
yeah it works thanks , , correct one is `$ProjectManagers = Employees::select(DB::raw("CONCAT(first_name,' ',last_name) AS name"),'id')->where('designation', 1)->pluck('name', 'id');` — Punit Gajjar, Oct 19 '16 at 13:08

score 49 · Answer 1 · edited Nov 15 '17 at 14:47

49

The most elegant solution is to create an accessor.

Open your Employees class (model) and add an accessor function:

public function getFullNameAttribute()
{
    return $this->first_name . ' ' . $this->last_name;
}

After that, just simply use:

$ProjectManagers = Employees::where('designation', 1)->get()->pluck('full_name', 'id');

edited Nov 15 '17 at 14:47

George John

2,069
1
17
15

answered Aug 18 '17 at 11:21

Blaž Oražem

746
5
10

1

Brilliant! It's worth noting (though, maybe just my stupidity) that to access all records with the mutator you must write `Employees::all()->pluck('full_name', 'id')`. Notice the `all()`. I negated that because I didn't have a well clause, but Laravel queries the database on the first call causing it to search the database for `full_name`. – JustCarty Jul 06 '18 at 13:35
Great use of the getFooAttribute() accessor. – Samuel Shifterovich Nov 06 '18 at 13:44
This is the best soluton – gX. May 26 '19 at 19:02
Wow! Impressive. This worked for me and I feel this is very elegant! – sgtcoder Nov 26 '19 at 19:21
This is a good solution as long as you have only a few hundreds of records in your database. For thousands of records, this takes ages. The well performing solutions are those that don't use accessors, but a concat() on the database level. – TeeJay Jun 19 '20 at 12:16

score 47 · Accepted Answer · edited Mar 30 '17 at 06:53

47

Try changing the eloquent query to:

$ProjectManagers = Employees::select(
            DB::raw("CONCAT(first_name,' ',last_name) AS name"),'id')
            ->where('designation', 1)
            ->pluck('name', 'id');

edited Mar 30 '17 at 06:53

Punit Gajjar

4,304
7
28
61

answered Oct 19 '16 at 13:35

martincarlin87

9,630
22
92
143

score 2 · Answer 3 · answered Jun 15 '20 at 04:09

if your column is nullable then you should try this

convert the NULL values with empty string by COALESCE

$ProjectManagers = Employees::select(
            DB::raw("CONCAT(COALESCE(`first_name`,''),' ',COALESCE(`last_name`,'')) AS name"),'id')
            ->where('designation', 1)
            ->pluck('name', 'id')
            ->toArray();

Hasib Kamal · Answer 4 · 2018-09-12T04:14:35.577

1

I also faced a problem like that with join query here is the solution of my problem

$studentDegree = Student::leftJoin('degree','degree.student_id','=','student.id')
->select(
      DB::raw("CONCAT(student.name,'-',degree.name) AS name_degree"),
      'student.id'
)->lists('name_degree','id');

edited Sep 12 '18 at 04:14

answered Jan 30 '18 at 08:32

Hasib Kamal

1,971
20
26

Laravel use of concat with pluck method

4 Answers4