When there is a multiple value field for which each value must be displayed (thus creating partial "duplicate" records), the pager may miss records, particularly when there are other fields displayed as well.

The reason for this appears to be the fact that the count_query does not have the same logical structure as the data query, and thus it brings back a count of less records than the data query does.

How to replicate the problem:

  1. Create a content type with at least multiple-value CCK text field (I created a content type called "directory_entry" with a field named "last_name")
  2. Add another field to the content type (I added one called "phone_number")
  3. Create a bunch of nodes
  4. Create a view on this content type
  • Page view
  • Use pager (choose your page size)
  • Select fields (last_name, node title, phone_number)
  • Make sure it's sortable on last_name
  • Filter on content type, last_name is not empty
  • Have a look at your view...with any luck you'll notice that there are records missing from the end of the list

    • ...I had a brief look into the code. I'm not acquainted with the views code, but I noticed in include/view.inc that line 673 and 674 look like this:

      $query = db_rewrite_sql($this->build_info['query'], $this->base_table, $this->base_field, array('view' => &$this));
  $count_query = db_rewrite_sql($this->build_info['count_query'], $this->base_table, $this->base_field, array('view' => &$this));

    ...when I printed these queries off lower down in the code, just before they get executed, I got the following:

    query:

    SELECT DISTINCT(node.nid) AS nid, node_data_field_lastnames.field_lastnames_value AS node_data_field_lastnames_field_lastnames_value, node_data_field_lastnames.nid AS node_data_field_lastnames_nid, node_data_field_lastnames.delta AS node_data_field_lastnames_delta, node.type AS node_type, node.title AS node_title 
FROM node node 
  LEFT JOIN content_field_lastnames node_data_field_lastnames 
    ON node.vid = node_data_field_lastnames.vid 
WHERE (node.type in ('directory_entry')) AND (node_data_field_lastnames.field_lastnames_value IS NOT NULL) 
ORDER BY node_data_field_lastnames_field_lastnames_value ASC ;

    and count_query:

    SELECT COUNT(*) FROM (
SELECT DISTINCT(node.nid) AS nid 
FROM node node 
  LEFT JOIN content_field_lastnames  node_data_field_lastnames 
    ON node.vid = node_data_field_lastnames.vid 
WHERE (node.type in ('directory_entry')) AND (node_data_field_lastnames.field_lastnames_value IS NOT NULL) ) count_alias;

    ...so now the problem is obvious...the fact that the count_query is not including the other fields means that the DISTINCT will chop the result to less records. Because I'm not acquainted with the views code, I simply replaced line 674 with this:

     $count_query = $query

    ...because it seemed intuitively odd to me that the count query would be different at all. I understand not using the record count function as it's often implemented in a very inefficient way (sometimes causing a complete traversal of the result in order to count the records) but I'm not sure I understand the reasoning behind generating the query separately. There's too much chance that something like this will happen, where the count query has a different logical structure, and therefore returns spurious results.

    However, as I said...I don't know the views code, so maybe there's a reason I'm not seeing. Regardless, I hope this explanation helps fix the bug. For now, my "fix" works in my case, so I guess I'm not exercising anything that would be indicative of the reason for the differing count/data queries.

    Comments

    nokes’s picture

    nokes CreditAttribution: nokes commented

    Oh, I should note as well that I had been having the same issue at least with 6.x-2.2 as well. I just noticed the problem, then updated to 2.3. When I saw that the bug was still there, I started looking into it and posted this issue.

    David_Rothstein’s picture

    David_Rothstein CreditAttribution: David_Rothstein commented

    I'm relatively new to Views also, but from looking at the code in includes/query.inc -- in particular, the query() function -- it seems like Views already does what you suggest. That is, if the query has a DISTINCT in it, Views will not try to optimize the count query but rather return the original query with all fields included... at least, that appears to be the intention.

    However, I also noticed that a recent bugfix went into this part of the code in #371923: Arguments always using distinct, whether set to or not... which seems like it might be related to the problem you're experiencing. Have you tried this with the latest -dev version of the Views module, and if so, can you reproduce it there or does the problem go away?

    merlinofchaos’s picture

    merlinofchaos CreditAttribution: merlinofchaos commented

    Indeed you shouldn't be getting a different query if DISTINCT is set. Hm.

    nokes’s picture

    nokes CreditAttribution: nokes commented

    Sorry for the delay in getting back...been busy on other projects...I'll test with the dev version and see what happens.

    nokes’s picture

    nokes CreditAttribution: nokes commented

    Just tested this with the most recent dev version (2009-Feb-25) and I've got the same count_query (and the same result). To clarify, I made the change at line 705 of include/view.inc, so that it looked like this:

            if (!empty($this->pager['use_pager']) || !empty($this->get_total_rows)) {
die("COUNT QUERY: ".$count_query);
          $this->total_rows = db_result(db_query($count_query, $args)) - $this->pager['offset'];

    ...and got this result:

    COUNT QUERY: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid FROM {node} node LEFT JOIN {content_field_lastnames} node_data_field_lastnames ON node.vid = node_data_field_lastnames.vid WHERE (node.type in ('%s')) AND (node_data_field_lastnames.field_lastnames_value IS NOT NULL) ) count_alias

    In case it's helpful, here's the export of the view:

    $view = new view;
