Infinite loop getRows and getTotalRows retool database query

Queries and Resources
3

I don't think that's possible. Due to the fact that the query is triggered again when filters change. It's not a small query. It works fine though. Especially when it's cached.

The reason I need it to be set to automatic is because the pagination stops working in retool mobile. I've got a topic about that as well. I'll add it below in case people want to read about it.

getTotalRows is the total amount of records taking in consideration the filters that are set.

getRows():


SELECT 
  a.id AS lineitemsId,
  *,
  CAST((
    SELECT COUNT(*)
    FROM app_variants v2
    WHERE v2."productId" = a."productId" 
      AND v2.variant_stock = true
  ) AS INT ) AS inStockVariantCount
FROM app_lineitems a
JOIN app_products p ON a."productId" = p.id
JOIN app_variants v ON a."variantId" = v."variantId"
JOIN app_lineitem_properties lp ON a.id = lp."lineitemId"
WHERE 
  
  ((lp.picked = {{ getTabFilter.value }} AND lp.source = 'order')
  OR (lp.source <> 'order' AND lp.restocked={{ getTabFilter.value }} AND lp.restock_location = 'order'))
  
  AND lp.archived = {{ archivedSwitch.value }}
  AND p.ignore = {{  ignoreSwitch.value }}
  AND (
    ({{ preorderSwitch.value }}::boolean IS NULL OR {{ preorderSwitch.value }}::boolean = true)
    OR lp.preorder = false
  )
  AND (
    ({{ orderedSwitch.value }}::boolean IS NULL OR {{ orderedSwitch.value }}::boolean = true)
    OR lp.ordered = false
  )
  AND (
    ({{ lostSwitch.value }}::boolean IS NULL OR {{ lostSwitch.value }}::boolean = true)
    OR lp.lost = false
  )
  AND (
    ({{ inTransitSwitch.value }}::boolean IS NULL OR {{ inTransitSwitch.value }}::boolean = true)
    OR lp.in_transit = false
  )
ORDER BY a.created_time DESC
LIMIT {{ lineItemList.pagination.pageSize }}
OFFSET {{ lineItemList.selectedPageIndex * lineItemList.pagination.pageSize }};

getTotalRows()

SELECT COUNT(*)::int AS count
FROM app_lineitems a
JOIN app_products p ON a."productId" = p.id
JOIN app_variants v ON a."variantId" = v."variantId"
JOIN app_lineitem_properties lp ON a.id = lp."lineitemId"
WHERE 
  
  ((lp.picked = {{ getTabFilter.value }} AND lp.source = 'order')
  OR (lp.source <> 'order' AND lp.restocked={{ getTabFilter.value }} AND lp.restock_location = 'order'))
  
  AND lp.archived = {{ archivedSwitch.value }}
  AND p.ignore = {{  ignoreSwitch.value }}
  AND (
    ({{ preorderSwitch.value }}::boolean IS NULL OR {{ preorderSwitch.value }}::boolean = true)
    OR lp.preorder = false
  )
  AND (
    ({{ orderedSwitch.value }}::boolean IS NULL OR {{ orderedSwitch.value }}::boolean = true)
    OR lp.ordered = false
  )
  AND (
    ({{ lostSwitch.value }}::boolean IS NULL OR {{ lostSwitch.value }}::boolean = true)
    OR lp.lost = false
  )
  AND (
    ({{ inTransitSwitch.value }}::boolean IS NULL OR {{ inTransitSwitch.value }}::boolean = true)
    OR lp.in_transit = false
  )

It's basically the same query but without the limit and offset and COUNT added.

Topic about pagination not working when resource query is set to manual: