OFFSETs >1000 fail to return anything

[tristansokol]

I have a page that executes a query does some stuff with the results and then refreshes while changing an URL parameter that increases the offset of the query.

I was using queries like SELECT * FROM Artists LIMIT 1 OFFSET 523 with FetchAll() but they started returning empty at 1001. I can query my admin interface with the same query SELECT * FROM Artists LIMIT 1 OFFSET 523 and get the expected result. I have also tried the same general premise with FetchPage(1).

I'm going to try to extract the cursor and pass that between pages.

[tomwalder]

Cursors are MUCH cheaper than offsets. I strongly recommend changing.

Also, there might be a 1000 bug... Do do with default page sizes. I'll look
into it!

On Saturday, 9 July 2016, Tristan Sokol [email protected] wrote:

I have a page that executes a query does some stuff with the results and
then refreshes while changing an URL parameter that increases the offset of
the query.

I was using queries like SELECT * FROM Artists LIMIT 1 OFFSET 523 with
FetchAll() but they started returning empty at 1001. I can query my admin
interface with the same query SELECT * FROM Artists LIMIT 1 OFFSET 523
and get the expected result. I have also tried the same general premise
with FetchPage(1).

I'm going to try to extract the cursor and pass that between pages.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#126, or mute the thread
https://github.com/notifications/unsubscribe/ABMykzecJiAK0rLB9wu6S2uAhvV5yx29ks5qT9nNgaJpZM4JIqYn
.

Tom Walder, CTO
We're hiring. Find out more at www.docnet.nu/jobs
http://www.docnet.nu/jobs?utm_source=email_signature&utm_medium=email&utm_campaign=email_signature
Call: 0161 660 7110 / Web: www.docnet.nu
http://www.docnet.nu/?utm_source=email_signature&utm_medium=email&utm_campaign=email_signature
This message is private and confidential. If you have received this message
in error, please notify us and remove it from your system. Venditan Limited
t/a Docnet is a company registered in England and Wales. Registered number:
9604502. Registered office: Speakers House, 39 Deansgate, Manchester, M3 2BA

[emilorol]

@tristansokol

Can you try something like this:

$results = $obj_store->query("SELECT * FROM Artists");

if (!is_null($results))
 {
      $page_results = $results->fetchPage(1, $offset);
      foreach ($page_results as $page_result)
      {
        // first_name is an example as I don't know your schema
        echo $page_result->first_name;
      }
}

[phpso10]

Experts,
I need your help.
not sure how to get around this issue. Till 1000 records, it is displaying fine, but after 1000, it doesn't work as expected.

