Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
Is there a way to set a limit to the total number of results returned while also using a pager? Say I want to pull back the top 100 nodes out of 200, but I want to use a pager to group them by 25 per page, a total of 4 pages. Is there a way to set this limit - through the interface or code?
Comments
Comment #1
merlinofchaos CreditAttribution: merlinofchaos commentedCurrently there is not a way to achieve this.
Comment #2
dropchew CreditAttribution: dropchew commentedCan this be a feature request? I guess this will be a valuable feature for performance boost
Comment #3
nevets CreditAttribution: nevets commentedI am pretty sure that this would not produce any performance improvement. The database engine handles getting the next N records from the database so each page only gets the needed records.
Comment #4
dropchew CreditAttribution: dropchew commentedOic, than I have misunderstood. Thanks for clearing out. Resetting category.
Comment #5
dropchew CreditAttribution: dropchew commentedI did a test with devel with ard 5000+ nodes and uses paging of 20 items per page.
- Result as follows, Page execution time was 9826.73 ms. Executed 734 queries in 7824.13 milliseconds.
Did some filtering on the view and limit the results to ard 1000+ nodes and uses paging of 20 items per page.
- Result as follows, Page execution time was 4628.19 ms. Executed 735 queries in 2273.16 milliseconds.
It executes the same query but execution time was more than half. Sorry but pls help me to understand, wouldn't the total no of items return from the query affects server load?
Comment #6
nevets CreditAttribution: nevets commentedYes, the total number of items returned by a query can affect the server load. That said when the results from views are paged 20 items to a page, the query only returns the 20 items. As for your results, without more details, it is hard to comment.
Comment #7
dropchew CreditAttribution: dropchew commentedActually both results are return from the 1st page of 20 items using the same view (same fields, args, relation etc), just that the 1st result has a total of 5000+ nodes with more pages, and the 2nd result, with some added filtering has total of 1000+ nodes with less pages.
I tried a few times, clearing cache etc, and it gives a the conclusion that the more pages a view has, the longer it takes to load each page, despite executing the same number of queries for the 2 test cases....
Comment #8
nsciaccaThe reason you're seeing an improvement in the total time is the count query -- the pager query needs to figure out how many results there are, and then how to divide that among the number per page. So when you add additional filters, you are limiting down the results returned by the count query, making the overall load time faster, but not necessarily speeding up the pager query. What needs to be implemented is the core Drupal pager query function needs to be able to take a max limit, so when it does the count query, it forces a LIMIT and you get back the smaller of the two: the max limit or the total count. Then, the pager query can run as it did.
A possible solution would be for Views to take in a parameter for the max results and then append a LIMIT to the count query when passing it to the pager query.
Comment #9
nevets CreditAttribution: nevets commentedThe count query returns the count (a single row).
Comment #10
dropchew CreditAttribution: dropchew commentedHi nsciacca,
I agree with you. Here's another finding I made,
Created a grid page view with 20 page items with around 800+ pages total up to 16K+ nodes.
Ok, there's some diff as its expected with the sort option.
Comparing the 2nd and 4th block of results, with the same sort option and diff by a node:title field, it seems node.title field and another sort option would lead to query time by ard 50% more.
Anyone can confirm this on D6.8? I tried on a few other diff views and it gives me similar results. Btw it won't show much if you just have node:title as your field.
Edit. I have open a issue http://drupal.org/node/460524 and hope someone will participate in the testing.
Comment #11
nsciaccadropchew - regarding my original post, the idea is that Views should be able to say what the maximum results are and can use the count query to specify this. It's not really a question of performance, but of course the smaller the number of nodes, the quicker the results. This feature would allow you to get the top 100 of xyz nodes and still utilize the pager function.
nevets - the count query only returns a single row, but the query itself still has to find all the matching nodes and can take a LIMIT parameter... just because it doesn't return all the data, doesn't mean that there isn't time spent finding all the matches... but like I said above, it's not about the speed, it's the functionality that I would like see implemented.
I've already added the change to my system - unfortunately I can't get it into the Views interface so I just added it to myhook_views_pre_execute to append the $view->build_info['count_query'] variable.
Comment #12
dropchew CreditAttribution: dropchew commentedHi nsciacca,
Thanks for the explanation. I would also like to implement this feature myself but I am not an established coder... I Will it be possible for you to share the code or patch?
Comment #13
nsciaccaOkay - I'm going to assume you know how to create a custom module, if not - see this guide
For reference below, my module is called "viewsmaxlimit"
File: viewsmaxlimit.module
The "hook_views_pre_execute" is run before the building of every view, so the important part is the if check --- in this case I've limited the change to only work on the view I have named "catalog" and apply to two of the displays. You may have something as simple as just checking the $view->name - depending on which views you want to affect. If you're having trouble figuring out what $view->name and $view->current_display to check, just print them out via:
Then just visit the page with the view that you want to change and it should be displayed at the top -- note that printing out these variables temporarily breaks the AJAX preview at the bottom of the edit view screen. If you have issues - just post your code here and I'll try to help out. Good luck.
Comment #14
nsciaccaPlease see my latest reply to this issue -- it is achievable via code in the 'hook_views_pre_execute' by modifying the $view->build_info['count_query'] variable. I think it's something pretty straightforward that may be worthwhile to include in future implementations of Views. Many thanks for all the hard work you put into developing these modules.
Comment #15
dropchew CreditAttribution: dropchew commentedThanks! This really helps. I had performance issue when displaying like 1000+ of pages but once reducing it to 10-20+ pages makes my headache gone.
Edit: Its not really performance issue as you mentioned, my bad. It more like speeds up the page loading once I reduce the no. of pages to display.
Comment #17
babymission CreditAttribution: babymission commentedHi nsciacca,
Thanks for your solution. But I tried to follow your instruction to create a module with the following code
<?php
function viewsmaxlimit_views_pre_execute(&$view) {
// set limit to 2
if ($view->name == 'song1') {
$view->build_info['count_query'] .= ' LIMIT 2';
}
}
But it returns a blank page right after I enabled the module....could you kindly advise what was wrong?
Comment #18
gausarts CreditAttribution: gausarts commentedSubscribing. Thanks
Comment #19
mcarrera CreditAttribution: mcarrera commentedsubscribe
Comment #20
ferrangil CreditAttribution: ferrangil commentedSubscribing. Performance might be the same, but I don't want to have pagers up to 2780 pages, so it would be great to have some kind of limit. Think about a youtube site listing the top rated videos (without limiting the total results, it would also list the worst rated videos, on the last page..).
Comment #21
SchnWalter CreditAttribution: SchnWalter commentedany progress on this?
Comment #22
merlinofchaos CreditAttribution: merlinofchaos commentedhttp://drupal.org/node/268023
Comment #23
SchnWalter CreditAttribution: SchnWalter commentedthank you...
Comment #24
danieldd CreditAttribution: danieldd commentedIf anyone is wondering the custom module in #13 works exactly as intended and solves this issue. Ie for any view you specify it will limit the results to whatever figure you set.
Thanks, extremely helpful.
Comment #25
ccrackerjack CreditAttribution: ccrackerjack commentedI second that the custom module in #13 is the simplest solution so far to limit the total results for views 2 when using pager is necessary.
For anyone that may interest, my sample of code is:
function viewsmaxlimit_views_pre_execute(&$view) {
// apply to the block view of the all_activity module - limit to latest 75
if ($view->name == 'all_activity' && in_array($view->current_display, array('block_1'))) {
$view->build_info['count_query'] .= ' LIMIT 75';
}
}
Views 3 will has this option build in, its currently in alpha stage.
Comment #26
bflora CreditAttribution: bflora commentedCan I just toss this function into my template.php file instead of adding yet another module to my stack?
Comment #27
altavis CreditAttribution: altavis commentedThank you nsciacca. Extremely useful.
Comment #28
mhefernan CreditAttribution: mhefernan commentedGreat this does exactly what i was looking for nsciacca. Nice one champ.