Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Search Results Pagination Problems


Go to End
Reply


10 Posts   4692 Views

Avatar
Garrett

Community Member, 245 Posts

13 August 2009 at 9:19am

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

Avatar
Hamish

Community Member, 712 Posts

13 August 2009 at 9:56am

Edited: 13/08/2009 10:29am

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

Edit: not quite true :P

The easy answer is to stick to the SilverStripe [url=http://doc.silverstripe.org/doku.php?id=datamodel]datamodel[/url], [url=http://doc.silverstripe.org/doku.php?id=searchcontext]SearchContexts[/url] etc.

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

Avatar
Garrett

Community Member, 245 Posts

13 August 2009 at 10:07am

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

Avatar
Hamish

Community Member, 712 Posts

13 August 2009 at 10:26am

Edited: 13/08/2009 10:26am

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);

Avatar
Garrett

Community Member, 245 Posts

13 August 2009 at 10:40am

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

Avatar
Hamish

Community Member, 712 Posts

13 August 2009 at 11:02am

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.

Avatar
Garrett

Community Member, 245 Posts

14 August 2009 at 1:58am

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

Avatar
Garrett

Community Member, 245 Posts

14 August 2009 at 4:53am

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

Go to Top