Public
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Highlighted
Advisor

Pagination in REST-API using LiQL query

I have REST-API for LiQL query where I'm fetching information from the USER table. unfortunately every time I'm getting only 25 results.
I tried with parameter 'page = 1' and 'page_size = 100' but still it is giving the same result.

 

can someone help me for below rest API:

https://api.lithium.com/community/2.0/******/search?api.pretty_print=true&client-id=******&q=SELECT id,email,sso_id FROM users&page=1&page_size=100

 

how can I fetch results more than a default 25?

 

Thanks in Advance. 

16 Replies 16
Highlighted
Honored Contributor

@hpanmand - In V2 call, you have to use the Limit parameter instead of page_size.

 

https://api.lithium.com/community/2.0/******/search?api.pretty_print=true&client-id=******&q=SELECT id,email,sso_id FROM users LIMIT 100

 

Let me know if this helps.

 

Thanks

Highlighted

Thanks Payal,
but it is not working with LIMIT more than 100,000.

Its giving error as 


"status": "Gateway timeout",
"message": "",
"statusCode": 504

Highlighted
Honored Contributor

@hpanmand - Max limit is 1000. To increase the limit more than 1000, you need to raise the ticket to Lithium Support .

 

Thanks,

Payal

Highlighted
Honored Contributor

@hpanmand

In release 17.9 Lithium added a configuration which sets the maximum value allowed for LIMIT in LiQL queries to 1000. This is to help performance and protect against malicious attacks. I strongly recommend you to keep the limit 1000. 
Instead, you can use the below method to get all the data

<#assign count = rest( "2.0", "/search?q=" + "SELECT count(*) FROM users"?url).data.count />
<#if count&lt;1000>
  <#assign n = 0 />
<#else>
  <#assign n = (count/1000)?ceiling />
</#if>
${n}
<#list 0..n-1 as offsetValue>
<#assign users = rest( "2.0", "/search?q=" + "SELECT id,email,sso_id from users limit 1000 offset ${offsetValue*1000}"?url).data.items />

<#list users as user> 
	${user.id},
</#list>
</#list>
Give kudos if you find my posts helpful or mark solution if it answers your query
Highlighted

Thanks Vikas your suggestion is appreciated.

One question!
I was using
https://api.lithium.com/community/2.0/*****/search?api.pretty_print=true&client-id=****&q=SELECT id,email,sso_id FROM users offset 500

but still, it was giving me only 25 records.

Highlighted
Honored Contributor

@hpanmand

You need to use 'limit' not offset. 
https://api.lithium.com/community/2.0/*****/search?api.pretty_print=true&client-id=****&q=SELECT id,email,sso_id FROM users limit 500
Offset is required when you want to get the next 500 results, then query will be like this

https://api.lithium.com/community/2.0/*****/search?api.pretty_print=true&client-id=****&q=SELECT id,email,sso_id FROM users limit 500 offset 500

Give kudos if you find my posts helpful or mark solution if it answers your query
Highlighted

Yes,

 

I realized after replying.

 

Thanks for your great response.

Highlighted

@VikasB

Hey!

I tried with your solution but its giving duplicate records as we proceed ahead by increasing offset value.

@Payal Is there no another way by which we can retrieve all the records of LiQL Query using REST-API ?

Highlighted
Honored Contributor

@hpanmand

It seems your offset value is not correct.  Can you please share the code so I can figure out the issue. 

Give kudos if you find my posts helpful or mark solution if it answers your query