Pagination using "hasMore" response

Hi @Gardar 

If you hit the last page where the hasMore is False, what is the pagination result looking like?

Also do you have the Row Scan Depth set to 0?

Hi @Thomas Lind,

The pagination when using LIMIT 2 OFFSET 1615 reaches the end of the list with this pagination result:

"paging": {
        "from": 1616,
        "limit": 2,
        "hasMore": false
    }

I did not have row scan depth set to 0 as the query should only return 2 rows (+ paging result). I now tried to set it to 0 but I get the same error:

The attribute 'hasMore' does not exist.

I may be missing something obvious but is the element mapping really necessary? For basic pagination it should be enough to just add the hasMore field like this:

<attr name="hasMore" xs:type="xs:boolean" readonly="false" other:xPath="/json/paging/hasMore" />

And you may need this:

<api:set attr="Repeatelement" value="/json/entities" />

Can you verify that you are actually getting one page with a result (I expected some kind of authentication first).

@RLB Thank you for the answer. The problem I have is that I need to pass in a LIMIT and OFFSET into the API request. I assume that this type of “Repeatelement” approach would work under normal circumstances. If I do as you have said, I will only get 2 rows as the attribute [AddToURI] is not changing.

Hi @Gardar 

Try this setup.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
  <api:info title="entities" desc="Generated schema file_" xmlns:other="http://apiscript_com/ns?v1"> />
	<attr name="portfolio_id"                                  	xs:type="string"   readonly="false"              other:xPath="/json/entities/Portfolio/id"                                  />
    <attr name="portfolio_name"                                	xs:type="string"   readonly="false"              other:xPath="/json/entities/Portfolio/Name"                                />
	<input name="rows@next" default="0" />
  </api:info>

  <api:set attr="DataModel" value="FLATTENEDDOCUMENTS" />
  <api:set attr="BASEURI" value="https://apie1.clarizen.com/V2.0/services/data/query" />
  <api:set attr="JSONPath" value="$.entities.Portfolio" />
  <api:set attr="ElementMapPath#" value="/json/paging/hasMore" />
  <api:set attr="ElementMapName#" value="hasMore" />
  <api:set attr="EnablePaging" value="TRUE" />

  <api:script method="GET">
	<api:set attr="AddToURI" value="q=SELECT+Portfolio.Name+FROM+Portfolio+LIMIT+2+OFFSET+[_input.rows@next]"/>
	<api:set attr="URI" value="[BaseURI]?[AddToURI]" />
	<api:set attr="method" value="GET"/>
    <api:call op="jsonproviderGet">
		<api:if attr="hasMore" value="true" operator="equals">
			<api:set attr="rows@next" value="[rows@next | Add(2)]" />
		</api:if>
      <api:push/>
    </api:call>
  </api:script>

</api:script>

What I did was to add a JSONPAth field to point at the data. This may be part of the issue, also consider changing the DataModel to just DOCUMENTS.

Thanks @Thomas Lind. I tried your suggestion but the error stayed the same. I changed the JSONPath to:

<api:set attr="JSONPath" value="$." />
And it seems to work “somewhat” now. I changed it to LIMIT 1000 and 

[rows@next | Add(1000)]

As there are >1600 rows to fetch, I see in the log_file (with verbosity = 5) that it is doing the correct thing:

GET https://apie1.clarizen.com/V2.0/services/data/query?q=SELECT+Portfolio.Name+FROM+Portfolio+LIMIT+1000+OFFSET+0

followed by:

GET https://apie1.clarizen.com/V2.0/services/data/query?q=SELECT+Portfolio.Name+FROM+Portfolio+LIMIT+1000+OFFSET+1000

And I see in the buffer log that it is fetching all the data.

But it seems that it only returns the first row of each query. Selecting from the portfolio table in TimeXtender returns only 2 rows.

Hi @Gardar 

It likely still is the JSONPath that is the issue.

$. is the highest level it may not return more than one row. See if you can try other options like $.entities and similar

Hi @Gardar 

When you use FlattenedDocuments as the DataModel adding multiple options in the JSONPath field is normally what is expected. So using ; between statements pulls all together in one file.