$view->name = 'photo_directory';
$view->description = 'Photo Directory Listings';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'field_lastnames_value' => array(
    'label' => 'Last Name',
    'link_to_node' => 0,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => 0,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => 0,
    ),
    'exclude' => 0,
    'id' => 'field_lastnames_value',
    'table' => 'node_data_field_lastnames',
    'field' => 'field_lastnames_value',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Override',
    ),
  ),
  'title' => array(
    'label' => 'Name',
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'field_lastnames_value' => array(
    'order' => 'ASC',
    'delta' => '0',
    'id' => 'field_lastnames_value',
    'table' => 'node_data_field_lastnames',
    'field' => 'field_lastnames_value',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Override',
    ),
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'directory_entry' => 'directory_entry',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('title', 'Photo Directory');
$handler->override_option('use_pager', '0');
$handler = $view->new_display('page', 'Listings', 'page_1');
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'directory_entry' => 'directory_entry',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
  'field_lastnames_value' => array(
    'operator' => 'not empty',
    'value' => '',
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'case' => 1,
    'id' => 'field_lastnames_value',
    'table' => 'node_data_field_lastnames',
    'field' => 'field_lastnames_value',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'role',
  'role' => array(
    '2' => 2,
    '3' => 3,
  ),
));
$handler->override_option('title', 'Photo Directory Listings');
$handler->override_option('items_per_page', 15);
$handler->override_option('use_pager', '1');
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
  'grouping' => '',
  'override' => 1,
  'sticky' => 0,
  'order' => 'asc',
  'columns' => array(
    'field_lastnames_value' => 'field_lastnames_value',
    'title' => 'title',
    'field_address_phone' => 'field_address_phone',
  ),
  'info' => array(
    'field_lastnames_value' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'title' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'field_address_phone' => array(
      'separator' => '',
    ),
  ),
  'default' => '-1',
));
$handler->override_option('path', 'directory/list');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));
    nokes’s picture

    nokes CreditAttribution: nokes commented

    More information:

    Following up on David_Rothstein's comments, I checked out include/query.inc

    The intereresting part of what I found may be that the views_query object has no value in it's distinct data member:

    In include/query.inc, in function query, just prior to the foreach, I inserted this:

    if ( $get_count ) {
  print_r($this);
  die();
}

    ...which gave me this, clearly showing the distinct data member without a value:

    ...still digging...

    views_query Object
