XF 2.2 Finder not sorting correctly

[Dannymh]

Hi,

I have a custom thread field called "Display order". The custom field is a number , however it looks like it returns as a string in the query, I have tried casting to an integer but nothing seems to work. Essentially the order will look like

1, 10, 11, 12, 13
The finder query

$recent_activities = $this->finder('XF:Thread')
        ->where('user_id', '=', $user->user_id)
        ->where('discussion_type', '=', 'article')
        ->order('CustomFields|ProfileOrder.field_value', 'ASC');

IS there a way to cast this as a number inside finder or anywhere else to make sure it will sort properly.

Some of the things I have tried

$recent_activities = $this->finder('XF:Thread')
        ->where('user_id', '=', $user->user_id)
        ->where('discussion_type', '=', 'article')
        ->order(CAST('CustomFields|ProfileOrder.field_value' AS INTEGER), 'ASC');

$recent_activities = $this->finder('XF:Thread')
        ->where('user_id', '=', $user->user_id)
        ->where('discussion_type', '=', 'article')
        ->order(CustomFields|ProfileOrder.field_value + 0, 'ASC');

have tried casting to unsigned as wel, isnumeric and s onl. Some of those functions work, some case errors. It looks like I may have to d this as a wider query rather than use finder but had hoped for a more robust solution

 

[Jeremy P]

The field_value is a mediumtext column (regardless of the type of field). You can still sort on them and likely achieve the casting behavior you're after using finder expressions, but the values aren't indexed so it's probably not going to perform very well. It may be better to simply add a new integer column.