CODEWITHSUNDEEP
phpmysqlsorting
Andreas Jarbol
Andreas Jarbol

Reputation: 745

PHP, MySQL optimizing sorting based on an external sorting faction


I'm trying to optimize the speed of data processing and would like to have any suggestions for a scalable solution to the following:

I have a PHP array looking like:

$arr = array(
           2543 => 1,
           2123 => 2,
           2431 => 1,
           3223 => 3,
           ...
       );

Here the keys represent unique item ids and the value, orders of these ids in a system. Using this (very big) array I would like to extract the items assign them their priority and sort in the most efficient manner.
My current solution is rather straight forward (using PDO and primarily based on PHP):

$item_ids = array_keys($arr);
$item_ids_csv = implode(",", $item_ids);

$sql = "SELECT `id`,`name` 
        FROM `item_data`
        WHERE `id` IN ($item_ids_csv);";

#execute is a custom function returning a PDO execution
$items = $this->execute($sql)->fetchAll(PDO::FETCH_ASSOC);

foreach($items as &$item){
    $item['order'] = $arr[$item['id']];
}

usort($items, function($a, $b) {
    return $a['order'] - $b['order'];
});

print_r($items);

Which works just fine, but I'm however wondering if the above PHP operation is possible entirely in SQL? Maybe I could INNER JOIN the orders ON each id and ORDER BY these?

I'm figuring that it wound save me some processing time to shift this logic to the database, correct me if I'm wrong.

Upvotes: 0

Views: 149

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can do the ordering in the database, with a single query by splitting the values into three groups:

select id, name
from item_data
order by (id in ($items_ids_csv_1)) desc,
         (id in ($items_ids_csv_2)) desc,
         (id in ($items_ids_csv_3)) desc;

This works because the expression id in (...) returns "1" when the id is in the list and "0" otherwise.

Upvotes: 1

Turophile
Turophile

Reputation: 3405

As I suggested in a comment, if there are only 3 sequence values, split it into 3 parts:

# make 3 different $item_ids_csv
$item_ids_csv_1 = ...
$item_ids_csv_2 = ...
$item_ids_csv_3 = ...

...

$sql = "SELECT `id`,`name`,1 AS seq
        FROM `item_data`
        WHERE `id` IN ($item_ids_csv_1)
        UNION 
        SELECT `id`,`name`,2 AS seq
        FROM `item_data`
        WHERE `id` IN ($item_ids_csv_2)
        UNION 
        SELECT `id`,`name`,3 AS seq
        FROM `item_data`
        WHERE `id` IN ($item_ids_csv_3)
        ORDER BY seq, id;";

Upvotes: 0

Related Questions