CODEWITHSUNDEEP
phpdatabaselaravel
Hao Luo
Hao Luo

Reputation: 1891

How to get database field type in Laravel?

Is there a way to get the datatype of a database table field? This would be almost like the inverse of a migration.

For example, if the migration of a users table column looks like

...
$table->integer('age')
...

Is there a function that will return integer if I specify table user and column age?

I'm not interested in a specific database implementation, (mysql_field_type()). Like Laravel's migration, it needs to be database agnostic.

Upvotes: 31

Views: 41280

Answers (11)

Mihail
Mihail

Reputation: 1651

With Laravel 10:

$columns = Illuminate\Support\Facades\Schema::getColumns('<table name here>');
$definition = current(array_filter($columns, function($column) {
    return $column['name'] === '<column name here>';
}));

result:

[
  'name' => 'id',
  'type_name' => 'bigint',
  'type' => 'bigint(20) unsigned',
  'collation' => NULL,
  'nullable' => false,
  'default' => NULL,
  'auto_increment' => true,
  'comment' => '',
]

Upvotes: 0

Gourav Sahu
Gourav Sahu

Reputation: 39

The most simplest way is :-

$data = DB::table('INFORMATION_SCHEMA.COLUMNS')
                    ->select('DATA_TYPE')
                    ->where('TABLE_NAME', 'users')
                    ->where('COLUMN_NAME', 'age')
                    ->first();

Upvotes: 2

Sᴀᴍ Onᴇᴌᴀ
Sᴀᴍ Onᴇᴌᴀ

Reputation: 8297

For Laravel 5.2 - 9.x

use Illuminate\Database\Schema\Builder::getColumnType()

DB::getSchemaBuilder()->getColumnType($tableName, $colName)

e.g.

$ageType = DB::getSchemaBuilder()->getColumnType('user', 'age')

You may need to run this command if you haven't already:

composer require doctrine/dbal

Upvotes: 21

asmmahmud
asmmahmud

Reputation: 5044

In Laravel 5+ (including 6 and 7), you can get the db table column metadata (ie type, default value etc) in the following way:

use Illuminate\Support\Facades\Schema;

For all columns:

 $columns = Schema::getConnection()->getDoctrineSchemaManager()->listTableColumns('table_name');
 $column = Schema::getConnection()->getDoctrineColumn('table_name'', 'column_name'); //For a single column:

getDoctrineSchemaManager method returns a array of \Doctrine\DBAL\Schema\Column Class Instances.

getDoctrineColumn method returns the instance of \Doctrine\DBAL\Schema\Column class.

Couple of methods from \Doctrine\DBAL\Schema\Column class:

$column->getName();
$column->getNotnull(); // returns true/false
$column->getDefault(); 
$column->getType(); 
$column->getLength();

Upvotes: 9

Hao Luo
Hao Luo

Reputation: 1891

For Laravel 4:

After digging in Laravel, this is what I got.

DB::connection()->getDoctrineColumn('users', 'age')->getType()->getName()

Upvotes: 36

Pawan Kumar
Pawan Kumar

Reputation: 762

GET All Details of fields of table

DB::select('describe table_name');

Example:-

DB::select('describe users');

Response will be like this

 Array
(
    [0] => stdClass Object
        (
            [Field] => id
            [Type] => int(11)
            [Null] => NO
            [Key] => PRI
            [Default] => 
            [Extra] => auto_increment
        )

    [1] => stdClass Object
        (
            [Field] => user_group_id
            [Type] => int(11) unsigned
            [Null] => YES
            [Key] => MUL
            [Default] => 
            [Extra] => 
        )

    [2] => stdClass Object
        (
            [Field] => username
            [Type] => varchar(100)
            [Null] => YES
            [Key] => MUL
            [Default] => 
            [Extra] => 
        )
 )

Upvotes: 9

Edgars Praulins
Edgars Praulins

Reputation: 61

Use this expression with a dial-down function to show you array details of all your table's fields:

dd(DB::select(DB::raw('SHOW FIELDS FROM tablename')));

Upvotes: 5

Tjeu Moonen
Tjeu Moonen

Reputation: 317

Within the model: laravel5.x

    $temp = $this->newQuery()->fromQuery("SHOW FIELDS FROM ".$this->getTable());

    foreach($temp as $val){
       echo 'Field: '.$val->Field;
       echo 'Type: '.$val->Type;
   }

Upvotes: 2

simhumileco
simhumileco

Reputation: 34535

Generalizing:

DB::connection()->getDoctrineColumn($tableName, $colName)
    ->getType()
    ->getName();

It works on Laravel 5.3.

Upvotes: 0

Kresimir Pendic
Kresimir Pendic

Reputation: 3614

I use this with laravel 4.1,,

$schema = \DB::getDoctrineSchemaManager();
$tables = $schema->listTables();

foreach ($tables as $table) {
    echo "<i>".$table->getName() . " </i><b>columns:</b><br>";
    foreach ($table->getColumns() as $column) {
        echo ' - ' . $column->getName() . " - " . $column->getType()->getName() . "<br>";
    }
}

or to get specific table use this: $columns = $schema->listTableColumns('user');

Upvotes: 4

fideloper
fideloper

Reputation: 12293

Short answer: Yes, that functionality exists

After scouring the code, I found that you could. Skip below to "The Solution" to see it.

Eloquent, and the Database classes, use PDO, which does not tie you a specific SQL-based database.

Therefore, you should be able to do something like this:

$pdo = DB::getPdo();

Note that the connection object can return the instance of PDO.

There are some methods like getColumnMeta, but they aren't fully supported across all drivers.

However, some googling seems to point out that the best way might be to use ANSI-standard INFORMATION_SCHEMA - using sql queries to get that information.

The solution

Lastly, Laravel includes the Doctrine library as a dependency, which does contain some schema functionality.

Sidenote: Doctrine is, in fact, included for its schema-based functionalities - Laravel doesn't use Doctrine's ORM

See here on the same connection object where we retrieved the PDO instance, we can get the doctrine connection and schema manager. You should be able to call:

$schema = DB:: getDoctrineSchemaManager();

You can then use the schema manager (docs here) to get what you're after.

Upvotes: 10

Related Questions