Hi everyone,

I think this might be related to this issue. #375298: Pager misses records when multiple value fields are displayed with other fields But I'm not sure, I'm not a drupal programmer.

Here is my page. http://www.law.hawaii.edu/personnel/teaching Each of the headings are a "teaching area" which are terms in a taxonomy vocabulary. Attached is a screenshot of my view settings and how I'm grouping by term.

Right now pager is set to yes and items per page is set to unlimited. All the records appear fine. However, if I set "items per page" to any number, 10, 25, etc, then all the records do *not* display. For example, if I set items per page to 25 then the last page in the view will only go up to "Internet Law and Policy" instead of "Wildlife and Natural Resources Law".

Please let me know if there is more information that is needed to help debug.

I'm using...
- Drupal 6.13
- Views 6.x-3.x-dev
- CCK 6.x-2.5

Thanks!
-Tim

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

gpk’s picture

Yes I think you may well have found a duplicate of the issue you mention.

In your Faculty/Staff Profile node type, can one of of the CCK fields have multiple values?

TimG1’s picture

Hi gpk,

Thanks for responding!

Yes, many fields within my CCK Node Type are allowed to have multiple values.

If you're asking about the "teaching area" field specifically, yes that can have multiple values too. The "teaching area" is actually a taxonomy vocabulary with Multiple Select checked under the Settings for that vocabulary.

-Tim

gpk’s picture

Category: support » bug

I think that what is happening with this bug is that Views first "counts" how many records there are in total, to work out how many pages of results there are, and then it works out which results to show on the page you request. However for reasons explained in #375298: Pager misses records when multiple value fields are displayed with other fields that I've not yet fully got my head round it only counts unique nodes when counting, although the results correctly include all the "duplications" of nodes (with their different-valued teaching areas). So if many of the nodes take 2 or more values for "teaching area" then you could well be missing many of the results pages! On the other hand if only one or 2 nodes take 2 or more values then you might not notice the problem, if they all fitted on the last page of results anyway.

Does this sound a bit like what might be going on in your case?

TimG1’s picture

Hi GPK,

Yeah the symptoms sound right. I'm not sure if that's definitely what's happening in my case. But, it sure sounds like it. Is there a way to tell/confirm?

In any case, I'd be happy to help be a guinea pig and help test/try different things to try and fix this bug.

-Tim

dawehner’s picture

Status: Active » Postponed (maintainer needs more info)

It would be cool if someone could describe what for kind of cck and what for kind of views configuration you need to reproduce it

gpk’s picture

I'm still on Views 6.x-2.x so can't definitely confirm that this still exists in 3.x, though I'm not aware of any fixes.

> what for kind of cck and what for kind of views configuration you need to reproduce it
You need a CCK multiple values field. In my case this is a date field which allows repeats: the repeat dates are generated by CCK on node save and are stored as multiple field values.

