Laravel 8 Get Column Names from Select Query

Question

I want to get column names from a select query example:

$data = RoomModel::from('rooms as a')
    ->where('a.id', 1)
    ->where('a.isdelete', 0)
    ->join('roomclass as b', 'a.roomclass_id', 'b.id')
    ->join('nursestation as c', 'a.nursestation_id', 'c.id')
    ->select('a.id as ID', 'a.description as Description', 'b.customdesc as Room Class', 'c.customdesc as Nurse Station', 'a.isactive as Status');

Which will have a result of

array('ID','Description','Room Class', 'Nurse Station', 'Status')

This can be achieved using code igniter by using field_list:

$sql = "
    SELECT
    id as `ID`, 
    description as `Description`, 
    customdesc as `Nurse Station`,
    isactive as `Status`
    FROM mytable";

$query = $this->db->query($sql);
print_r($query->field_list());

which I can't find in Laravel.

Thanks in advance!!

If I get it right you want to retrieve filed names.then you can check this link https://stackoverflow.com/questions/37157270/how-to-select-all-column-name-from-a-table-in-laravel — simpson, Jun 30 '21 at 05:30
@simpson yeah i'd like to get the field name but not from the whole table columns itself, i just want to get the field name of my selected columns in my query. — eman_invok05, Jun 30 '21 at 06:32

Alex Black · Answer 1 · 2021-06-30T07:14:51.830

0

    $data = RoomModel::from('rooms as a')
                            ->where('a.id', 1)
                            ->where('a.isdelete', 0)
                            ->join('roomclass as b', 'a.roomclass_id', 'b.id')
                            ->join('nursestation as c', 'a.nursestation_id', 'c.id')
                            ->limit(1)->get('a.id as ID', 'a.description as Description', 'b.customdesc as Room Class', 'c.customdesc as Nurse Station', 'a.isactive as Status');

$attr = array_keys($data->getAttributes());

edited Jun 30 '21 at 07:14

answered Jun 30 '21 at 06:38

Alex Black

450
2
7

the code seems to be also getting the result not the column names, what i want is the array of the columns I selected: array('ID','Description','Room Class', 'Nurse Station', 'Status') – eman_invok05 Jun 30 '21 at 07:01
thanks for the answer but what if the result is empty ? then this would'nt work ... unlike the field_list() in CI which still gives the column names even if the result is empty – eman_invok05 Jun 30 '21 at 12:13
@eman_invok05 Laravel has Schema::getColumnListing('table_name'); See this answer https://stackoverflow.com/questions/33512184/get-laravel-models-with-all-attributes – Alex Black Jun 30 '21 at 12:36
is it just returning my selected columns or the whole columns for the table ? – eman_invok05 Jul 01 '21 at 02:38

score 0 · Answer 2 · answered Jan 31 '22 at 20:01

When using the model class to create a new query you will get an instance of Illuminate\Database\Eloquent\Builder (or a class derived from it).

To get the columns used in the select you need the query from within the builder:

$data = RoomModel::from('rooms as a')
    ...
    ->select('a.id as ID', 'a.description as Description', 'b.customdesc as Room Class', 'c.customdesc as Nurse Station', 'a.isactive as Status');

$columns = $data->getQuery()->columns;

If you are not using Eloquent, and have an instance of Illuminate\Database\Query\Builder you can get the columns property without the getQuery() call:

$data = DB::table('rooms as a')
    ...
$columns = $data->columns;

which will result in $columns being set to the array:

[
  "a.id as ID",
  "a.description as Description",
  "b.customdesc as Room Class",
  "c.customdesc as Nurse Station",
  "a.isactive as Status",
]

Unfortunately the alias are not resolved for you, even if you execute the query.

Laravel 8 Get Column Names from Select Query

2 Answers2