CODEWITHSUNDEEP
phpmysqllaravel
nhaarman
nhaarman

Reputation: 100378

Fetching column types in Laravel

I'm creating a Laravel project for which I need to dynamically retrieve column names and their types for some tables in the (MySQL) database. Currently, this is my solution:

$columnTypes = array();
$columns = Schema::getColumnListing($tableName);
foreach($columns as $columnName) {
    $columnTypes[$columnName] = DB::connection()->getDoctrineColumn($tableName, $columnName)->getType()->getName();
}

Unfortunately, this requires a lot of queries, and thus a lot of time (up to ~100ms per table).

Is there a faster way to retrieve the types of the columns?

Upvotes: 4

Views: 5519

Answers (2)

voodoo417
voodoo417

Reputation: 12101

Think, more fast will be using (for MySQL):

$tables = array[/* table list */];
foreach($tables as $table){
  $table_info_columns = DB::select( DB::raw('SHOW COLUMNS FROM "'.$table.'"'));
  
  foreach($table_info_columns as $column){
    $col_name = $column['Field'];
    $col_type = $column['Type'];
    var_dump($col_name,$col_type);
  } 
}

Upvotes: 9

Zeyad Mounir
Zeyad Mounir

Reputation: 59

Run "composer require doctrine/dbal", then write this function in the model:

public function getTableColumns() {
    $builder = $this->getConnection()->getSchemaBuilder();
    $columns = $builder->getColumnListing($this->getTable());
    $columnsWithType = collect($columns)->mapWithKeys(function ($item, $key) use ($builder) {
        $key = $builder->getColumnType($this->getTable(), $item);
        return [$item => $key];
    });
    return $columnsWithType->toArray();
}

Upvotes: 0

Related Questions