0
\$\begingroup\$

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->order($orderAsc);

    // Process ['Projects.code' => 'DESC'] and ['Projects.code' => 'ASC'] intelligently
    // Doesn't support other notation like ['Projects.code ASC'] for now
    $query->traverse(function ($conditions, $clause) use ($orderAsc, $orderDesc) {
        if (!empty($conditions)) {
            $conditions->iterateParts(function ($c, &$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.

\$\endgroup\$
  • 3
    \$\begingroup\$ The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles. \$\endgroup\$ – Toby Speight Oct 24 '18 at 14:39
1
\$\begingroup\$

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.

\$\endgroup\$

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