2

WHen i try to do :

$fields = array('id' => 'custom_id', 'title' => 'some_name');

The result I get has id as a string.

If I do:

$fields = array('custom_id', 'title' => 'some_name');

then it gives custom_id as integer.

How can I obtain custom_id as id without loosing the data type. I read the documentation but didn't found much help.

There is something that virtual fields can do I think. But is it possible inside the find query without the use of virtual fields etc?

Thanks in Advance

ndm
  • 59,784
  • 9
  • 71
  • 110
Ashish Choudhary
  • 2,004
  • 1
  • 18
  • 26

2 Answers2

6

As of CakePHP 3.2

you can use Query::selectTypeMap() to add further types, which are only going to be used for casting the selected fields when data is being retrieved.

$query = $table
    ->find()
    ->select(['alias' => 'actual_field', /* ... */]);

$query
    ->selectTypeMap()
    ->addDefaults([
        'alias' => 'integer'
    ]);

You can use any of the built-in data types, as well as custom ones. In this case the alias field will now be casted as an integer.

See also

With CakePHP 3.1 and earlier

you'll have to use Query::typeMap(), which will not only affect the selected field when data is being retrieved, but in various other places too where data needs to be casted according to the field types, which might cause unwanted collisions, so use this with care.

$query
    ->typeMap()
    ->addDefaults([
        'alias' => 'integer'
    ]);

See also

Change the type of existing columns

Changing the type of an existing column of a table is possible too, however they need to be set using a specific syntax, ie in the column alias format used by CakePHP, that is, the table alias and the column name seprated by __, eg, for a table with the Articles alias and a column named id, it would be Articles__id.

This can be either set manually, or better yet retrieved via Query::aliasField(), like:

// $field will look like ['Alias__id' => 'Alias.id']
$field = $query->aliasField('id', $table->alias());

$query
    ->selectTypeMap()
    ->addDefaults([
        key($field) => 'string'
    ]);

This would change the the default type of the id column to string.

See also

ndm
  • 59,784
  • 9
  • 71
  • 110
  • I am using cake 3.0.6 and it displays `Call to undefined method Cake\Database\TypeMap::addDefaults()` when using second solution. I used other functions from `TypeMap.php` and `TypeMapTrait.php` without any luck. – Ashish Choudhary Mar 02 '16 at 11:58
  • Then update your CakePHP dependency. The last 3.0.x release was 3.0.16. @AshishChoudhary – ndm Mar 02 '16 at 12:06
  • Still the same. my `defaultTypes` has `'id' => 'integer'` but still the output is string. I have tried it with cakephp 3.1.5. `'defaultTypes' => ['id' => 'integer']` – Ashish Choudhary Mar 03 '16 at 05:10
  • Can you post a working example of it please. I shall copy paste the as it is code and change the model to see if that works for me as well or not. What I can see is the code appears fine. Functions makes sense as well but the output is not changing. Is there something i'm missing. – Ashish Choudhary Mar 03 '16 at 05:13
  • 1
    The snippet above is a "working example", it works just fine, `alias` will be returned as an integer. If you have problems with this in your environment, then you'll have to come up with a reproducible example. @AshishChoudhary – ndm Mar 03 '16 at 10:36
  • Sorry I was wondering if it's possible to find out all the possible datatypes acceptable in TypeMap for 3.2 http://stackoverflow.com/questions/35973135/what-are-the-acceptable-types-used-in-the-class-typemap-in-cake-3-x – Kim Stacks Mar 13 '16 at 17:11
  • how can i use typemap in my query ? can you help me? i used it in my query,but i got fatal error ... @ndm – mostafaznv Sep 29 '16 at 20:38
0

Hi my alternative example full, user schema() in controller Users add type column aliasFiels by join data:

        $this->Users->schema()
            ->addColumn('is_licensed', [
                'type' => 'boolean',
            ])
            ->addColumn('total_of_licenses', [
                'type' => 'integer',
            ]);

        $fields = [
            'Users.id',
            'Users.username',
            'Users.first_name',
            'Users.last_name',
            'Users.active',
            'Users__is_licensed' => 'if(count(LicenseesUsers.id)>=1,true,false)',
            'Users__total_of_licenses' => 'count(LicenseesUsers.id)',
            'Users.created',
            'Users.modified',
            'Languages.id',
            'Languages.name',
            'Countries.id',
            'Countries.name',
            'UserRoles.id',
            'UserRoles.name',
        ];

     $where = [
        'contain' => ['UserRoles', 'Countries', 'Languages'],
        'fields' => $fields,
        'join' => [
            'LicenseesUsers' => [
                'table' => 'licensees_users',
                'type' => 'LEFT',
                'conditions' => [
                    'Users.id = LicenseesUsers.users_id'
                ],
            ],
        ],
        'group' => 'Users.id'
    ];

    // Set pagination
    $this->paginate = $where;

   // Get data in array
   $users = $this->paginate($this->Users)->toArray();
Sergio
  • 2,369
  • 1
  • 15
  • 22