CakePHP - MySQL Order by CAST to INT not working-CodePudding

With CakePHP 2.x and MySQL 5.6.40 i'm creating the following pagination:

$options = array(
    'contain' => array(
        'Category',
        'ProductImage'
    ),
    'limit' => 10,
    'order' => array(
        'Product.year' => 'DESC',
        'Product.month' => 'DESC'
    )
);

But i forgot my products.month column is varchar so i have to do a cast to convert the Product.month to integer:

$options = array(
    'contain' => array(
        'Category',
        'ProductImage'
    ),
    'limit' => 10,
    'order' => array(
        'Product.year' => 'DESC',
        'CAST(Product.month as INT)' => 'DESC'
    )
);

But on the Query output, it removes the Order By CAST as INT

SELECT
    ...
FROM
    `web_quality`.`products` AS `Product`
LEFT JOIN `web_quality`.`categories` AS `Category` ON (
    `Product`.`category_id` = `Category`.`id`
)
WHERE
    `Product`.`category_id` IN (
        SELECT
            id
        FROM
            categories
        WHERE
            tag = 'revistas'
        AND company_id IN (
            SELECT
                id
            FROM
                companies
            WHERE
                tag = 'dr'
        )
    )
AND NOT (
    (
        (`Product`.`year` = '2022')
        AND (`Product`.`month` = '2')
    )
)
AND `Product`.`enabled` = '1'
ORDER BY
    `Product`.`year` DESC,
LIMIT 10

Also it doesn't generate any kind of error, if i try manually the query with the CAST as INT it works, but i have to do it using the CakePHP Model, what could be wrong ?

Thank you so much !

CodePudding user response：

From what I remember you should use a virtual field for that, as the paginator checks whether the given field exists on the model, and whitelisting an SQL snippet doesn't seem like an overly good idea.

So in your model you'd do something like:

public $virtualFields = array(
    'month_number' => 'CAST(Product.month as INT)',
);

and then you order by Product.month_number.