Public
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
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

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

Its giving error as 


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

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

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

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.

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

Yes,

 

I realized after replying.

 

Thanks for your great response.

@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 ?

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

Welcome to the Technology board!

Curious about our platform? Looking to connect on social technology? You've come to the right place!

Are you a Khoros customer? For direct assistance from our Support team, please visit the Support Forum.