Strange behavior using server side pagination

agaitan026 · October 29, 2023, 9:22pm

Hi im getting this behavior using server side pagination

my sql query is like this

SELECT 
  DISTINCT on (c.apellidos)
  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, 
  c.sexo, 
  c.usuario,
  DATE_PART('YEAR', AGE(CURRENT_DATE, c.fecha_nacimiento)) as edad 
FROM 
  tbl_clientes c 
  INNER JOIN tbl_observaciones o ON c.idclave_nombre = o.idclave_nombre 

  WHERE (
    {{ !cedula.value }}
    OR c.cedula LIKE {{ cedula.value  }}
  )


AND 
    ({{ !analista.value }}
    OR c.analista ILIKE {{ '%' + analista.value + '%' }})

  AND (
    {{ !ubicacion.value }}
    OR c.ubicacion ILIKE {{ '%' + ubicacion.value + '%' }}
  )

  AND (
    {{ !motivo.value }}
    OR o.motivo ILIKE {{ '%' + motivo.value + '%' }}
  )

  AND (
    {{ !sector.value }}
    OR c.sector ILIKE {{ '%' + sector.value + '%' }}
  )

ORDER BY 
  c.apellidos, 
  o.fechaproxima_llamada ASC 
LIMIT 
  {{table1.pagination.pageSize}} 
OFFSET 
  {{table1.pagination.offset}}

how can avoid that? if you see i got some filters, it lookup a dataset, that works but table should be limited to the results items but still showing like all results are in there and allows user to keep changing page, any way to remove that behaviour? heres is my table pagination config

and rowcount query

SELECT
  COUNT(*) as count
FROM
  tbl_clientes;

thank you

Any idea? @ScottR @PeteTheHeat @Tess @Kabirdas @victoria

agaitan026 · November 5, 2023, 3:20pm

Hi any tips? @PatrickMast @victoria @Harry_Doan @Kabirdas @ScottR @matth

matth · November 9, 2023, 1:41am

All SQL? No filters? If I'm not mistaken, your count query should have those where conditions as well

agaitan026 · November 9, 2023, 1:42am

Yea all sql filters are inside where … and or and or etc as i need to query all dataset thats why im using all sql

agaitan026 · November 9, 2023, 1:43am

So need to add to the query all where statements and or etc?

matth · November 9, 2023, 1:51am

I think so... The wheres and the join. You have a where on o too. Because your count currently is the whole dataset. I could be wrong, give it a whirl.

agaitan026 · November 9, 2023, 2:58am

works perfect i added this to my rowcount

SELECT
  COUNT(*) as count
FROM
  tbl_clientes c
  
 INNER JOIN tbl_observaciones o ON c.idclave_nombre = o.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 (
    {{ ! acreedor.value }} 
    OR c.acreedor = {{ acreedor.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 (
    {{ !(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 != ''

without the limit and offset and now if results are 4, in the table shows 4

thank you