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
Comment | File | Size | Author |
---|---|---|---|
#21 | views-prevent-optimised-count-query-552804-21.patch | 3.55 KB | Alan D. |
#15 | 552804-views-1-D6.patch | 2.11 KB | kndr |
screenshot1.png | 132.53 KB | TimG1 |
Comments
Comment #1
gpk CreditAttribution: gpk commentedYes 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?
Comment #2
TimG1 CreditAttribution: TimG1 commentedHi 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
Comment #3
gpk CreditAttribution: gpk commentedI 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?
Comment #4
TimG1 CreditAttribution: TimG1 commentedHi 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
Comment #5
dawehnerIt would be cool if someone could describe what for kind of cck and what for kind of views configuration you need to reproduce it
Comment #6
gpk CreditAttribution: gpk commentedI'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
Comment #7
gpk CreditAttribution: gpk commentedComment #8
merlinofchaos CreditAttribution: merlinofchaos commentedThe 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.
Comment #9
gpk CreditAttribution: gpk commentedAh, that would follow ... when does core add the DISTINCT? I can't see anything obvious in pager_query() that would cause this.
Comment #10
merlinofchaos CreditAttribution: merlinofchaos commentedIt happens in node_db_rewrite_sql() whenever there are node access modules. It forces every node query to distinct.
Comment #11
gpk CreditAttribution: gpk commentedInteresting... 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!
Comment #12
gpk CreditAttribution: gpk commentedHmm 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..
Comment #13
enkara CreditAttribution: enkara commentedI 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):
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.
Comment #14
Matt BI'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?
Comment #15
kndrI 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.
Comment #16
dawehnerWe are talking about 7.x, right?
It might be helpful to have this as setting as well.
Comment #17
merlinofchaos CreditAttribution: merlinofchaos commentedThat'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.
Comment #18
kndrdereine: 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.
Comment #19
kndrmerlinofchaos: 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.
Comment #20
dawehnerMove back to 6.x. Additional it does not apply to 6.x-3.x anymore.
Comment #21
Alan D. CreditAttribution: Alan D. commentedMuch 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.
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.
Comment #22
Alan D. CreditAttribution: Alan D. commentedFor fun, lets see if it passes any tests
Comment #24
dawehnerI don't think it's a good idea to add a new parameter in the query method as this can cause strict errors.
Comment #25
Alan D. CreditAttribution: Alan D. commentedNo 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:
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)
Comment #26
cravecode CreditAttribution: cravecode commentedThis 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:
Comment #27
MustangGB CreditAttribution: MustangGB commented