locked
Pagination of search results. RRS feed

  • Question

  • User-352524747 posted

    I am having diffuclties with the code below trying to display search results on multiple pages / pagination. It doesn't work.

    @if(IsPost){
        
        bool isValid = true;
        var filled = Request.Form["searchTerm"];
        if (filled.IsEmpty() || filled.Length <= 2) {
            isValid = false;
            Response.Redirect("/search");
        }
    
        var pageSize = 4;
        var totalPages = 0;
        var count = 0;
        var page = UrlData[0].IsInt() ? UrlData[0].AsInt() : 1;
        var offset = (page -1) * pageSize;
    
        var searchwords = Request["searchTerm"].Split(' ');
        IEnumerable<dynamic> result = Enumerable.Empty<string>();
    
        var queryfilter = "SELECT * FROM (SELECT DateD AS [Date], CoD AS Content, 'd' AS Table FROM daily UNION ALL SELECT DateW AS [Date], CoW AS Content, 'w' AS Table FROM weekly UNION ALL SELECT DateM AS [Date], CoM AS Content, 'm' AS Table FROM monthly) AS AllTables WHERE [Date] LIKE @0 OR Content LIKE @0 ORDER BY [Date] DESC OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY";
    
        foreach(var word in searchwords) {
            result = db.Query(queryfilter, "%" + word + "%", offset, pageSize).ToList();
        }
    
        count = result.Count();
        totalPages = count/pageSize;
        if(count % pageSize > 0) {
            totalPages += 1;
        }
    
        <hr>
        <p>@result.Count() results matched</p>
        foreach(var item in result){
            <p>@item.Content<br><small>@item.Date.ToString("D")</small></p>
        }
    
        <p>
        @{
            for (var i = 1; i < totalPages + 1; i++){
                <a href="/search/@i">@i</a>
            }
        }
        Page @page / @totalPages
        </p>
    }


    Tuesday, April 9, 2013 7:48 PM

Answers

All replies

  • User-821857111 posted

    It doesn't work.

    What does that mean? Do you get errors? If so what?

    Wednesday, April 10, 2013 1:03 AM
  • User-352524747 posted

    No errors but if there are 21 results, it displays only 6 of them and @result.Count() = 6 // not 21

    Also at the bottom doesn't display other pages, only:  1 Page 1 / 1 instead of 1, 2, 3, 4 Page 1 / 4

    Wednesday, April 10, 2013 5:54 AM
  • User895691971 posted

    Maybe you are using some kind of WHERE clause. That is causing this issue, and making the server to select only 6 suitable results instead of all 21 results!

    Wednesday, April 10, 2013 11:15 AM
  • User-821857111 posted

    Your logic is incorrect. You are getting the total number of records returned from a query that has a fetch clause, which limits the total to the same amount as pageSize, so count will equal pagesize every time. Then your totalPages variable is assigned the result of dividing count with pagesize (which are the same) which results in 1.

    Wednesday, April 10, 2013 2:20 PM
  • User-352524747 posted

    Your logic is incorrect.

    Yes, this is correct. I removed ..OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY and ..offset, pageSize.. line from the code.

    Is there an example how to paginate the results without webgrid?

    Wednesday, April 10, 2013 4:24 PM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 11, 2013 12:35 AM
  • User-352524747 posted

    Thanks, i think it works now with the code below. I have another problem. My results are being displayed on the same page with search form and because of this pagination doesn't work. If i click second page, it does not displays the results because of if(IsPost){}, if i type the same word in the search form i get the results from second page. How to separate the code from if(IsPost){} block?

    <form method="post" action="" id="search">
    <ul>
        <li><input type="text" name="searchTerm" value="@Request["searchTerm"]"></li>
        <li><a href="#" onclick="$('#search').submit()">Search</a></li>
    </ul>
    </form>
    
    
    @if(IsPost){
    
        var pageSize = 4; 
        var totalPages = 0; 
        var count = 0;
        var page = UrlData[0].IsInt() ? UrlData[0].AsInt() : 1;
        var offset = (page -1) * pageSize;
    
    
        var searchwords = Request["searchTerm"].Split(' ');
        IEnumerable<dynamic> result = Enumerable.Empty<string>();
    
        var queryfilter = "SELECT * FROM (SELECT DateD AS [Date], CoD AS Content, 'd' AS Table FROM daily UNION ALL SELECT DateW AS [Date], CoW AS Content, 'w' AS Table FROM weekly UNION ALL SELECT DateM AS [Date], CoM AS Content, 'm' AS Table FROM monthly) AS AllTables WHERE [Date] LIKE @0 OR Content LIKE @0 ORDER BY [Date] DESC";
    
        foreach(var word in searchwords) { 
            result = db.Query(queryfilter, "%" + word + "%").ToList();
        }
    
        count = result.Count();
        totalPages = count/pageSize;
        if(count % pageSize > 0) {
            totalPages += 1;
        }
        
        queryfilter = "SELECT * FROM (SELECT DateD AS [Date], CoD AS Content, 'd' AS Table FROM daily UNION ALL SELECT DateW AS [Date], CoW AS Content, 'w' AS Table FROM weekly UNION ALL SELECT DateM AS [Date], CoM AS Content, 'm' AS Table FROM monthly) AS AllTables OFFSET @0 ROWS FETCH NEXT @1 ROWS ONLY";
    
        var data = db.Query(queryfilter, offset, pageSize);
         
        <hr>
        <p>@result.Count() results matched</p>
        foreach(var item in data){
            <p>@item.Content<br><small>@item.Date.ToString("D")</small></p>
        }
        <p> @{ for (var i = 1; i < totalPages + 1; i++){ <a href="/search/@i">@i</a> } } Page @page / @totalPages </p>
    }



    Thursday, April 11, 2013 11:16 AM