| Project: | Views |
| Version: | 5.x-1.x-dev |
| Component: | Views Data |
| Category: | bug report |
| Priority: | minor |
| Assigned: | Unassigned |
| Status: | closed (won't fix) |
Issue Summary
This is my first post. It is possible that I am posting at the wrong place…
First of all: thank you for the great module!
Just wanted to mention the problem and solution in case it might be useful for someone else.
I was creating a view and selected “Random” as sort criteria. I noticed that the view came out with duplicate records. After doing some code debugging I found the following.
The query was constructed with “rand() as random_order” added as a field, then the query was using “order by random_order” . I fixed the problem by removing extra field and ordering records by the function itself: “order by “rand()”.
To do so, I modified add_orderby function in views_quiery.inc. Instead of:
$this->add_field($f, $table, $as);
$this->orderby[] = "$as $order";
I now have:
if ($table){
$this->add_field($f, $table, $as);
$this->orderby[] = "$as $order";
}else{
$this->orderby[] = "$f $order";
}
Hope this post can be useful.
Comments
#1
This might be related to the core 'distinct' issue. No fixes are planned for the 1.x line of views.
#2
We have had the same problem. Duplicate records in Views after setting the View order to random and are trying the fix given above in the description.
#3
the same problem is in 6.x-2.11 version, here is my solution without modifying views core code, insert it into any custom module, do not forget to replace 'yourmodule' with real name:
<?php
/**
* Implementation of hook_views_query_alter()
*/
function yourmodule_views_query_alter(&$view, &$query) {
// fixing duplicate views result for random order argument
if (is_array($query->orderby)) {
foreach ($query->orderby as &$q) {
if (strpos($q, '_random') === 0) {
if (!$_SESSION['views_random_sort_int'] || !$_REQUEST['page']) {
$_SESSION['views_random_sort_int'] = rand(1, 1001);
}
global $db_type;
switch ($db_type) {
case 'mysql':
case 'mysqli':
$formula = 'RAND('. $_SESSION['views_random_sort_int'] .')';
break;
case 'pgsql':
$formula = 'RANDOM()';
break;
}
$q = $formula;
}
}
}
}
?>
in order to fix core code, views_handler_sort_random.inc should be changed to:
<?php
/**
* Handle a random sort.
*/
class views_handler_sort_random extends views_handler_sort {
function query() {
// the idea is to add integer argument to MySQL RAND() function, which will remain
// among all the pages for the same view, going back to the first page (no $_REQUEST['page'])
// will initialize and randomize entire view
if (!$_SESSION['views_random_sort_int'] || !$_REQUEST['page']) {
$_SESSION['views_random_sort_int'] = rand(1, 1001);
}
global $db_type;
switch ($db_type) {
case 'mysql':
case 'mysqli':
$formula = 'RAND('. $_SESSION['views_random_sort_int'] . ')';
break;
case 'pgsql':
$formula = 'RANDOM()';
break;
}
if (!empty($formula)) {
//the third parameter is order ('ASC' or 'DESC'), which has no influence in random case
$this->query->add_orderby(NULL, NULL, '', $formula);
}
}
function options_form(&$form, &$form_state) {
parent::options_form($form, $form_state);
$form['order']['#access'] = FALSE;
}
}
?>
#4
Please please please don't hijack such old issues. Thanks!
#5
It's a real head scratcher, that's for sure. You don't need to save the integer in the $_SESSION as in #3 above. Choosing a new random integer to pass to RAND() will do. The attached patch should not work, but does on Views 6.x-2.12.