How to pluck multiple columns in Laravel

Question

I want to get a student's full name, but in my database, I have two different columns: first_name and last_name. I want to get both of these columns at the same time.

Controller

public function getStudentName($id) 
{
    $students = DB::table("students")->where("students_class_id", $id)
        ->pluck("first_name", "id");

    return json_encode($students);
}

Answer 1

Create custom accessor in your eloquent model like :

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

Then use it in the query like :

$students = DB::table("students")->where("students_class_id",$id)->pluck("full_name","id");

Try it with eloquent like :

Student::where("students_class_id",$id)->pluck("full_name","id")->toArray();

Answer 2

You could concat the first_name & last_name columns in the eloquent query to resolve the issue.

public function getStudentName($id) 
{
    $students = DB::table("students")
                 ->select("id", DB::raw("CONCAT(first_name, ' ', last_name) as name"))
                 ->where("students_class_id", $id)
                 ->pluck("name", "id");

    return json_encode($students);
}

Answer 3

If you want to pick multiple columns, you can use either only or except, depending on your requirement.

public function getStudentName($id) 
{
    $students = DB::table("students")->where("students_class_id", $id)->select("first_name", "id")->get();

    return json_encode($students);
}

In tinker

>>> DB::table('users')->where('id', 1)->select('name', 'email')->get()
=> Illuminate\Support\Collection {#3116
     all: [
       {#3117
         +"name": "superadmin",
         +"email": "superadmin@charmboard.com",
       },
     ],
   }
>>>

Answer 4

I have the same Situation before and i have create the model scope

/**
     * Scope a query to Pluck The Multiple Columns
     *
     * This is Used to Pluck the multiple Columns in the table based
     * on the existing query builder instance
     *
     * @author Manojkiran.A <manojkiran10031998@gmail.com>
     * @version 0.0.2
     * @param  \Illuminate\Database\Eloquent\Builder $query
     * @param string $keyColumn the columns Which is used to set the key of array
     * @param array $extraFileds the list of columns that need to plucked in the table
     * @return \Illuminate\Support\Collection
     * @throws Illuminate\Database\QueryException
     **/
    public function scopePluckMultiple($query, string $keyColumn, array $extraFileds): \Illuminate\Support\Collection
    {
        //pluck all the id based on the query builder instance class
        $keyColumnPluck = $query->pluck($keyColumn)->toArray();

        //start @deprecated because slower than foreach

        //anonymous callback method to iterate over the each fileds of table

        // $callBcakMethod = function ($eachValue) use ($query) {
        //     $eachQuery[$eachValue] = $query->pluck($eachValue)->toArray();
        //     return $eachQuery;
        // };
        //now we are collapsing the array single time to get the propered array 

        // $extraFields = Arr::collapse(array_map($callBcakMethod, $extraFileds));

        //end @deprecated because slower than foreach

        //iterating Through All Other Fileds and Plucking it each Time
        foreach ((array)$extraFileds as  $eachFiled) {
            $extraFields[$eachFiled] =   $query->pluck($eachFiled)->toArray();
        }

        //now we are done with plucking the Required Columns
        //we need to map all the values to each key

        //get all the keys of extra fileds and sets as array key or index
        $arrayKeys = array_keys($extraFields);
        //get all the extra fileds array and mapping it to eack key
        $arrayValues = array_map(
            function ($value) use ($arrayKeys) {
                return array_combine($arrayKeys, $value);
            },
            call_user_func_array('array_map', array_merge(
                array(function () {
                    return func_get_args();
                }),
                $extraFields
            ))
        );
        //now we are done with the array now Convert it to Collection
        return new \Illuminate\Support\Collection(array_combine($keyColumnPluck, $arrayValues));
    }

So Paste this in Your Model

$whereCondition = [ ['students_class_id','=',$id] ];
$collection = Student::where($whereCondition )->pluckMultiple('id',['first_name','last_name']);

dd($collection);

RESULT WILL BE

Collection {#74 ▼
  #items: array:1 [▼
    2 => array:2 [▼
      "last_name" => "Doe"
      "first_name" => "John"
    ]
  ]
}

Explaination

Arguments:

[First Argument] $query This Will be autoloded for all the model Scopes

[Second Argument] $keyColumn The Column That Hold the Id or primary key [id]

[Third Argument] $extraFileds The List of Columns needs to be plucked [first_name,last_name]

So now we got the key and value with array

So if You want to display the first name and last name in same line

You can use the foreach or map

I have tested both but foreach is faster that map

foreach ( $collection as $idField => $columnFilelds) {

    $fianArray[ $idField] = $columnFilelds[ 'first_name'].'-'.$columnFilelds[ 'last_name'];

}

So Now dd($fianArray)

array:2 [▼
  2 => "John-Doe"
  3 => "Foo-Bar"
]

Hope it helps

AND ONE MORE THING YOU CAN PLUCK MULTIPLE COLUMNS NOT ONLY THE first_name,last_name