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

merlinofchaos’s picture

Status: Active » Fixed

Currently there is not a way to achieve this.

dropchew’s picture

Category: support » feature

Can this be a feature request? I guess this will be a valuable feature for performance boost

nevets’s picture

I 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.

dropchew’s picture

Category: feature » support

Oic, than I have misunderstood. Thanks for clearing out. Resetting category.

dropchew’s picture

I 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?

nevets’s picture

Yes, 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.

dropchew’s picture

Actually 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....

nsciacca’s picture

The 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.

nevets’s picture

The count query returns the count (a single row).

dropchew’s picture

Hi 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.

Put in some fields (node,cck fields but not node:title) with no sort options
Pg 1 Result: Page execution time was 1562.5 ms. Executed 152 queries in 759 milliseconds
Pg 2 Result: Page execution time was 1578.12 ms. Executed 151 queries in 835.24 milliseconds.
pg 3 Result: Page execution time was 1593.75 ms. Executed 151 queries in 1027.31 milliseconds
Same fields with node:post date(desc) sort option.
Pg 1 Result: Page execution time was 1625 ms. Executed 153 queries in 1228.94 milliseconds
Pg 2 Result: Page execution time was 1708.11 ms. Executed 151 queries in 1128.56 milliseconds
Pg 3 Result: Page execution time was 1625 ms. Executed 151 queries in 1277.31 milliseconds.

Ok, there's some diff as its expected with the sort option.


Same fields and added node:title with no sort options
Pg 1 Result: Page execution time was 1578.12 ms. Executed 152 queries in 1186.81 milliseconds.
Pg 2 Result: Page execution time was 1640.62 ms. Executed 151 queries in 1043.71 milliseconds.
pg 3 Result: Page execution time was 1631.16 ms. Executed 151 queries in 926.17 milliseconds.
Same fields and added node:title with node:post date(desc) sort option.
Pg 1 Result: Page execution time was 3176.6 ms. Executed 152 queries in 2323.55 milliseconds.
Pg 2 Result: Page execution time was 2781.25 ms. Executed 151 queries in 2238.79 milliseconds
Pg 3 Result: Page execution time was 2781.25 ms. Executed 151 queries in 2350.15 milliseconds.

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.

nsciacca’s picture

dropchew - 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.

dropchew’s picture

Hi 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?

nsciacca’s picture

Okay - 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

function viewsmaxlimit_views_pre_execute(&$view) {
     // bestsellers - limit to top 50
     if ($view->name == 'catalog' && in_array($view->current_display, array('panel_pane_3','panel_pane_4'))) {
	$view->build_info['count_query'] .= ' LIMIT 50';
     }
}

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:

print $view->name .'<br/>';
print $view->current_display;

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.

nsciacca’s picture

Please 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.

dropchew’s picture

Thanks! 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.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

babymission’s picture

Hi 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?

gausarts’s picture

Subscribing. Thanks

mcarrera’s picture

subscribe

ferrangil’s picture

Subscribing. 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..).

SchnWalter’s picture

Version: 6.x-2.5 » 6.x-2.7
Component: Documentation » Views Data
Category: support » feature
Priority: Normal » Minor
Status: Closed (fixed) » Active

any progress on this?

merlinofchaos’s picture

Status: Active » Closed (duplicate)
SchnWalter’s picture

thank you...

danieldd’s picture

If 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.

ccrackerjack’s picture

I 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.

bflora’s picture

Can I just toss this function into my template.php file instead of adding yet another module to my stack?

altavis’s picture

Thank you nsciacca. Extremely useful.

mhefernan’s picture

Great this does exactly what i was looking for nsciacca. Nice one champ.