Need the output of a query sorted in round robin manner

[Vikas Jayna]

Dear All,

I am working for a real estate site which is faced with the problem of a few brokers spamming the site by putting a number of properties each day. As a result, properties posted by non-spammers do not get enough presence. Having said this, its quite easy to have an upper limit on the number of properties that can be posted by a person but we do not wish to do the same.

Is it possible in mysql to order the results on a particular column in round robin fashion such that if there are 10 unique advertisers that match the criteria, then 1 property from each adviser comes, then the next 10 in the same way and so on..?

Please suggest a way out for this in mysql.

Thanks in advance!

Best Regards,
Vikas Jayna

[fenway]

Not really... the problem is that 10 is a magic number, and MySQL would have "know" which rows were already returned.  Of course, you can easily do the dirty work in PHP, with a distinct list of advertisers and counts, but that's not exactly what you're looking for.  I'll keep thinking about this... fantastic question!

[effigy]

Here's a first draft; I'm not sure if it covers everything you want. It steps through the entire property list one at a time and either (1) adds it to the output; or (2) adds it to the seen, and back onto the property list.

[code]
<pre>
<?php

### Data.
$properties = array(
'ABC Real Estate',
'ABC Real Estate',
'ABC Real Estate',
'DEF Real Estate',
'ABC Real Estate',
'GHI Real Estate',
'XYZ Real Estate',
'ABC Real Estate',
'DEF Real Estate',
'PQR Real Estate',
'JKL Real Estate',
'JKL Real Estate',
'XYZ Real Estate',
'XYZ Real Estate',
);
echo '<hr>Data:<hr>';
print_r($properties);

### Metadata.
$repeats = array_count_values($properties);
$unique = count($repeats);
echo "<hr>$unique properties and their repeats:<hr>";
print_r($repeats);

### Processing.
$counter = 1;
$last_property = '';
$out_stack = array();
$seen_stack = array();
while ($property = array_shift($properties)) {
if ($counter % $unique == 0) {
$seen_stack = array();
if ($property == $last_property && count($properties) > 1) {
array_push($seen_stack, $property);
}
}
if (! in_array($property, $seen_stack)) {
array_push($out_stack, $property);
--$repeats[$property];
++$unique_found;
}
else {
array_push($properties, $property);
++$counter;
}
array_push($seen_stack, $property);
$last_property = $property;
}

echo '<hr>Final:<hr>';
print_r($out_stack);

?>
</pre>
[/code]

[quote]
Data:
Array
(
    [0] => ABC Real Estate
    [1] => ABC Real Estate
    [2] => ABC Real Estate
    [3] => DEF Real Estate
    [4] => ABC Real Estate
    [5] => GHI Real Estate
    [6] => XYZ Real Estate
    [7] => ABC Real Estate
    [8] => DEF Real Estate
    [9] => PQR Real Estate
    [10] => JKL Real Estate
    [11] => JKL Real Estate
    [12] => XYZ Real Estate
    [13] => XYZ Real Estate
)
6 properties and their repeats:
Array
(
    [ABC Real Estate] => 5
    [DEF Real Estate] => 2
    [GHI Real Estate] => 1
    [XYZ Real Estate] => 3
    [PQR Real Estate] => 1
    [JKL Real Estate] => 2
)
Final:
Array
(
    [0] => ABC Real Estate
    [1] => DEF Real Estate
    [2] => GHI Real Estate
    [3] => XYZ Real Estate
    [4] => PQR Real Estate
    [5] => JKL Real Estate
    [6] => XYZ Real Estate
    [7] => ABC Real Estate
    [8] => DEF Real Estate
    [9] => XYZ Real Estate
    [10] => ABC Real Estate
    [11] => JKL Real Estate
    [12] => ABC Real Estate
    [13] => ABC Real Estate
)
[/quote]

[fenway]

Looks great... only problem is that it requires getting back _all_ the data to begin with, and probably won't scale well at some point.

[Vikas Jayna]

Thanks effigy!
The solution works fine but as fenway said its going to be quite slow as I have a database of 50000+ properties and the number of searches is also quite a lot. I am rather looking for a solution within mysql that would do the entire computation within a fraction of a second or an algorithm(probably in C/C++) that has lesser computations compared to the one above.

Regards,
Vikas Jayna

[effigy]

Is it possible to mimic what I did in PHP with a stored procedure and a temporary table? I'm not sure how much better that would be...

[b]Update:[/b] Or create a trigger that calculates the entry's sort value on insert.

[fenway]

I think I said earlier that you could also simply find out the # of occurences of each advertiser, and if you cheat with a GROUP_CONCAT(), you can grab all the UIDs as well.  With some fancy sorting routines, you can generate the "desired" order.