Effect CommunityEC
Effect Community•2y ago•
8 replies
attila

Creating a Cursor-Paginated SQL Query with Multiple Filters and Sorting

Hi 👋 I'm trying to write a cursor-paginated SQL query where it is possible to filter several columns, order the results by a pre-defined set of available columns, in a given sort order (ASC or DESC). My attempt looks like this:

sql`SELECT DISTINCT(id) FROM my_table WHERE ${sql.and(
  [
    sql`col1 = ${col1}`,
    col2 ? sql`col2 IN ${sql(col2)}` : [],
    cursor ? sql`id ${direction === "forward" ? ">" : "<"} ${cursor}` : [],
  ].flat()
)} ORDER BY ${orderBy} ${order} LIMIT ${limit}`


When I print the resulting sql (using compiler.compile), I get this:
SELECT DISTINCT(id) FROM my_table WHERE (col1 = ? AND col2 IN (?)) ORDER BY ? ? LIMIT ?' == ''


What I would like to see somehow is:
SELECT DISTINCT(id) FROM my_table WHERE (col1 = ? AND col2 IN (?)) ORDER BY id ASC LIMIT ?


Did I hit a limitation of sqlfx/mysql, or is the way I'm trying to build this query wrong?
Was this page helpful?