Is using beforeFind() event in background to dynamic update SQL queries a good practice? I have a Projects table, which I need to ORDER BY code column. But problem is code is not a simple INT, but a VARCHAR specifying multiple level (like 01-15_33/23). So I need to sort by first part, then next, and so on.

One of the option I found is to replace order field everywhere with a complex array 

    $orderAsc = [
        "cast(Projects.code as unsigned)",
        "cast(SUBSTRING_INDEX(Projects.code,'_', -1) as unsigned)",
        "cast(SUBSTRING_INDEX(Projects.code,'-', -1) as unsigned)",
        "cast(SUBSTRING_INDEX(Projects.code,'/', -1) as unsigned)",
        "Projects.code"
    ];


This is the other solution I wrote.

public function beforeFind(Event $event, Query $query, ArrayObject $options, $primary) {
    $commonOrder = ["Projects.code REGEXP '^[0-9]+' DESC"];

    $orderAsc = [
        "cast(Projects.code as unsigned)",
        "cast(SUBSTRING_INDEX(Projects.code,'_', -1) as unsigned)",
        "cast(SUBSTRING_INDEX(Projects.code,'-', -1) as unsigned)",
        "cast(SUBSTRING_INDEX(Projects.code,'/', -1) as unsigned)",
        "Projects.code"
    ];
    $orderDesc = array_map(function($value) { return $value . ' DESC'; }, $orderAsc);

    $orderAsc = implode(',', array_merge($commonOrder, $orderAsc));
    $orderDesc = implode(',', array_merge($commonOrder, $orderDesc));

    // Order project ascending if no order is specified
    $query-&gt;order($orderAsc);

    // Process ['Projects.code' =&gt; 'DESC'] and ['Projects.code' =&gt; 'ASC'] intelligently
    // Doesn't support other notation like ['Projects.code ASC'] for now
    $query-&gt;traverse(function ($conditions, $clause) use ($orderAsc, $orderDesc) {
        if (!empty($conditions)) {
            $conditions-&gt;iterateParts(function ($c, &amp;$key) use ($orderAsc, $orderDesc) {
                if ('projects.code=asc' == strtolower("$key=$c")) {
                    $c = $orderAsc;
                    $key = null;
                }
                if ('projects.code=desc' == strtolower("$key=$c")) {
                    $c = $orderDesc;
                    $key = null;
                }
                return $c;
            });
        }
    }, ['order']);
}


It process everything in background. And you don't have to change your code at all anywhere.

One of the other advantage I found is, it works well with Pagination feature which generate ORM query in background. But I feel a developer who is new to this project won't understand what's happening at all.

Any feedback regarding the code I wrote or the thinking behind it is welcomed.
It might lead to a ton of refactoring, but have you considered breaking up your code field into multiple columns, and simply ordering by them all?

Beyond that, magic in callbacks is always risky as you've pointed out. But with such a complex requirement for ordering, doing it manually each time doesn't make a lot of sense either.