I'm trying to find the activity_type with the highest value, so I can ++ that value and insert a new activity type. I thought this would be easy.

I cannot sort on the "value" column of the option_value table because it is a varchar column containing (in this case) numbers. Take the following api call:

civicrm_api3('OptionValue', 'getsingle', [
  'option_group_id' => 'activity_type',
  'options' => ['sort' => 'value DESC', 'limit' => 1]

This ought to return the activity type with the highest value. However, it returns "9" because I guess mysql thinks the string "9" > "62" (which is the actual max in my sandbox).

Anyone have a good approach to suggest?


I see if we try to modify the sort col with any sql defined functions, it gets returned with the Error Message. For Eg - ['sort' => 'CAST(value AS decimal) DESC'] returns

Unknown field specified for sort. Cannot order by 'CAST(value AS decimal) DESC'.

Maybe, there should be some modification in CRM_Utils_Rule::mysqlOrderBy() to either handle such cases.

Or we can simply use get instead of getsingle and then order the returned result values using array functions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.