You need a view (I'm using a page view) that:
- has a pager
- row style=fields
- the multiple values field is included in the output fields
- "Group multiple values" *not* checked for this field
- and set the the number of entries per page to less than the total number of results returned by the View when the pager is off

gpk’s picture

Status: Postponed (maintainer needs more info) » Active
merlinofchaos’s picture

The problem here is that core can add a DISTINCT to the query; if you're relying on multiple fields per node showing up, that DISTINCT can cause values to disappear.

gpk’s picture

Ah, that would follow ... when does core add the DISTINCT? I can't see anything obvious in pager_query() that would cause this.

merlinofchaos’s picture

Status: Active » Closed (won't fix)

It happens in node_db_rewrite_sql() whenever there are node access modules. It forces every node query to distinct.

gpk’s picture

Interesting... do you know why the problem with missing records only surfaces with a pager query? Without the pager all of the records show up. And even with the pager, I do see multiple entries for the same node, it's just that the pager seems to under-estimate the total number of records, ... it's the COUNT query has an unwanted DISTINCT in it maybe??

And would this therefore be a bug in core?

Thanks!

gpk’s picture

Hmm I see there are various issues (http://drupal.org/search/apachesolr_search/db_rewrite_sql%20count%20dist..., http://www.google.co.uk/search?num=100&hl=en&q=db_rewrite_sql+count+dist...) to do with db_rewrite_sql(), DISTINCT and node access modules, including problems with pagers mis-counting the total number of records. Probably I need to update to the latest versions of everything first since IIRC there have been some bugfixes in this area.

I guess the D7 database API fixes most if not all of this..

enkara’s picture

I know this is extremely dirty, but I've fixed this issue adding this code in line 725 of view.inc (views 6.x-2.10):

if ($this->name == "view_name") {
       	$count_query = str_replace("DISTINCT", "", $count_query);
}

I'm just removing the distinct by node.nid from the count query only in the view I have the problem with the docs (view_name).
I haven't test it very much, but I drop it here to help a possible desperated soul.

Matt B’s picture

Version: 6.x-3.x-dev » 6.x-2.12

I'm having this same issue with a view that uses a Node Referrer relationship pulling back multiple results from the related nodes. enkara's "hack" in #13 has fixed this. Perhaps this needs to be used if the Distinct option is not set to yes on the views settings?

kndr’s picture

Status: Closed (won't fix) » Needs review
FileSize
2.11 KB

I can confirm the problem with a pager when multple values fields are used inside the view. In my own views I am using multiple date field and problem with pager is very annoying. In my opinion it is the bug inside view module. It could be fixed. At #11 we have the key question: "(...) why the problem with missing records only surfaces with a pager query?" and answer is: because of count optimization inside views_query::query function. This is the source of problem since count optimization trims every field besides of base field (usually: nid). We can read it in the comment: "An optimized count query includes just the base field instead of all the fields.". That's why we have two different queries. It is not important when there is no multiple fields but makes very big difference when multiple fileds exists in view. I suggest to check if any field inside view is multiple. If yes - count optimization should be bypassed. It works for me.

dawehner’s picture

Version: 6.x-2.12 » 7.x-3.x-dev
Status: Needs review » Needs work

We are talking about 7.x, right?

It might be helpful to have this as setting as well.

merlinofchaos’s picture

That's not true. The presence of a field does not control the number of rows. That's about the joins. The only time the presence of a field matters is when you're using aggregate functions, and the count query optimization handles that.

kndr’s picture

dereine: My patch is for D6 (I don't use D7) but it would be nice if someone could write a D7 patch and backport it to D6 :) I agree, it could be usefull as setting.

kndr’s picture

merlinofchaos: I don't know how it works but it is fact that pager fails with multiple value field if there are some modules, which add "DISTINCT" clause to query (as you mention at #8). It is obvious that "SELECT DISTINCT nid (...)" will give other results than "SELECT DISTINCT nid, event_date (...)" (with assumption, that event_date is the multiple value field). Truncation of fields inside count optimization makes problem under some circumstances. I know, that my proposal could be not wrong but problem with SELECT DISTINCT queries exists and there is no solution for it, as yet.

dawehner’s picture

Version: 7.x-3.x-dev » 6.x-3.x-dev

Move back to 6.x. Additional it does not apply to 6.x-3.x anymore.

Alan D.’s picture

Much cleaner than my quick hack!

For those that do not want to hack modules, you can do a string replace in hook_views_pre_execute() to add enough fields so that it matches the main query.

function htsa_views_pre_execute(&$view) {
	if ($view->name == 'events_search' && $view->current_display == 'page_1') {
    $view->build_info['count_query'] = str_replace(
        'SELECT node.nid AS nid',
        "SELECT node.nid AS nid, CONCAT(node.nid, '-', node_data_field_event_session_date_time.delta) AS node_delta_stop_distinct", $view->build_info['count_query']);
	}
}

I have re-rolled and slightly changed the patch. I think that if only the fields that have multiple values + the primary field would be needed to be included, rather than all of the additional SELECT fields, but the functionality of this patch should be the same as kndr's in #15.

I am looking at this issue with a Views 6.x-2.x version (about the only Drupal 6 installation I have atm), so this patch has been written with ZERO testing.

Side note: in the current git 6.x-3.x branch, plugins/views_plugin_query_default::compile_fields(), $get_count_optimized is not defined.

Alan D.’s picture

Status: Needs work » Needs review

For fun, lets see if it passes any tests

Status: Needs review » Needs work

The last submitted patch, views-prevent-optimised-count-query-552804-21.patch, failed testing.

dawehner’s picture

I don't think it's a good idea to add a new parameter in the query method as this can cause strict errors.

Alan D.’s picture

No probs. I'd need to have a running test box to see, but I'm sure that the lines of code in query() could be modified to get similar information:

    if ($get_count && !$this->groupby) {
      foreach ($fields_array as $field) {
        if (!empty($field['distinct']) || !empty($field['function'])) {
          $get_count_optimized = FALSE;
          break;
        }
      }
    }

But this one is internal only (i.e. no method signature contract is specified in the views_plugin_query class) and atm this appears to be incomplete from the git version I checked out. (I am cut 'n pasting as git appears to be having major issues creating patches again)

diff --git a/plugins/views_plugin_query_default.inc b/plugins/views_plugin_query_default.inc
index 48460c0..8f4b555 100644
--- a/plugins/views_plugin_query_default.inc
+++ b/plugins/views_plugin_query_default.inc
@@ -917,7 +917,7 @@
     return "";
   }
 
-  function compile_fields($fields_array) {
+  function compile_fields($fields_array, $get_count_optimized = TRUE) {
     $fields = $distinct = array();
     $non_aggregates = array();


# AND in query()
    list($distinct, $fields, $non_aggregates) = $this->compile_fields($fields_array, $get_count_optimized);
 
cravecode’s picture

This is happening on a lot of our views. After doing lots of debugging it is apparent that there isn't really a fix without modifying the views module code or adding hooks later in the SQL generation process. A simple and effective fix for this is to force the count query to use the exact query as the view itself.

Below is what I'm using in my module to correct the problem:

function MODULENAME_views_pre_execute(&$view) {
 if ($view->name == 'VIEW_NAME') {
    $view->build_info['count_query'] = $view->build_info['query'];//Fix for the distinct that is added to the count query.
  }
}
MustangGB’s picture

Issue summary: View changes
Status: Needs work » Closed (won't fix)