Jump to content


Photo

Need the output of a query sorted in round robin manner


  • Please log in to reply
6 replies to this topic

#1 Vikas Jayna

Vikas Jayna
  • Members
  • PipPipPip
  • Advanced Member
  • 121 posts
  • LocationNoida, Delhi, India

Posted 03 October 2006 - 12:58 PM

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
Vikas Jayna,
Project Manager - Jeevansathi.com
7 yrs. exp. in LAMP
Certified Mysql DBA
Zend Certified Engineer

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 October 2006 - 02:55 PM

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!
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 03 October 2006 - 04:36 PM

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.

<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>

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
)


Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 October 2006 - 04:40 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Vikas Jayna

Vikas Jayna
  • Members
  • PipPipPip
  • Advanced Member
  • 121 posts
  • LocationNoida, Delhi, India

Posted 04 October 2006 - 03:38 PM

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
Vikas Jayna,
Project Manager - Jeevansathi.com
7 yrs. exp. in LAMP
Certified Mysql DBA
Zend Certified Engineer

#6 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 04 October 2006 - 06:39 PM

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...

Update: Or create a trigger that calculates the entry's sort value on insert.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 04 October 2006 - 07:45 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users