"ORDER BY FIELD" How?

[ortick]

I need query:

SELECT * FROM table WHERE row IN (1,2,3) ORDER BY FIELD (row, 1,2,3)

What solution with medoo?

[jtechera]

It's in the documentation, at the end of the where syntax section:

$database->select("account", "user_id", [ "GROUP" => "type",

// "ORDER" => "age DESC"
"ORDER" => "age",

// Must have to use it with ORDER together
"HAVING" => [
    "user_id[>]" => 500
],

// LIMIT => 20
"LIMIT" => [20, 100]

]); // SELECT user_id FROM account // GROUP BY type // ORDER BY age // HAVING user_id > 500 // LIMIT 20,100

[ortick]

I see, but what about "ORDER BY FIELD (row, 1,2,3)" ?

[catfan]

@ortick Using ORDER BY FIELD will slow down the performance greatly. The good idea to handle this situation is fetched the data at first and sorted by PHP sorting function afterward. http://www.php.net/manual/en/array.sorting.php

Here the sorting function for user define order like ORDER BY FIELD for usort:

function mySort($a, $b)
{
    // The ordered field
    $order = array_flip([50, 164, 1023, 532, 43]);
    // The field name
    $field = 'user_id';

    return $order[ $a['user_id'] ] - $order[ $b['user_id'] ];
}

Original:

Array
(
    [0] => Array
        (
            [user_name] => jason
            [user_id] => 43
        )

    [1] => Array
        (
            [user_name] => Andy
            [user_id] => 50
        )

    [2] => Array
        (
            [user_name] => snow
            [user_id] => 164
        )

    [3] => Array
        (
            [user_name] => foo
            [user_id] => 532
        )

    [4] => Array
        (
            [user_name] => sinda
            [user_id] => 1023
        )

)

After:

usort($data, "mySort");

Array
(
    [0] => Array
        (
            [user_name] => Andy
            [user_id] => 50
        )

    [1] => Array
        (
            [user_name] => snow
            [user_id] => 164
        )

    [2] => Array
        (
            [user_name] => sinda
            [user_id] => 1023
        )

    [3] => Array
        (
            [user_name] => foo
            [user_id] => 532
        )

    [4] => Array
        (
            [user_name] => jason
            [user_id] => 43
        )

)

By commenting this issue, I came up an idea to improve the sorting feature for Medoo based on this concept. Maybe available in the further.

[ghost]

Using catfan solution. Actually by doing this query with mysql and standar pdo functions in php:

1. taking longer
2. give extra burden to mysql when this burden can be handled great and fast with php array functions

catfan could add these php array functions in medoo class I think to make medoo compatible with what you want but medoo would be bigger in size (now it is 10kb) and gives you the ability to get the data and sort them in various ways later with php.

[catfan]

@ortick @ks1f14s

I tested it, but I founded that the customized sorting PHP function is not faster than MySQL ORDER BY FILED.

That`s a little bit strange, but I think the reasonable explanation is the SQL querying language is running as C and the ORDER BY command can use the indexed data to optimize the ordering operation.

Anyway, this feature is still valuable and will be added on the further.

[catfan]

Added

[richardaum]

What happens when that field is datetime (from mysql)?

[yaowenqiang]

how to add a desc order ?