Hi,
I have a fairly complex query, which is for a click exchange (where users can use their x.credits for clicks on their exchange_links l). This query is what generates a list of click links for a user to click on. Through the various WHERE conditions, it ensures that: (1) they don’t see their own link (can’t click your own!), (2) they haven’t clicked the link in the exchange already (AND l.id NOT IN), and (3) they haven’t clicked the link OUTSIDE of the exchange either (AND l.eggid NOT IN).
The troublesome part is the GROUP BY x.user_id. I only want to display one link per user. The issue is that when I use this method, the GROUP BY clause will always return on ORDER BY l.id ASC. But I want to return a random record from each GROUP BY set to ensure that click links are evenly distributed. I’ve done some reading on this to grasp a general concept of how to do this, but I’m at a loss as to how to write it in this query with the JOINs everywhere.
Thanks in advanced.
SELECT l.*
FROM exchange_links l
INNER JOIN eggs e
ON l.egg_id = e.id
INNER JOIN users u
ON e.user_id = u.id
INNER JOIN exchange x
ON u.id = x.user_id
WHERE u.id != '.$this->db->escape($this->sess->member('id')).'
AND l.id NOT IN ( SELECT c.link_id
FROM exchange_clicks c
INNER JOIN exchange_links l
ON c.link_id = l.id
WHERE c.ip = '.$this->db->escape($this->input->ip_address()).'
AND l.egg_id = e.id
)
AND l.egg_id NOT IN ( SELECT eggid
FROM clicks
WHERE ip = '.$this->db->escape($this->input->ip_address()).'
)
AND x.credits > 0
GROUP BY x.user_id
ORDER BY x.credits DESC, l.time
LIMIT 32
How many rows do you expect to be in the result set? I don’t know about other server-side languages but PHP has a few functions for randomizing the order of an array or picking x number of rows at random
An individual GROUP BY set would never return more than 10 rows per group.
Using sample data could you please post a typical result set?
What server-side language is being used?
PHP is the server-side language. Here is an example result set.
id egg_id hash prefers num_clicks max_clicks time user_id
722 1166810 cSuidyOk3emRstyFbz7uYg7sKAyCUkoivAUjoYcyG1WXer6gGVhQqV0QiipCqkwZeA998PCWfFVA7ZCTFUyveEcXFyopAyNFsmmB 0 743 0 1407703198 64120
3905 1343346 vcmHhgOVEmCnFLgBSzVLs22rc3d9aX5xFzO4rLH80A2mat08MY8fcjjeA1HZeWwQcvUZFO6qDfgRAhLcwu1YWk4kE0K0dtOlpp0v 0 18 0 1408191262 55436
3906 1342561 Ky3uikzTPcCPqZ36o1AbcslUMdyE4eVC6GwcZhKH1HcyRkqGe9pZtObD8RlxTWhcWvLZqHD64Ygiv55tW0hBo0siw8E68OqZBHt9 0 18 0 1408191270 55436
3922 1342821 vTx2EFh2rGdX60gJmcEVPdWhxtiuqNxn5bqrXUClCbSUWDOtJ8NmewbuMehwqH23MtRfsmheCCcgQd7KbJeBvv0C04iSYJLf9Ibz 0 27 0 1408193535 55436
3512 1323609 UestykMMf4AavME74IAs4xysfrzdBPho08vh6PhDJF0sZhUwsIyw1CxDse38SsWnYC5Nw3RHHnI387QYPEq5ztkbwstuVpeo2Q4h 0 220 1607 1408144869 5024
3513 1321681 HUa95sjlnLmaznvpGs0fwCuthESRMtw7zX6Xp3vA4Y5aQnRg8EZcFu6AnpAOmPM0XgFe9pDRHcQuKTFpHtAP4W9sWWLOUYRqtEyr 0 219 1353 1408144911 5024
524 1172305 Me04diObrDo5kc5oMOjXUlPBrjo9AKh4JP9z6ZXZTtJfaAFUao0RUNxMPEoGZaq1LK35iUwOR1pPk0iPy7z61fT4QMtGRY3SoMXd 0 864 0 1407587408 51271
544 1285913 DF6AovPPCsDmJyzDQuyrv8In9Kxr0VqXOIju06b9W6SKAwTSjuqvvlrIgDYu2gmUpc0sK6rhaEecfjc20B1tz7mJpM39u0X2I2BG 0 759 0 1407590005 51271
850 1167653 OlCk0ib8UTtuSz6DEePJhWRTvmqfYLWfSeX3UaITjnOUTgsQqf2ZZKpkht1njfcpxLHvvlHVb4WIiEjw2wGMe4p7Jb6S2H0QGSXR 0 775 0 1407800074 51271
888 1273009 pbYwY6rjNIFMe6UcHqjknWRSzig6xfXOAoLmoAqNGn2ymcBZPX07B73ZuOQA7VXRKduFSk6SD1rcLbBk76uuZ3WuZWtYNnRHTktJ 0 577 0 1407845938 51271
3533 1285909 QZfgYiLr1fNZy9Mt3jG9ZogVJEQrTP0okafMhwk4z4Lsk5aLpMSIuIEUvFagiVpF3bYkzrOKRlx6euJaebvvnBJT5zVbg5Kib3rG 0 195 0 1408147014 51271
1739 1329974 nFPHTuSLdkfiMGDGbHIfHNgwQZwiaxHwLLWkKmqvdhmPsAg1IR1dhQVdz76fzZC0BHo3N0xa8kTcNNnQFIFvWogXNXN759x6p2j9 0 509 0 1407896508 56936
3952 1289091 DvKAZqBocJCirO9uAm9xInGI7smZldJJEnCZfz51VfNBOShfRLfY1DuFu1FoXAw6fUCbUemMrTgamgShgcbXcTviXav5uoCu6yYR 0 18 50 1408194821 75149
2259 1351236 0AazKonasqZKEkwE4LP7LvYxbSBlx5dCh6WZevEuTADfROOmF8wo2wuBUj06mGdvyFtQjdqVi1zfbw2guzTDosRF1tXnvnUNE2a7 Feeds Preferred 617 1000 1407937130 3768
3800 1348404 j1hUnKoN5d96Wpa7qW5icLTkyLO1Pl0cY6n0c1wCVKYKdZudMGL4L1zMYz8lYuSZZ5rAa1ahOskcku1HrHmwT3yXhjhOH3volKFy 0 103 0 1408168424 48219
704 71751 Fdw4jAqzdlrPcxEt1bjl78Zw52ImGN2vytMSF7h45DJXJZW5QIGh9Rey2ZxLtJA5vFkcVstgUfpTkDjq2JRCEpNRdaLV9mrb51Pn Feeds Preferred 727 0 1407689457 5326
1776 889919 dC2jJoGfHyxriWA4c2aLBkEbZvRpWWO53sZn0gM3Heigd2aS9nHT96cNQmCEo6K1YdKrP6qhcyybdMmIoNvKMZZumi8ALeSHXdMb 0 666 0 1407899464 49027
523 1290469 fe9hSDTYEo9F4hHNMqTRj15L3f7mutkwWxTfBHMZxXxsADsR5yuTcTIkLRqoRa3NPlx8dtBTfu8FmBNTvnooiIYs3cQmLQRsrtWD 0 544 2000 1407587315 21081
1180 1293820 3cgA5Tfz5OP77l4QbYFOGS5kRumuKXH0i2B5UUTkSGM9irSiNpbnSK7UoHBaa13jmkeAZg3HVnWO400Km0NTTEiqcH0n65V94VtW 0 979 0 1407875751 80864
3879 1315893 lg4du8r3FHaBam0KHJXmcLX7NrqiBB74KBpx97ar1EWScDJ6ZAc8X8wchLTm6jK5nQaoPrsYiyS1dLkx4OXQnQ6uUZuFViiIA78J 0 37 67 1408186690 79722
3880 1316009 Bf3uItfDuGA4U0zWN2tCJanfCUlA1uo3tq4l5L0woSGZe9aQ7TwiKUv2kNiLp9qHZEzpQozpiLoIcjn2JhqtW6AKbYr03M0uBLXC 0 45 130 1408186728 79722
3855 1353849 c8FJBLnBLq950jGzjDYS8qNRALfAcEBY85V9Cv1pcY30oQnuSA7nN7jhhnO9X5Kk8HqgSrwj9qWjAEyg38SL4F4yVU0d1Uz6irfR 0 71 0 1408178826 45102
2159 1296542 hqG63wEsrhtlLNxzRIIkh2eJk1BbQOhbfEzGa1P1Yi9nemdt68LhsOVytULcHiZRj05BN1bqCAe56SYWmrfMqGZ7zOb3XmBH3OrH 0 279 0 1407925361 28858
3951 631863 HEBCV6gNfKCYTDPDQIrtVIVtNlvlvOlRRMnBujig2nwpB5BaTYf1iFLsxoGCuMbp4uEGUr19Vwbmg9kUZ4SYi67n7kMJYCM99Xsq 0 9 15 1408194785 28858
2808 1344342 v4vD0lF2qcL0lzf1UN6fwoWRkLeLM4GZGbsOFSzD1LL8jvHMUSxKKs1jHYzCjAuHy6b8ctzBAYnoNrSzyJopIUlLiqTjWmloWTeD Clicks Only 279 500 1408004401 44140
3955 1069593 zUItjwS67NZ1ZuMBYamiS7JzVoXmnOjECBwdhr316RLL16jgr4Pyyt7YSSNjug3jnvhsZCFdPkiZobFFZ9BBwUz3XEfeopWdgyee 0 12 0 1408195329 11022
3964 1344550 ZZvbsayH9tTj1ogLcEhRPUjYm5Bj82kNKhDEhQA5jLsV74GTVn7qL87ZHIJz0on9dnctE3uftxjhAJDUjXs1GOePnDacIfKOhgpS Feeds Preferred 4 0 1408195730 81007
3832 1215279 t07sol0GpotxZPwVzkE8TJaenWQVugdLobqkaJIUYEkl04v91312xCJofXtr0KhyRotCnWKR0vOfNrgv7xwfgsNOppeZECoxNIAt 0 78 0 1408175558 39809
2528 1258337 ZHqkkNxuiTodOvOeRiGclcW7UyDCSEiWj1AHC5c7BC4niTAd4ERasBK7Gtrs7QR6J6Afiqtj05pWysQB6J3bK2C4cigrONYX3hpK 0 191 0 1407957286 58125
3389 1348994 iXaSKpoGBANQ7upE39B8F1eS4whujrR4dWhGA3NehxPT7riKuCDaBG7gh04USifNyogJGPZajoYL2OdRRD3h2Ey6YH8cKHd3fQCy 0 83 0 1408139187 58125
3390 1348081 zuYLmiUPUH7aL8YfqS90gY8zbnP4NV6YERKe5tlJ3xGzuGMzDZGfWhDINI5XYSzRG0oIrS9Irj70D64UzsKWPUB7rwPXPU8usxEp 0 86 0 1408139207 58125
3391 819111 MsrOQxL6qygVbll8SQUPZSmlmSOmqB0LqTHD3LyXs0vnVdvJIRVESRPOKKOWpKsrmijVaDXxYR3jpXrzGhTxZ79LWxjucYSl4Ais 0 79 0 1408139224 58125
<?php
echo '<p>This assumes that the result set has been placed in an array called "records"</p>';
$user_ids=array();
foreach ($records AS $record ) {
$user_ids[]= $record['user_id'];
}
$user_ids = array_flip($user_ids);
$user_ids = array_unique($user_ids);
foreach ($user_ids AS $key => $value ) {
$user_records[$key] = array();
}
ksort($user_records,SORT_REGULAR);
foreach ( $records AS $record ) {
$user_records["{$record['user_id']}"][] = $record;
}
foreach ( $user_records AS $user ) {
shuffle($user);
$user_random_entry[]=array_pop($user);
}
echo '
<table>
<tr>
<th>User ID</th>
<th>Egg ID</th>
<th>Hash</th>
<th>Prefers</th>
<th>Num. Clicks</th>
<th>Max Clicks</th>
<th>Time</th>
<th>ID</th>
</tr>
';
foreach ($user_random_entry AS $entry ) {
echo "
<tr>
<td>{$entry['user_id']}</td>
<td>{$entry['egg_id']}</td>
<td>{$entry['hash']}</td>
<td>{$entry['prefers']}</td>
<td>{$entry['num_clicks']}</td>
<td>{$entry['max_clicks']}</td>
<td>{$entry['time']}</td>
<td>{$entry['id']}</td>
</tr>
";
}
echo '</table>';
?>
That script spits out a random entry for each user so that each user has one entry in the table that is displayed.