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

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 datamodel, SearchContexts etc.

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

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

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

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

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.

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

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