Limit clause throwing unexpected errors via Query JSON with SQL

Queries and Resources
1

Hi all,

I'm getting unexpected errors with a query that I'm trying to set-up (Query JSON with SQL).

This is working as expected:

select * from {{formatDataAsArray(import_all_results.data)}} 
limit 10

The following doesn't work:

select * from {{formatDataAsArray(import_all_results.data)}} 
limit (10)

--> Expecting 'NUMBER', got 'LPAR'"

Neither does this (this is similar to what I eventually want to use, for pagination on a listview container, with limit+offset):
limit 10 offset {{pageInput1.value}} or limit 10 offset {{parseInt(pageInput1.value)}}
(pageInput1.value evaulates to 1 in this case)
--> Expecting 'NUMBER', got 'QUESTION'"

Or any combination of parse to int, either in javascript within the {{}} or in SQL with CAST or CONVERT. It only works if I type a number directly.

I'm not sure whether I'm doing something I shouldn't with the query or whether this is some sort of bug? Does anyone have a workaround? Thanks!

2

Check out this doc

3

Thanks for the reply @ScottR . I've checked out the link to the doc you sent me as well as the 3 external links to the AlaSQL docs, but haven't been able to solve this yet or see anything that would lead me to think that this is not supported.

The limit and offset clause work perfectly as long as I enter values in the SQL statement itself.

So this is working correctly and as expected:
limit 10 offset 10

The issue is when I try to use not a fixed number but when I refer to a number from somewhere else. So these are not working, to give you some examples:
limit 10 offset {{Number(10)}}
limit 10 offset {{10}}
limit 10 offset {{pageInput1.value}}
etc

So I guess my question becomes, is there anyway to refer to a value instead of typing a static value (just as I'm able to do with for example the WHERE clause etc)?

Thanks :pray:

4

Found this thread with the same issue, looks like this might be an AlaSQL issue.
Unfortunate, since it is, well, limiting (:stuck_out_tongue_closed_eyes:)

For now this looks like it's going to work:

image
image1554×140 33.7 KB

First value is the offset, second is the limit

Edit:
Spoke a little too soon, here it what ended up working in my particular case for pagination:

image
image1540×176 48.5 KB

The two values of the slice method refer to the start and end index of the array to return (more info here). In my example, the first value is the offset, and the limit behavior is achieved by using offset + the number of values you want to return.

1 Like
5

This led me in the right direction.

For my particular use case we were not able to user server-side pagination since our query included several filters and CTEs. I ended up paginating the resulting dataset through a transformer:

const data = {{ myquery.data }} // formatted as an array
const pageSize = {{ table.pageSize }}
const offset = {{ table.paginationOffset }}
  
return formatDataAsObject(data.slice(offset, offset + pageSize))

Main reason was that dataset contained thousands of rows sometimes and using this prevents the tab from crashing.

1 Like
6

ey can you help me? im having i think the same issue as yours Pagination Offset in Query JSON with SQL - #7 by agaitan026 in this post i got the details