Enable dynamic filter in columns using server side pagination

App Building
1

Hi i got an app with server side pagination but client needs to use filtering by columns too , i saw this code:

SELECT * FROM example_table ORDER BY {{table1.sortArray[0].columnId}} {{table1.sortArray[0].direction}} ...

but is not working inside my current query

my current query:

SELECT 
  o.id, 
  c.idclave_nombre, 
  o.fechaproxima_llamada, 
  c.ubicacion, 
  c.notas, 
  c.nombres, 
  c.apellidos, 
  c.cedula, 
  c.sector, 
  c.empresa, 
  c.acreedor, 
  c.analista, 
  c.sucursal, 
  o.motivo, 
  c.ps, 
  c.fecha_nacimiento, 
  DATE_PART(
    'YEAR', 
    AGE(
      CURRENT_DATE, c.fecha_nacimiento
    )
  ) as edad, 
  c.sexo, 
  c.usuario, 
  c.notas, 
  CURRENT_DATE - CAST(
    cu.fechadecreacion_ubicacion AS DATE
  ) as TE 
FROM 
  tbl_clientes c 
  INNER JOIN tbl_observaciones o ON c.idclave_nombre = o.idclave_nombre 
  LEFT JOIN tbl_cambiosdeubicacion cu ON c.idclave_nombre = cu.idclave_nombre 
WHERE 
  (
    {{ ! cedula.value }} 
    OR c.cedula ILIKE {{ '%' + cedula.value + '%' }}
  ) 
  AND (
    {{ ! sector.value }} 
    OR c.sector = {{ sector.value }}
  ) 
  AND (
    {{ ! analista.value }} 
    OR c.analista ILIKE {{ '%' + analista.value + '%' }}
  ) 
  AND (
    {{ ! ubicacion.value }} 
    OR c.ubicacion = {{ ubicacion.value }}
  ) 
  AND (
    {{ ! motivo.value }} 
    OR o.motivo = {{ motivo.value }}
  ) 
  AND (
    {{ !(rangodefecha.value.start) }} 
    OR (
      o.fechaproxima_llamada <= {{moment(rangodefecha.value.end).format('YYYY-MM-DD') }} 
      AND o.fechaproxima_llamada >= {{moment(rangodefecha.value.start).format('YYYY-MM-DD') }}
    )
  ) 
  AND o.fechaproxima_llamada IS NOT NULL 
  AND o.fechaproxima_llamada != '' 
ORDER BY 
  TO_DATE(
    o.fechaproxima_llamada, 'YYYY-MM-DD'
  ) ASC,
{{table1.sortArray[0].columnId}} {{table1.sortArray[0].direction}}
  
LIMIT {{table1.pagination.pageSize}} 
OFFSET {{table1.pagination.offset}}

i disabled prepared statements too but getting this

** message:"syntax error at or near ")**

2

Regarding the error message that you are getting - that showed up for me when I was trying to use this same concept for sorting with server side pagination.

In order to clear the error itself, I updated the resource settings to check "Disable converting queries to prepared statements", however this made my search function no longer work.

And the resulting query that was generated for sorting didnt work anyways, which I am still trying to figure out. Not to mention being able to add filtering - hence how I came across this post.

1 Like
3

Its maybe a bug?

4

@agaitan026 Hi there, you should be able to get this to work by first disabling prepared statements:

Screenshot 2023-12-04 at 5.53.52 PM
Screenshot 2023-12-04 at 5.53.52 PM1356×656 29.3 KB

Then by implementing something like the following:

SELECT
*
FROM
activity
ORDER BY
{{ table1.sortArray[0]?.columnId ? table1.sortArray[0].columnId : "id" }}
{{ table1.sortArray[0]?.direction ? table1.sortArray[0].direction : "DESC" }};

It's important to note that for best results you should check if the properties exist first and provide default values for Order By and for the direction (ASC, DESC) in case that the sortArray property is empty.

Users can configure the sorting by clicking on the table column but they can also remove it altogether with a third click which would empty the sortArray property of the table and cause an issue with the query if this scenario is not handled. Hopefully this works for you but let me know if you have any questions.

1 Like
5

thank you