--Code--
$results= $obj_store_ns->query("SELECT * FROM opportunities where createdby='ADMIN' );
while($arr_page = $results->fetchPage(100,1000))
{
foreach ($arr_page as $obj_ch) {
echo $obj_ch->title;
}
break;
}

The above code fetches fine until the offset of 1000, but after that it fails.. can someone help me, if I have more than 1000 records, whats the best way ? and whats the function I need to use. Tom says, use Cursors, what I should use from the library ? I understand Fetchall has that 1000 limitation.

Appreciate your help!

[phpso10]

Not sure why this returns only 20 as count --but I have more than 900 records.

$obj_schema = new GDS\Schema('xxaas');
$obj_gateway_ns = new GDS\Gateway\ProtoBuf(null, 'bkdatastore');
$obj_store_ns = new \GDS\Store($obj_schema, $obj_gateway_ns);

$arr_subs = $obj_store_ns->fetchAll("SELECT * FROM xxaas where createdby='6229999BK9999ADMIN' );
$rowCount = count($arr_subs);
echo "FetchAll=" . $rowCount;

--what am I doing wrong ? my understand is that FetchAll should return max of 1000 records.
--I have 2 issues, one with that fetchPage (not sure how to use the Cursors to fetch more than 1000 records) and with FetchAll it returns only 20 as count.

[phpso10]

Is there any help/pseudocode that uses Cursors as Tom suggests? I need this for navigating more than 1000+ records (UI has Next and Previous).

[phpso10]

I think all I need to know is how to use this Query and FetchPage by passing Cursors.
I have inputs as Records_to_Show in one page, Start Index and End Index. (we can calculate offset)

so using this (I could go only far with Fetchpage - pasted the code above) but got stuck how to pass as Cursors.

if anyone can help me with that cursors that would be of great help. This is blocking totally :(

[emilorol]

BTW I was looking at your code and I notice you have

while($arr_page = $results->fetchPage(100,1000)) 

and that means after the record 100 bring the next 1000.

I was under the impression that wanted something like:

while($arr_page = $results->fetchPage(1000,100)) 

Did I get it right?

[phpso10]

Hi Emilorol -- so this is how I tested.
while($arr_page = $results->fetchPage(20,10))
{
foreach ($arr_page as $obj_ch)
{
$recordsperpage= $recordsperpage+1;
}
echo $recordsperpage;
break;
}

This will print 20 records at an offset of 10.

so what I need is I want to pass something like "show 100 records, offset by 1100")
OR something like "show 100 records, Starting RecordIndex, Ending RecordIndex".

[phpso10]

Tristan gave a glimpse of that Cursor code, Now I just need to know how to Pass the Cursor variable to the FetchPage.

$obj_store->setCursor(rawurldecode($_GET['albumoffset']));
$entity = $obj_store->fetchPage(1);

[phpso10]

Okay-- I tried with this below code and works fine as long as you don't pass the Cursor variable. So as soon as I pass the SetCursor Variable to fetchPage(5 , 'cur1') it gives me error.. The strange part is if I pass 5, it gives me 5 records...but if I pass 2000 it gives me only 300 records.
Tom -- Can you please help.

---Code----
$obj_schema = new GDS\Schema('channels');
$obj_gateway_ns = new GDS\Gateway\ProtoBuf(null, 'datastore');
$obj_store_ns = new \GDS\Store($obj_schema, $obj_gateway_ns);
$str_gql = "SELECT * FROM channels";
$obj_request = getBasicFetchRequest();
$obj_gql_query = $obj_request->mutableGqlQuery();
$obj_gql_query->setAllowLiteral(TRUE);
$obj_gql_query->setQueryString($str_gql . " LIMIT @intPageSize OFFSET @startCursor");
$obj_arg = $obj_gql_query->addNameArg();
$obj_arg->setName('intPageSize');
$obj_arg->mutableValue()->setIntegerValue(5);
$obj_arg_offset = $obj_gql_query->addNameArg();
$obj_arg_offset->setName('startCursor');
$obj_arg_offset->setCursor('cur1');
$arr_result = $obj_store_ns->query($str_gql)->fetchPage(5);
foreach ($arr_result as $obj_ch)
{
echo $obj_ch->title, PHP_EOL;
}

[tomwalder]

Hi there,

"Cursors" are not strings that you should set arbitrarily - they are generated by Datastore in response to getting a page of data, so you know where to pick up again for the next page.

Please also see this note regarding supplying your own LIMIT parameter and how it may cause problems
https://github.com/tomwalder/php-gds#tips-for-limit-ed-fetch-operations

Would you like some "general" paging examples? I can put some together.

Tom

[devsterj]

Paging examples on saving/setting the cursors would be appreciated!

[phpso10]

Hi Tom,
Thanks for your email.
So here is my requirement.

1. User sends me the offset number (like 0, 100, 150, 200)
2. I need to take this input and give the resultset in batch of 50 (based on offset he sends me).
3. There are more than 5000 records in the Datastore for that entity.

so I just need a loop/foreach or anything that will work fine for any number of records. The resultset will never return more than 50 per call.

Below code works return 50 records and an offset of 2. but I need to implement using Cursors, so I don't get into this READS/COST/performance problem as you suggested. so if you can translate this code into a way so it uses Cursors, that would be awesome.

$showingcount=0;
while($arr_page = $obj_store_ns_2->fetchPage(50,2)) {
foreach($arr_page as $obj_ch) {
$showingcount=$showingcount+1;
echo $showingcount , PHP_EOL;
echo $obj_ch->subscriptionid, PHP_EOL;

}
}

[phpso10]

Hey Guys,
So here is the code after I had few email conversations with Tom. (Thanks Tom) Hope this would help someone else.

For the first time call, you would simply query, Fetchpage, and then use GetCursor.
and afterwards you would use as below.

$page_results= $obj_store_ns->query("SELECT * FROM logs where createdby='XXX' ORDER BY createdon desc");
$obj_store_ns->setCursor($str_bkcursor); // Tell it to start from the end of the previous page
$page_results = $obj_store_ns->fetchPage(50); // gets the first page
$str_bkcursornext = $obj_store_ns->getCursor();

The callout is, you need to have the composite index in place and its' better (at-least for me) to convert the getcursor to HexStrings and then Cache but for sure, better than offset. (particularly if you are using for Mobile Apps)

Cheers and Thanks everyone for your support.

[yanivyhc]

hi to all,
now after beta of php client is not supported, can i still use cursors ?
the example below will work on new version ?
also i don't understand the last solution posted here, what is : $str_bkcursor ?
in this part: $obj_store_ns->setCursor($str_bkcursor);

can someone give a full sample ?
thanks Yaniv

b.t.w - great work Tom, thank you for this GDS library !!!