Can LaravelSchema::getColumnListing() return columns in order?

Question

I would like to list all of the columns in a given table, Schema::getColumnListing() does a great job of this however it is returning the columns alphabetically rather than the order they are created in, is there a way to change this?

Here is an example on a standard users table:

Table Structure:

+---+-------------------+-----------------+
| 1 | id                | bigint unsigned |
+---+-------------------+-----------------+
| 2 | name              | varchar(255)    |
+---+-------------------+-----------------+
| 3 | email             | varchar(255)    |
+---+-------------------+-----------------+
| 4 | email_verified_at | timestamp       |
+---+-------------------+-----------------+
| 5 | password          | varchar(255)    |
+---+-------------------+-----------------+
| 6 | remember_token    | varchar(100)    |
+---+-------------------+-----------------+
| 7 | created_at        | timestamp       |
+---+-------------------+-----------------+
| 8 | updated_at        | timestamp       |
+---+-------------------+-----------------+

Test Code:

$model = new \App\Models\User;
$table = $model->getTable();
$columns = Schema::getColumnListing($table);
dd($columns);

Output:

^ array:8 [▼
  0 => "created_at"
  1 => "email"
  2 => "email_verified_at"
  3 => "id"
  4 => "name"
  5 => "password"
  6 => "remember_token"
  7 => "updated_at"
]

Desired Output:

^ array:8 [▼
  0 => "id"
  1 => "name"
  2 => "email"
  3 => "email_verified_at"
  4 => "password"
  5 => "remember_token"
  6 => "created_at"
  7 => "updated_at"
]

user_3163116 · Answer

You can use model attributes.

$item = Model::where('id', 1)->first();
$attributes = array_keys($item->getOriginal());
// or
$attributes = array_keys($item->getAttributes());
dd($attributes);

user_972235 · Answer

Haven't looked at the implementation of it, but if it's not returning the columns in order, why not try raw mysql?

SHOW COLUMNS
FROM users;

Use that within a DB::select() query and get the results.

Full query: $columns = \DB::select('SHOW COLUMNS FROM users');

If you want to keep it dynamic, use $columns = \DB::select('SHOW COLUMNS FROM ?', [$table]);

Note: Code is untested.

user_2271389 · Answer

This worked for me:

$columnObjects = $this->db::select("SELECT column_name FROM information_schema.columns WHERE table_name = 'table_name' ORDER BY ordinal_position");

This returns an array of column names as stdClasses, which have to be converted to an array of strings:

$columns = [];
foreach ($columnObjects as $key => $value) {
    if (!empty($value->COLUMN_NAME)) {
        $columns[] = $value->COLUMN_NAME;
    }
}

Note that COLUMN_NAME is all capital case.

user_6522726 · Answer

This occurs because Laravel doing this query behind:


    select column_name AS column_name from information_schema.columns where table_schema = ? and table_name = ?

From mysql 8 version, above query return in alphabetical order.

So, I solved doing this:


    $db_columns = DB::select('SHOW COLUMNS FROM '. $table);
    $fields_list = array_map(function($db_column) {
        return $db_column->Field;
    }, $db_columns);
    
    // dd($fields_list);

(Note: align DB versions between DEV and PROD)

Can LaravelSchema::getColumnListing() return columns in order?

4 Answers