MySQL LIMIT with ProcessWire API

[kongondo]

This topic is similar to this but wasn't sure whether to post there or start a new thread. I have tried solutions posted there without success. 

I am trying to use the MySQL pagination feature LIMIT (range results, e.g. SELECT * FROM `your_table` LIMIT 0, 10 ) where I'll limit the actual results returned in a query. I cannot use PW "limit" since that will limit the total number of results returned. I am trying to use the MySQL LIMIT with PW API as follows but I'm getting errors. At the moment, I am trying to query a PW database as follows:

$start = 0;
$limit = 15;

$values = $pages->find("parent=/"."LIMIT". $start ."," . $limit);

How do I go about this? Many thanks. This is similar to what Soma does in the module DataTable as follows:

$pa = $this->pages->find($selector."start=".$_REQUEST['iDisplayStart'].",limit=".$_REQUEST['iDisplayLength']);

In this case he's getting start and limit values from an ajax request (I think). The idea is that more results (pagination) can be loaded on demand.

Thanks.

[diogo]

The find method only accepts the pw selectors. What you are doing there seems very different to me to what Soma did in that example. The way Soma concatenated that string I can see that it will be something like this (just guessing, i didn't look at his code);

// $selector = any selector f.ex. "template=basic-page,"

// $_REQUEST['iDisplayStart'] = 10

// $_REQUEST['iDisplayLength'] = 20

$pa = $this->pages->find("template=basic-page, start=10, limit=20");

which is a valid selector.

In your selector the result of the concatenation would be something like this, right?:

$values = $pages->find("parent=/LIMIT0,15"); // ??

You can't use regular mySQL queries because they won't be recognized by the method. If you want to use queries you can use query() http://processwire.com/talk/topic/2209-display-data-from-mysql-table/?p=20630

[kongondo]

OK. Thanks Diogo...I will go the $db-query way.

[teppo]

I am trying to use the MySQL pagination feature LIMIT (range results, e.g. SELECT * FROM `your_table` LIMIT 0, 10 ) where I'll limit the actual results returned in a query. I cannot use PW "limit" since that will limit the total number of results returned. I am trying to use the MySQL LIMIT with PW API as follows but I'm getting errors.

I'm not really sure I get what you're trying to do here and what's wrong, but isn't this essentially what you're describing there:

$start = 0;
$limit = 15;

$values = $pages->find("parent=/, start=$start, limit=$limit") 

If you need total count of possible results (without limit) you could always use $pages->count() to do that separately:

$total = $pages->count("parent=/");

// or like @nik pointed out below:
$total = $values->getTotal();

Edited April 15, 2013 by teppo

[nik]

@kongondo: It looks like you're trying to implement something that's in core already. Take a look at http://processwire.com/api/selectors/, especially "Limiting the number of results returned by a selector". There's a description of limit, start and even end, should you ever need it.

In short: enable page numbers for your template and use only limit without start in your selector. You can get the total number or rows matched from $values->getTotal() and even render a pager using $values->renderPager() if you like.

[kongondo]

Hi Nik & Teppo,

Thanks for your responses. I think I didn't express myself clearly and got confused. What I was trying to do in the end is to be able to query an external database. Yes, in the example above I was querying PW tables.

Aaah, I knew about PW start, limit and end selectors but thought they wouldn't do what MySQL LIMIT does. 

My understanding then is that if the tables are in PW, use PW selectors. If the tables are external, use PW database class (or other database abstraction layer e.g. ezSQL ), correct? Many thanks.

[nik]

Yes, something like that. I'd put it this way:

• When using tables in another database, PW selectors can't help you so just use plain SQL. If you like, you can use PW's Database class for connecting, some escaping and debug timers, but other means of getting there are ok too.
• When using data from pages in PW, use PW selectors and you'll get most likely the best performance with all the tricky things like grants taken care of.
  • If the selector engine can't offer what you need, you're still able to use plain SQL. But go down that road only if you really need to and preferably know what you're doing .

[kongondo]

Thanks Nik