MySQL FIND_IN_SET search result needed in original sequence

Question

I am using FIND_IN_SET in MySQL as below :

SELECT  
        color, b.id
FROM    cards b
        where FIND_IN_SET(b.color, 'gold,gold,bronze,silver,silver') > 0
GROUP   BY b.id
        ORDER BY RAND() 
        limit 5;
        

And I get output as below :

However, I want to get cards in the same sequence as in the parameter string supplied to FIND_IN_SET i.e.

gold, gold, bronze, silver, silver

So the first output row should be of gold color, then another gold, then bronze and so on - in the same sequence without any random search.

I cannot have self join as it's a big table.

Answer 1

You will need to do this using a UNION of 3 queries to find random 'gold', 'silver' and 'bronze' medallists, and then order those results using FIELD:

SELECT color, id
FROM (
    (
        SELECT color, id
        FROM cards
        WHERE color = 'gold'
        ORDER BY RAND()
        LIMIT 2
    )
    UNION ALL
    (
        SELECT color, id
        FROM cards
        WHERE color = 'bronze'
        ORDER BY RAND()
        LIMIT 1
    )
    UNION ALL
    (
        SELECT color, id
        FROM cards
        WHERE color = 'silver'
        ORDER BY RAND()
        LIMIT 2
    )
) m
ORDER BY FIELD(color, 'gold', 'bronze', 'silver')

Demo on SQLFiddle

Here's a way you might implement this as a combination of PHP/MySQL:

$pack_sql_comma_separated = 'gold,silver,gold,gold,silver,gold';
$colors = explode(',', $pack_sql_comma_separated);
$counts = array_count_values($colors);
$sql = "SELECT color, id FROM (";
$subqueries = [];
foreach ($counts as $color => $count) {
    $subqueries[] = "(
        SELECT color, id
        FROM cards
        WHERE color = '$color'
        ORDER BY RAND()
        LIMIT $count
    )";
}
$sql .= implode(' UNION ALL ', $subqueries) . ') m';
// run SQL query
// ...
// assume all query results read into $rows according to medal colour, something like this
$rows = array('gold' => array(array('id' => 20),
                              array('id' => 5),
                              array('id' => 4),
                              array('id' => 27)
                              ),
              'silver' => array(array('id' => 19),
                                array('id' => 11)
                                )
             );

//display in required order
foreach ($colors as $color) {
    $card = array_shift($rows[$color]);
    echo "$color {$card['id']}\n";
}

Demo on 3v4l.org