(
    [table_queue] => Array
        (
            [node] => Array
                (
                    [alias] => node
                    [table] => node
                    [relationship] => node
                    [join] => 
                )

            [node_data_field_lastnames] => Array
                (
                    [table] => node_data_field_lastnames
                    [num] => 1
                    [alias] => node_data_field_lastnames
                    [join] => views_join Object
                        (
                            [definition] => Array
                                (
                                    [table] => content_field_lastnames
                                    [left_field] => vid
                                    [field] => vid
                                    [left_table] => node
                                )

                            [extra_type] => AND
                            [table] => content_field_lastnames
                            [left_table] => node
                            [left_field] => vid
                            [field] => vid
                            [type] => LEFT
                            [adjusted] => 1
                        )

                    [relationship] => node
                )

        )

    [tables] => Array
        (
            [node] => Array
                (
                    [node] => Array
                        (
                            [count] => 1
                            [alias] => node
                        )

                    [node_data_field_lastnames] => Array
                        (
                            [count] => 1
                            [alias] => node_data_field_lastnames
                        )

                )

        )

    [relationships] => Array
        (
            [node] => Array
                (
                    [link] => 
                    [table] => node
                    [alias] => node
                    [base] => node
                )

        )

    [where] => Array
        (
            [0] => Array
                (
                    [clauses] => Array
                        (
                            [0] => node.type in ('%s')
                            [1] => node_data_field_lastnames.field_lastnames_value IS NOT NULL
                        )

                    [args] => Array
                        (
                            [0] => directory_entry
                        )

                    [type] => AND
                )

        )

    [having] => Array
        (
        )

    [group_operator] => AND
    [orderby] => Array
        (
            [0] => node_data_field_lastnames_field_lastnames_value ASC
        )

    [groupby] => Array
        (
        )

    [header] => Array
        (
        )

    [distinct] => 
    [base_table] => node
    [base_field] => nid
    [fields] => Array
        (
            [nid] => Array
                (
                    [table] => node
                    [field] => nid
                    [alias] => nid
                )

            [node_data_field_lastnames_field_lastnames_value] => Array
                (
                    [field] => field_lastnames_value
                    [table] => node_data_field_lastnames
                    [alias] => node_data_field_lastnames_field_lastnames_value
                )

            [node_data_field_lastnames_nid] => Array
                (
                    [field] => nid
                    [table] => node_data_field_lastnames
                    [alias] => node_data_field_lastnames_nid
                )

            [node_data_field_lastnames_delta] => Array
                (
                    [field] => delta
                    [table] => node_data_field_lastnames
                    [alias] => node_data_field_lastnames_delta
                )

            [node_type] => Array
                (
                    [field] => type
                    [table] => node
                    [alias] => node_type
                )

            [node_title] => Array
                (
                    [field] => title
                    [table] => node
                    [alias] => node_title
                )

        )

    [count_field] => Array
        (
            [table] => node
            [field] => nid
            [alias] => nid
            [count] => 1
        )

)
    nokes’s picture

    nokes CreditAttribution: nokes commented

    ..the DISTINCT is there all the way from the db_rewrite_sql commands (include/view.inc line 673 and 674), so obviously one of the modules is determining that DISTINCT should be set.

    from modules/node/node.module:

    function node_db_rewrite_sql($query, $primary_table, $primary_field) {
  if ($primary_field == 'nid' && !node_access_view_all_nodes()) {
    $return['join'] = _node_access_join_sql($primary_table);
    $return['where'] = _node_access_where_sql();
    $return['distinct'] = 1;
    return $return;
  }
}

    ...and since our primary table is "node" and our primary field is "nid", node.module is determining that the DISTINCT should be set. As you can see from above, though, the view doesn't seem to know that this is the case.

    gpk’s picture

    gpk CreditAttribution: gpk commented

    Possible duplicates:
    #488712: views pager doesn't work with repeating date (in the Date issue queue)
    #521770: Displaying a Multi Value Field with a pager when using arguments
    #552804: Records are missing when using pager and items per page
    #553766: Pager doesn't work with multi-field CCK items?

    khan2ims’s picture

    khan2ims CreditAttribution: khan2ims commented

    I have posted this at other forums too.

    For me, the pager is not showing up if I set the Row Style to "Node". If I keep it fields and add in fields, then the pager shows up. And it doesn't matter if I have multiple views on that page with pager enabled.

    F1 F1 F1 !!

    merlinofchaos’s picture

    merlinofchaos CreditAttribution: merlinofchaos commented

    Juste to note, #9 is completely irrelevant to this issue.

    attheshow’s picture

    attheshow CreditAttribution: attheshow commented

    subscribing

    davidredshaw’s picture

    davidredshaw CreditAttribution: davidredshaw commented

    Do we know the reason for having separate results and counting queries? Setting $count_query = $query fixed this for me but I'm sure there was a reason for having the two separately.

    dawehner’s picture

    dawehner
    Primary language German
    CreditAttribution: dawehner commented

    The count_query counts how many items has the full result, so based on this a pager can be built. tting $count_query = $query fixed this for me but I'm sure there was a reason for having the two separately.

    esmerel’s picture

    esmerel CreditAttribution: esmerel commented
    Status: Active » Closed (fixed)

    No activity on this for more than 6 months

    gpk’s picture

    gpk CreditAttribution: gpk commented
    Status: Closed (fixed) » Closed (duplicate)

    Actually I suspect this is a dupe of #552804: Records are missing when using pager and items per page (amongst others), which has been won't fix-ed. Roll on D7 (which fixes this via the new database layer).