Filtering SQL query on paginated table

App Building
1

ey @Kabirdas i got a issue i think its almost the same, i got a big table with 100k records, first in the table im using

SELECT
*, DATE_PART(
'YEAR',
AGE (CURRENT_DATE, tbl_clientes.fechadenacimiento)
) as edad
FROM
tbl_clientes

limit {{ table1.pagination.pageSize }}
offset {{ table1.pagination.offset }}

i added some filters and im using query json with sql

SELECT * FROM {{formatDataAsArray(clientes.data)}} WHERE
({{!sector.value}} /* si esta en blanco muestra todo /
OR Sector = {{ sector.value }}) / filtro por colaborador */

AND ({{ !cedula.value }}
OR cedula ILIKE {{ cedula.value }})

AND ({{ !ubicacion.value }}
OR Ubicacion ILIKE {{ ubicacion.value }})

AND ({{ !(rangodefecha.value.start) }}
OR (fechadeseguimiento <= {{moment(rangodefecha.value.end).format('YYYY-MM-DD')}}
AND fechadeseguimiento >= {{moment(rangodefecha.value.start).format('YYYY-MM-DD')}} ))

order by fechadeseguimiento asc

but now my issue if i try to filter some data out of the current pagination it cant be found, what i should use to fix this? or should i dont usee pagination server side at all?

2

did you fixed the issue? having the same here

3

Hey @agaitan026!

Is it possible that you're missing wildcards from your ILIKE statements? How do things work if you use cedula ILIKE {{ '%' + cedula.value + '%' }}) and Ubicacion ILIKE {{ '%' + ubicacion.value + '%' }})?

4

like that it only shows results on first page, if i go to page 2, wont work, when server side pagination is enabled i cant filter all data only the paginated page

5

In that case, you'll need to include the filters in the first table query since it's the only one that has access to all your data. The single page of data is all the Query JSON with SQL query has access to filter on.

Does something like this work?

SELECT
  *,
  DATE_PART(
    'YEAR',
    AGE (CURRENT_DATE, tbl_clientes.fechadenacimiento)
  ) AS edadFROMtbl_clientes
WHERE
  (
    {{!sector.value}}
    OR Sector = {{ sector.value }}
  )
  AND (
    {{ !cedula.value }}
    OR cedula ILIKE {{ '%' + cedula.value + '%' }}
  )
  AND (
    {{ !ubicacion.value }}
    OR Ubicacion ILIKE {{ '%' + ubicacion.value + '%' }}
  )
  AND (
    {{ !(rangodefecha.value.start) }}
    OR (
      fechadeseguimiento <= {{moment(rangodefecha.value.end).format('YYYY-MM-DD')}}
      AND fechadeseguimiento >= {{moment(rangodefecha.value.start).format('YYYY-MM-DD')}}
    )
  )
ORDER BY
  fechadeseguimiento ASC
LIMIT
  {{ table1.pagination.pageSize }}
OFFSET
  {{ table1.pagination.offset }}
1 Like
6

so i dont need query json with sql? @Kabirdas

7

Loom | Free Screen & Video Recording Software | Loom this is what im getting, i stopped using query json with sql and put the main query as you said. if you see the result is on the page 2 that ID number, but when i try to search it in page #2 it wont work, but if i move to page #1, it works. What im doing wrong?

8

at the end i need the user to filter all dataset but also keep pagination, not sure if im missing something or need cache or something else

9

It doesn't look like you need it here but filtering with a server-side paginated query can be tricky since your table will stay on the same page even when the query is rerun. Can you try adding an event handler to your input component that sets the table back to its first page?

image
image1158×642 55.9 KB

10

will try that, but thats the correct approach for me use case? what you think? basically its a table with a query that have inner join in total i have 130k records, and users will be updating, adding, deleting in those tables in the day, there will be 10 users at the same time i need the best perfomance possible

11

@Kabirdas but if server side is tricky any chance i can use redis or dozer as cache for postgresql? i dont know if someone else in here got the same use case, a table with a large dataset and custom filters and the need to lookup all dataset not only first page :frowning: im struggling

12

with that approach i get a glitch see video

https://www.loom.com/share/fe49cbc159bf49088e332cbdaddd9272?sid=6ce15020-0f0d-4245-a9e0-0aef68cac1ab

is small almost 0.4 ms i think, but cant be removed that glitch? im using self hosted and a local db too so speed should not be issue

13

You could potentially try client-side pagination and see how performant it is @agaitan026. 100k records is certainly a lot to pull in at once but the new table is much better equipped to handle large collections of data. From there you could decide if the tradeoffs in performance are worth it for one method or the other :thinking:

14

Yeah what i did was to use filters directly in postgresql not using query sql with json as that only filter page 1. Also i added all my filters to the rowcount query too and worked good. Still not sure if doing the setpage 1 thing is something good or not. Not sure about that

15

So i can have my filters on my original query and query json with sql as source of table?
@Kabirdas @victoria @Tess

Thank you

16

If you were to pull in the full data set with your original query you could filter the results using a Query JSON with SQL query and then use client-side pagination in the table settings instead of trying to do it in the Query JSON with SQL query.

1 Like
17

I don’t understand, so i need to add all where statement for filter in original query. Then the query json with sql what will do? Any performance gain with that?

Right now i got

  1. Original query with all filter using limit and offset to use server side pagination
  2. I set that to table
  3. Table have rowcount from a sql query
18

I think the setup you have right now is good @agaitan026!

You could try:

  1. Original query with no filters
  2. Query JSON with SQL query with filters but no pagination
  3. Set Query JSON with SQL query to table
  4. Use client-side pagination on table

But that might or might not be more performant depending on how well Retool/your browser handle the 100k rows from your original query.

2 Likes