Cakephp-3.X: How to Change the Data Type of a Selected Alias

How specify field type for query select when using alias in cakephp?

Finally I found the solution. I add a mutator to entity of post class.

protected function _getId()
{
    return (string)$this->_properties['id'];

}

that force id to return as string.

How to set aliases and then still affect the return type for queries in json formats for Cake 3.x?

Realise that in 3.2 you can use addDefaultTypes to affect the query directly

See Cakephp-3.x: How to change the data type of a selected alias?

Realise that you can access the query from $event when using Crud

See http://crud-view.readthedocs.org/en/latest/basic-usage.html?highlight=query#providing-associations-to-be-displayed

Use the types datetime, and decimal

Put it altogther and you get:

$this->Crud->on('beforePaginate', function(Event $event) use ($conditions) {
        $this->paginate['conditions'] = $conditions;
        $this->paginate['limit'] = 100;
        $this->paginate['fields'] = [
            'id', 'title', 
            'start' => 'start_date', 
            'end' => 'end_date', 
            'revenue', 
            'costs' => 'total_costs', 'collections'
        ];
        $paginationQuery  = $event->subject()->query;

        $paginationQuery
        ->selectTypeMap()
        ->addDefaults([
            'start' => 'datetime',
            'end' => 'datetime',
            'costs' => 'decimal'
        ]);

    });

Override Cakephp 3.1 data type using typeMap()

It doesn't use the TypeMap function but it does convert the custom field into the datetime type you require

$jobs = $this->Jobs->find()
->select([ 'id', 'existingdatetime', 'newdatetime' => '"2016-05-10 16:12:10"' ])
->decorateResults(function ($row) {
    $row['newdatetime'] = new \Cake\I18n\Time($row['newdatetime']);
    return $row;
});

You can see more about it here: CakePHP 3 Decorate Results

How to use paginate and display a different alias for the fields in Cake 3.x?

How do I accomplish that?

By using a key => value syntax in the fields option, like

$this->paginate = [
    'fields' => [
        'non_aliased_field',
        'alias' => 'field',
        // ...
    ]
];

The fields option value will be passed to Query::select(), so you can use the very same syntax.

Sort Pagination by, row Alias Count for example in CakePHP 3.x, data by linking a join table

Thanks ndm, it was what you said.

The problem will be commented the following lines.

Controller:

//    $this->Licensees->schema()
//            ->addColumn('count_users', [
//                'type' => 'integer',
//            ]);
//        $this->Licensees->aliasField('count_users');

$where = [
        'recursive'=>-1,
        'fields' => [
           'Licensees.id',
            'Licensees.name',
            'Licensees.created',
            'Licensees.modified',
//            'Licensees__count_users' => 'count(LicenseesUsers.licensees_id)',
            'count_users' => 'count(LicenseesUsers.licensees_id)', 
         ],
        'sortWhitelist' => ['name','count_users','created','modified'],
        'join' => [
            'LicenseesUsers' => [
                'table' => 'licensees_users',
                'type' => 'LEFT',
                'conditions' => [
                    'LicenseesUsers.licensees_id = Licensees.id'
                ],
            ],
        ],
        'group' => 'Licensees.id'
    ];

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

    // Get data
    $licensees = $this->paginate($this->Licensees);

CakePHP 3 - use different table aliases when separating JOIN conditions

It's not yet possible to specify custom aliases for matching() calls, currently multiple matching() calls on the same association will merge the conditions as can be seen in your generated SQL snippet.

For now you'd have to either create additional associations (ie additionally to your DisplaySubstances association in your SubstancesTable class) with different aliases:

$this->hasMany('DsUses', [
    'className' => 'DisplaySubstances'
]);
$this->hasMany('Displays', [
    'className' => 'DisplaySubstances'
]);

on which you can then match:

$query->matching('DsUses', function ($q) use ($regulatory_information) {
    return $q->where([
        'DsUses.value LIKE' => '%dye%',
        'DsUses.display_id' => 128
    ]);
});

$query->matching('Displays', function ($q) use ($regulatory_information) {
    return $q->where([
        'Displays.value LIKE' => '%bpr%',
        'Displays.display_id NOT IN' => [1, 2, 3, 4, 6, 128]
    ]);
});

or build the joins manually, for example using innerJoin():

$query->innerJoin(
    [
        'DsUses' => 'display_substances'
    ],
    [
        'Substances.id' => new \Cake\Database\Expression\IdentifierExpression('DsUses.substance_id'),
        'DsUses.display_id' => 128,
        'DsUses.value LIKE' => '%dye%'
    ],
    [
        'DsUses.display_id' => 'integer',
        'DsUses.value' => 'string'
    ]
);

$query->innerJoin(
    [
        'Displays' => 'display_substances'
    ],
    [
        'Substances.id' => new \Cake\Database\Expression\IdentifierExpression('Displays.substance_id'),
        'Displays.display_id NOT IN' => [1, 2, 3, 4, 6, 128],
        'Displays.value LIKE' => '%bpr%'
    ],
    [
        'Displays.display_id' => 'integer',
        'Displays.value' => 'string'
    ]
);

See also

• cakephp/cakephp#9499 : Improve association data fetching
• Cookbook > Database Access & ORM > Query Builder > Adding Joins

CakePHP 3: TranslateBehavior on a model that works as an alias

You need two things

1) Set the proper reference name

The translate behavior on the PackageItemsTable class needs to be configured to use the same reference name (the value that is stored in the model column) as the behavior on the ProductsTable class, otherwise you'd never receive any translations, as it would by default look for PackageItems.

This is what the referenceName option can be used for. The reference name is being derived from the class name (not the alias), or for auto-tables, from the database table name or the alias. So for your ProductsTable class it would be Products.

Either set the name manually

$this->addBehavior('Translate', [
    'fields' => ['title', 'description'],
    'translationTable' => 'products_translations',
    'referenceName' => 'Products' // there it goes
]);

or retrieve it dynamically from the behavior on the ProductsTable, like

$referenceName = $this->Products
    ->target()
    ->behaviors()
    ->get('Translate')
    ->config('referenceName');

This however would need to be done after adding the corresponding belongsTo association for the Products table!

2) Use the translations finder for the containment

You need to configure the PackageItems containment to use the translations finder, which is as simple as

contain([
    'PackageItems' => [
        'finder' => 'translations', // there you go
        'Prices' => function ($q) {
            return $q->where(['product_id' => $this->product_id]);
        }
    ]
])

See also

• API > \Cake\ORM\Behavior\TranslateBehavior::_referenceName()
• API > \Cake\ORM\Behavior\TranslateBehavior::$_defaultConfig
• API > \Cake\ORM\Query::contain()

Cakephp 3.0: Cast selected fields to int

Use the typeMap() method

->select(['comment_count'=>'articleCommentCount'])
->typeMap(['comment_count' => 'integer'])

---

Related Topics