Sort columns not working for server side pagination

[Kiran_Nair]

This is the query I am using for for my table

select
*
from
domains d
left join
model_responses m
on
d.name = m.domain_name
order by
{{ table1.sortArray.length > 0 ? ${table1.sortArray[0].columnId} ${table1.sortArray[0].direction ? 'DESC' : 'ASC'} : 'm.score DESC' }} NULLS LAST
limit
{{ table1.pagination.pageSize }}
offset
{{ table1.pagination.offset }};

This is not working for some reason. I pasted this into a text box and I get the below query which work in my psql database
select
*
from
domains d
left join
model_responses m
on
d.name = m.domain_name
order by
score DESC NULLS LAST
limit
50
offset
0;

Not sure what is missing but I have been banging my head against the wall for quite a bit on this.

[Shawn_Optipath]

Hi @Kiran_Nair,

Can you give this a try and let me know?

SELECT
  *
FROM
  domains d
LEFT JOIN
  model_responses m
ON
  d.name = m.domain_name
ORDER BY
  {{ table1.sortArray.length > 0 
      ? table1.sortArray[0].columnId + ' ' + (table1.sortArray[0].direction ? 'DESC' : 'ASC') 
      : 'm.score DESC' 
  }} NULLS LAST
LIMIT {{ table1.pagination.pageSize }}
OFFSET {{ table1.pagination.offset }};

[Kiran_Nair]

@Shawn_Optipath

SELECT
  *
FROM
  domains d
LEFT JOIN
  model_responses m
ON
  d.name = m.domain_name
ORDER BY
  {{ table1.sortArray.length > 0 
      ? table1.sortArray[0].columnId + ' ' + (table1.sortArray[0].direction ? 'DESC' : 'ASC') 
      : 'm.score DESC' 
  }} NULLS LAST
LIMIT {{ table1.pagination.pageSize }}
OFFSET {{ table1.pagination.offset }};

Hi @Shawn_Optipath ,

I have tried your query and the sort functionality is still not working for some reason.

[Shawn_Optipath]

Hi @Kiran_Nair,

Can you please share as much as possible such as screenshots?

[Kiran_Nair]

@Shawn_Optipath

SELECT
  *
FROM
  domains d
LEFT JOIN
  model_responses m
ON
  d.name = m.domain_name
ORDER BY
  {{ table1.sortArray.length > 0 
      ? table1.sortArray[0].columnId + ' ' + (table1.sortArray[0].direction ? 'DESC' : 'ASC') 
      : 'm.score DESC' 
  }} NULLS LAST
LIMIT {{ table1.pagination.pageSize }}
OFFSET {{ table1.pagination.offset }};

@Shawn_Optipath

SELECT
  *
FROM
  domains d
LEFT JOIN
  model_responses m
ON
  d.name = m.domain_name
ORDER BY
  {{ table1.sortArray.length > 0 
      ? table1.sortArray[0].columnId + ' ' + (table1.sortArray[0].direction ? 'DESC' : 'ASC') 
      : 'm.score DESC' 
  }} NULLS LAST
LIMIT {{ table1.pagination.pageSize }}
OFFSET {{ table1.pagination.offset }};

This is the query

This is the result, I have a text component with the query to see if there is anything wrong, but the query from that works fine in my db

[Shawn_Optipath]

It looks like your query works fine when tested in the text component, but something might be going wrong when executing it within Retool's database query component. Here are a few things to check:

1. Check if table1.sortArray Exists and is Populated

• If table1.sortArray is empty, the sorting logic defaults to 'm.score DESC'.

2. Verify the Sorting Logic

• The query dynamically builds the ORDER BY clause, but if table1.sortArray[0].columnId is undefined, the query might break.
• Test this manually by replacing table1.sortArray values with static values in the text component.

3. Confirm Pagination Variables

• Ensure {{ table1.pagination.pageSize }} and {{ table1.pagination.offset }} return valid numbers.

4. Check SQL Syntax with the Final Query

• Copy the final SQL query displayed in the text component and run it directly in your database.
• If it runs fine, then Retool might be misinterpreting the query format.

5. Try a Static Query

• Temporarily replace {{ table1.sortArray }} and {{ table1.pagination.* }} with static values to see if the query runs.

[Darren]

Welcome to the community, @Kiran_Nair! Thanks for reaching out.

I'm guessing that the content of the text component is exactly the same as the query? If that's the case, it seems likely that the process of converting it to a prepared statement is responsible for the behavior that you're seeing.

You can verify this by disabling prepared statements via the resource configuration.

If that does fix it, we might have to figure out an alternative workaround, as we don't really recommend leaving prepared statements disabled. You can read more about it here!

[Kiran_Nair]

@Darren ,

Yeah, disabling prepared statements helped fix my issue.

Thanks