Vikas Jayna Posted October 3, 2006 Share Posted October 3, 2006 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted October 3, 2006 Share Posted October 3, 2006 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! Quote Link to comment Share on other sites More sharing options...
effigy Posted October 3, 2006 Share Posted October 3, 2006 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] Quote Link to comment Share on other sites More sharing options...
fenway Posted October 3, 2006 Share Posted October 3, 2006 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. Quote Link to comment Share on other sites More sharing options...
Vikas Jayna Posted October 4, 2006 Author Share Posted October 4, 2006 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 Quote Link to comment Share on other sites More sharing options...
effigy Posted October 4, 2006 Share Posted October 4, 2006 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 4, 2006 Share Posted October 4, 2006 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.