Support Query result typing

[smirnov-e]

Currently

• data returned by ActiveQuery is typed if column name matches table column name.
• data returned by Query is mostly untyped.

CREATE TABLE test (
     id INTEGER GENERATED ALWAYS AS IDENTITY,
     int INTEGER,
     float FLOAT,
     int_array INT[],
     text_array TEXT[]
 );

CREATE TYPE currency_money AS
(
  value numeric(19,4),
  currency_code char(3)
);
 
 INSERT INTO test 
 ("int", "float", int_array, text_array)
VALUES (1, 2.33, ARRAY[1, 2, 3], ARRAY['test']);

Code like this

$db->createCommand('SELECT * FROM test')->queryAll();
// or like this

$db->createCommand('SELECT 1 AS id, 
1 AS int, 
2.3 AS float, 
ARRAY[1,2] AS int_array, 
ARRAY['test'] AS text_array,
ARRAY['(0,USD)'::currency_money] as money_array
')->queryAll();

will return

[
    [
        'id' => 1,
        'int' => 1,
        'float' => '2.33', // type lost
        'int_array' => '{1,2,3}', // type lost
        'text_array' => '{test}', // type lost
        'money_array' => '{"(0.0000,USD)"}', // type lost
    ],
]

So currently in Yii3 and Yii2 to get typed data you have to extensively map values and manually control returned types.

// like this
$price = (new Query($db))->select(['price'])->from('product')->scalar();
$pricePhpType = $tableSchema->getColumn('price')->phpTypecast($price);
// or like this
array_map($rows, function(array $row) {
     $row['int_array'] = (new ArrayParser())->parse($row['int_array']));
     return $row;
})

Other way is to use PDOStatement::getColumnMeta which returns rather fine type information:

ARRAY(
    [pgsql:oid] => 16413
    [pgsql:table_oid] => 0
    [native_type] => _currency_money
    [pdo_type] => 2
    [name] => money_array
    [len] => -1
    [precision] => -1
)

[native_type] => int4 // int 
[native_type] => numeric // int 
[native_type] => _int4 // array of INT.  "_<typename>" means array of <typename>
[native_type] => _text // array of strings
[native_type] => _currency_money // array of currency_money (custom type)

Moreover, there is [pgsql:oid] => <some int> that refers to type information stored in pg_type table (SELECT * FROM pg_type WHERE oid = <some int>), so in theory it's possible to correctly typecast any possible type.

Main catch seems to be that it should be optional.

P.S. I can try to make pull request.

[Tigrov]

Type Information is stored in ColumnSchema.

Command and Query return raw values

$price = (new Query($db))->select(['price'])->from('product')->scalar();

To get typed values you need to cast the values after retrieving from DB

$pricePhpType = $tableSchema->getColumn('price')->phpTypecast($price);

[smirnov-e]

@Tigrov that way has several drawbacks:

• your should manually define type mappings. That's error prone in several ways: (1) when you write mapping (2) when you change column type in database.
• column with expected type should exists somewhere in the database. You can't typecast result of array_agg(currency_money) if your tables have only "scalar" column currency_money.

[Tigrov]

Depends of how do you use this.

E.g. ActiveRecord casts values after retrieving them from DB
https://github.com/yiisoft/active-record/blob/master/src/ActiveRecord.php#L259

[Tigrov]

If question about ActiveQuery perhaps better forward this to yiisoft/active-record

[smirnov-e]

@Tigrov no, it's about ability to get typed data without ActiveRecord and manual type juggling.

[Tigrov]

Could you show an example how you suggest to solve this?

[Tigrov]

Overall it's a good suggestion, worth to try.

Something like this

(new Query($db))->select(['price'])->from('product')->withTypecast()->scalar();

[smirnov-e]

@Tigrov yes, ->withTypecast() is exactly what I want )

I was planning

1. to put metadata collector in \Yiisoft\Db\Pgsql\Command::internalExecute or in \Yiisoft\Db\Driver\Pdo\AbstractPdoCommand::internalGetQueryResult (after statement creation, but before destruction)
2. Grab PDOStatement::getColumnMeta for each column
3. (optional, heavily disputable) for custom types - get type via additional select, cache info by type oid.
4. typecast each value.

Maybe it's better to extract typecasting from ColumnSchema so that ActiveQuery and raw Query could share typecasting code, but have separate metadata collectors, but not sure If this will work fine.

[samdark]

We plan to support non-PDO drivers as well and this feature might not fit this plan...

[smirnov-e]

You are right, some features differences seems to be inevitable, like support of async query execution.
Most likely ->withTypecast() with throw UnsupportedException in that case.
But at least for pgsql extension there are direct equivalents: pg_field_type and pg_field_type_oid.

[terabytesoftw]

Adding UnsupportedException::class, i don't think it would be good, in any case, this type of feature must be isolated in the extension itself.

[smirnov-e]

@terabytesoftw there is already one \Yiisoft\Db\Exception\NotSupportedException, earlier I didn't check how it is called.
Can you explain how do you isolating to extension?

[Tigrov]

If do this, it should be done for all supported DBMS mysql, pgsql, sqlite, mssql, oracle.

And can be done after solving the issue #737
It will allow use new classes for typecasting