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?