Server Side Pagination Issues

App Building
,
1

Hi!

I've successfully set up basic server-side pagination - I'm trying to implement sorting and am stuck.

Scenario one:

WITH
  cteCustomers AS (
    SELECT
      *
    FROM
      DEVELOPMENT_SCHEMA.CUSTOMERS
  ),
  cteUnits AS (
    SELECT
      *
    FROM
      DEVELOPMENT_SCHEMA.UNITS
  )
SELECT
  e.ID,
  c.CUSTOMER_NAME AS "Customer",
  (
    object_construct ('ID', u.UNIT_ID, 'NAME', u.UNIT_NAME)
  ) AS "Unit",
  e.ENTRY_DATE AS "Operation Date",
  e.UNIT_TARGET_ENTRY AS "Entry",
  e.ADDED_BY_USER
FROM
  DEVELOPMENT_SCHEMA.DAILY_DATA_ENTRY e
  INNER JOIN cteCustomers c ON e.CUSTOMER_ID = c.CUSTOMER_ID
  INNER JOIN cteUnits u ON e.UNIT_ID = u.UNIT_ID
ORDER BY 
  {{ DAILY_ENTRIES.sortArray[0]?.columnId || 'ENTRY_DATE' }} {{ DAILY_ENTRIES.sortArray[0]?.direction || 'DESC' }} 
LIMIT
  {{ DAILY_ENTRIES.pagination.pageSize }}
OFFSET
  {{ DAILY_ENTRIES.pagination.offset || 0 }}

Here I get the following error: SQL compilation error: syntax error line 28 at position 6 unexpected '2'.

Scenario 2, based on some responses from the forums:

WITH
  cteCustomers AS (
    SELECT
      *
    FROM
      CUSTOMERS
  ),
  cteUnits AS (
    SELECT
      *
    FROM
      UNITS
  )
SELECT
  e.ID,
  c.CUSTOMER_NAME AS "Customer",
  (
    object_construct ('ID', u.UNIT_ID, 'NAME', u.UNIT_NAME)
  ) AS "Unit",
  e.ENTRY_DATE AS "Operation Date",
  e.UNIT_TARGET_ENTRY AS "Entry",
  e.ADDED_BY_USER
FROM
  DAILY_DATA_ENTRY e
  INNER JOIN cteCustomers c ON e.CUSTOMER_ID = c.CUSTOMER_ID
  INNER JOIN cteUnits u ON e.UNIT_ID = u.UNIT_id
ORDER BY
  CASE
    WHEN {{ !DAILY_ENTRIES?.sortArray[0] }} THEN ENTRY_DATE
  END DESC,
  CASE
    when {{ DAILY_ENTRIES?.sortArray[0]?.columnId == 'ENTRY_DATE' }}
    AND {{ DAILY_ENTRIES?.sortArray[0]?.direction != 'asc'}} then ENTRY_DATE
  end DESC,
  CASE
    when {{ DAILY_ENTRIES?.sortArray[0]?.columnId == 'ENTRY_DATE' }}
    AND {{ DAILY_ENTRIES?.sortArray[0]?.direction == 'asc'}} then ENTRY_DATE
  end ASC
LIMIT
  {{ DAILY_ENTRIES.pagination?.pageSize }}
OFFSET
  {{ DAILY_ENTRIES.pagination?.offset || 0 }}

This works but the query runs repeatedly in a loop.

Please help :sweat_smile:

UPDATE:

  • I disabled converting queries to prepared sql statements and both queries 'worked'. Same behavior though. It appears that accessing the table's sortArray is what causes the query to run repeatedly.
2 Likes
2

Hey @OzzyGonz thanks for reaching out! Your second query is working nicely on my side (with prepared statements enabled) :thinking: I haven't been able to reproduce an endless loop

Are there any clues in the debugger (you can expand each query trigger to see what caused it to run)

Otherwise, are you open to sharing an app export?

3

Hi Tess, thanks for getting back to me. Open to sharing an export - is there an email I can send it to? Prefer that to attaching it hear. Will do it if that's the only option though :slight_smile:

To answer your question about hints in the debugger - the debugger indicates a user interaction is triggering the query. I thought maybe that having a default sort action set was triggering it but I turned it off and it still spazzed on me.

image

4

Interesting :thinking: I'll take a look at the export. I sent you a DM if you're ok with replying there so that other community members don't get access to your export.

The export also doesn't come with access to your resources - mostly just the app structure