Sort table with server pagination

App Building
1

I got an app that the client needs to sort the table using server side pagination, but client need to sort all data or if filters are used to sort those results only. Is that possible?

i have tried to put disable prepared statement in my resource postgres but my query is not working:


SELECT
  o.id as idobs,
  c.id,
  c.idclave_nombre,
  o.fechaproxima_llamada,
  c.ubicacion,
  c.nombres,
  c.apellidos,
  c.cedula,
  c.sector,
  c.empresa,
  c.acreedor,
  c.analista,
  c.sucursal,
  o.motivo,
  c.ps,
  c.fecha_nacimiento,
  c.nivel,
  DATE_PART('YEAR', AGE (CURRENT_DATE, c.fecha_nacimiento)) as edad,
  c.sexo,
  c.usuario,
  c.notas,
  CURRENT_DATE - CAST(c.fecha_seguimiento AS DATE) as TE

FROM
  tbl_clientes c
  LEFT JOIN tbl_observaciones o ON c.idclave_nombre = o.idclave_nombre
WHERE
  (
    {{ ! cedula.value }}
    OR c.cedula ILIKE {{ '%' + cedula.value + '%' }}
  )
  AND (
    {{ ! nombre.value }}
    OR c.nombres ILIKE {{ '%' + nombre.value + '%' }}
  )
  AND (
    {{ ! apellido.value }}
    OR c.apellidos ILIKE {{ '%' + apellido.value + '%' }}
  )
  AND (
    {{ ! notas.value }}
    OR c.notas ILIKE {{ '%' + notas.value + '%' }}
  )
  AND (
    {{ubicacion.value.length === 0}}
    OR c.ubicacion = ANY ({{ ubicacion.value }})
  )
  AND (
    {{motivo.value.length === 0}}
    OR o.motivo = ANY ({{ motivo.value }})
  )
  AND (
    {{sector.value.length === 0}}
    OR c.sector = ANY ({{ sector.value }})
  )
  AND (
    {{acreedor.value.length === 0}}
    OR c.acreedor = ANY ({{ acreedor.value }})
  )
  AND (
    {{sucursal.value.length === 0}}
    OR c.sucursal = ANY ({{ sucursal.value }})
  )
  AND (
    {{analista.value.length === 0}}
    OR c.analista = ANY ({{ analista.value }})
  )
  AND (
    {{nivel.value.length === 0}}
    OR c.nivel = ANY ({{ nivel.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

{{ table1.sortArray[0]?.fechaproxima_llamada ? table1.sortArray[0].columnId : "fechaproxima_llamada" }}


LIMIT {{table1.pagination.pageSize}} 
OFFSET {{table1.pagination.offset}}

shows this error:

error:true
message:"syntax error at or near ")""
position:554

Thank you

2

Any tip? @victoria @Peter_Kunz @ScottR @bradlymathews

3

Just one example: You have the following
{{sector.value.length === 0}} OR c.sector = ANY ({{ sector.value }})
I would use
{{!sector.value}} OR c.sector = {{ sector.value }}

1 Like
4

Its a multi select component thats why i used that, but thats not my current problem. My issue is how to allow columns order by so user may order or sort all columns just like when we use client side pagination

5

ok I didn't know about the field....
You client needs to sort the table by column? Any column? I think you should pick one default column to order by in the query and then go from there....
This doesn't look correct why is the question mark after the index?
table1.sortArray[0]?.fechaproxima_llamada....

1 Like
6

i saw that in another post of me

image
image894×783 74.3 KB