Jump to:

3460 Posts in 1064 Topics by 739 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Search Results Pagination Problems

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1 2
Go to End
Author Topic: 4502 Views
  • Garrett
    Avatar
    Community Member
    245 Posts

    Search Results Pagination Problems Link to this post

    Hi,

    I cloned the Pagination (PHP + SS) code from the Blog module and am attempting to use it on a different DataObject (some custom search results), and am having considerable trouble getting it to work.

    In the blog module, in which BlogHolder.php uses DataObject::get() to get the pages, the pagination works fine. I am echoing "$this->totalSize" from DataObjectSet.php and seeing that the total number of results is accurate. However, my custom search uses DB::query(), and although I am returning the data AS a DataObjectSet, "$this->totalSize" is totally off. It's saying the size is 12 even though there are 29 results. Obviously, if I don't know the total number of items, the paging won't work.

    I don't see the difference in how the data is being returned here. Any ideas??

    Thanks,
    Garrett

  • Hamish
    Avatar
    Community Member
    712 Posts

    Re: Search Results Pagination Problems Link to this post

    If you use DB::query you are going to get unpredictable results.

    Edit: not quite true

    The easy answer is to stick to the SilverStripe datamodel, SearchContexts etc.

    Is there any particular reason you need to use DB::query?

  • Garrett
    Avatar
    Community Member
    245 Posts

    Re: Search Results Pagination Problems Link to this post

    Thanks very much for your reply.

    Yes. I need to use DB::query() because my query is too complex for DataObject.get(), my SELECT statement in particular. There is no way to modify the SELECT statement in calling that method. That said, I think I figured out WHY my pagination is broken. It's because of my LIMIT clause. In my search results function:

    function NewsroomSearchHolder($limit = 12) {

    $start = isset($_GET['start']) ? (int)$_GET['start'] : 0;
    $results = new DataObjectSet();
    ........

    // then later in the SQL itself...

    LIMIT ".$start.", ".$limit."
    }

    I think the problem is that the limit stays at 12 no matter where you page to, so the results are crazy. In the initial query, the LIMIT clause is "LIMIT 0, 12", which is great, but when I click on Page 2, I think what I am getting back is "LIMIT 12, 12". $start I can modify in DataObjectSet.php:

    if($this->pageLength == 0) {
    $this->pageLength = 12;
    }

    But I don't know how this $limit is maintained. Do you?

    Thanks again,
    Garrett

  • Hamish
    Avatar
    Community Member
    712 Posts

    Re: Search Results Pagination Problems Link to this post

    Ok, you want to add this to your set:

    $set->parseQueryLimit($query);

    That is, you pass the query you created, back the dataobjectset. This allows SilverStripe to figure out the rest.

    Eg, to take some actual code from one of my projects:

    ...
    $query = new SQLQuery($select, $from, $filters, $order_SQL, "", "", $limit_SQL);
    $result = $query->execute();
    $assessments = singleton('Assessment')->buildDataObjectSet($result);
    if($assessments)
       $assessments->parseQueryLimit($query);

  • Garrett
    Avatar
    Community Member
    245 Posts

    Re: Search Results Pagination Problems Link to this post

    When I call that method on my $results DataObjectSet, I get the following error:

    Notice: Trying to get property of non-object in ....sapphire\core\model\DataObjectSet.php on line 225

    I guess I have to use SQLQuery() instead of DB::query() for this?

    Thanks,
    Garrett

  • Hamish
    Avatar
    Community Member
    712 Posts

    Re: Search Results Pagination Problems Link to this post

    Yes, SQLQuery->execute() creates the query object that would otherwise be created by DB::query, but adds all the extra sugar needed to do what you want.

  • Garrett
    Avatar
    Community Member
    245 Posts

    Re: Search Results Pagination Problems Link to this post

    Hey, thanks a lot Hamish. Very kind of you to take this time to help me out. One more question-- can all my other code pretty much remain the same? In other words, after the execute() call, can I then iterate through the resultset as follows:

    foreach($Search as $sqlResult) {
    $Title = $sqlResult['Title'];
    //.............etc
    }

    ?

    And then I do the parseQueryLimit() AFTER I get the resultset but BEFORE returning it to the browser?

    //Garrett

  • Garrett
    Avatar
    Community Member
    245 Posts

    Re: Search Results Pagination Problems Link to this post

    This is all working now-- thanks to you! In the end, I found that I did not need to use buildDataObjectSet(). I'm just stuffing the result from the SQLQuery() into a set, doing the parseLimit and then feeding that back to the client.

    Voila-- custom paging! Thanks again!

    //Garrett

    4502 Views
Page: 1 2
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.