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

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
Director

@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

Payal Uppal

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

Its giving error as 


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

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

 

Thanks,

Payal

Payal Uppal

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

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

@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

@VikasB@Payal
Is it possible to retrieve the user records without using OFFSET parameter?

Have you considered adding a sort? Like the below so that the list is in order of registration date: 

 

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 ORDER BY registration_data.registration_time DESC

 


Dropbox Community Developer
Check out our submission for the 2022 Customer Awards and please consider giving us a Kudos!

This is giving me as Invalid Query in postman.

 

{
"status": "error",
"message": "invalid query syntax for SELECT id, email, sso_id FROM users limit 500 offset 500 ORDER BY registration_data.registration_time DESC",
"data": {
"type": "error_data",
"code": 603,
"developer_message": "",
"more_info": ""
},
"metadata": {}
}

Sorry, try this one:

SELECT id,email,sso_id FROM users ORDER BY registration_data.registration_time ASC LIMIT 500

 

and then this one for the OFFSET:

SELECT id,email,sso_id FROM users ORDER BY registration_data.registration_time ASC LIMIT 500 OFFSET 500

Dropbox Community Developer
Check out our submission for the 2022 Customer Awards and please consider giving us a Kudos!

Thanks,

one question, why its not working with 

SELECT id,email,sso_id FROM users ORDER BY id ASC LIMIT 500

 

is there something I'm doing wrong?

You can't order by 'id', there's only a few 'sorts' that you can use for users. 

See here for the list - https://community.lithium.com/t5/Developer-Documentation/bd-p/dev-doc-portal?section=commv2&collecti...


Dropbox Community Developer
Check out our submission for the 2022 Customer Awards and please consider giving us a Kudos!

Thanks SheenaK.

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.