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.

CommentFileSizeAuthor
#5 views-6.x-2.12-random-sort-dedup.patch636 bytesjohn franklin

Comments

esmerel’s picture

esmerel commented
Status: Active » Closed (won't fix)

This might be related to the core 'distinct' issue. No fixes are planned for the 1.x line of views.

jmseigneur’s picture

jmseigneur commented

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.

andriy’s picture

andriy commented
Version: 5.x-1.x-dev » 6.x-2.11

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:

/**
* 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:

/**
 * 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;
  }
}
dawehner’s picture

dawehner
Primary language German
commented
Version: 5.x-1.6 » 5.x-1.x-dev

Please please please don't hijack such old issues. Thanks!

john franklin’s picture

john franklin commented
Version: 6.x-2.11 » 5.x-1.x-dev
StatusFileSize
new views-6.x-2.12-random-sort-dedup.patch636 bytes

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.