I can't build sql queries with dynamic column sort and sort order.

[cococov]

Due to pagination I need to make queries with different sort orders and even different column, but currently the @vercel/postgres clean my query, deleting my dynamic order.

// Works
await sql`SELECT * FROM metagame_cards ORDER BY occurrences DESC LIMIT ${query.pageSize} OFFSET ${query.page * query.pageSize}`;

// Doesn't work
await sql`SELECT * FROM metagame_cards ORDER BY ${columnSort} DESC LIMIT ${query.pageSize} OFFSET ${query.page * query.pageSize}`;

// Doesn't work
await sql`SELECT * FROM metagame_cards ORDER BY ${columnSort} ${sortOrder} LIMIT ${query.pageSize} OFFSET ${query.page * query.pageSize}`;

Is there a way to (at least in a dangerous way) do this?

[elliott-with-the-longest-name-on-github]

We currently have a PR providing this functionality:

const orderbyColumn = identifiers('occurrences');
const orderbyDirection = sortOrder === 'DESC' ?  fragment`DESC` : fragment`ASC`;
await sql`SELECT * FROM metagame_cards ORDER BY ${orderByColumn} ${orderByDirection} LIMIT ${query.pageSize} OFFSET ${query.page * query.pageSize}`;

However, we don't know if that's going to be merged yet -- there's a good bit of security auditing to do and probably some discussions around API!

For now, you can use sql.query directly. It means you'll have to do some manual string-concatenation, which isn't the best, but it's the best workaround until something better like the above is merged!

[nhopfe]

Is there any update on if/when that PR will be accepted? I just updated Next and Vercel to latest but am still not able to build ORDER BY queries dynamically.

For now, you can use sql.query directly. It means you'll have to do some manual string-concatenation, which isn't the best, but it's the best workaround until something better like the above is merged!

how does one get the sql.query approach to function properly? The following is the gist of what I'm trying to run in my code and it still seems to ignore the ORDER BY like it did when I was trying to use the template strings to build the query dynamically.

const orderBy = sortBy + ' ' + sortDirection;
  const params = [orderBy, ITEMS_PER_PAGE, offset];

  const fullQuery = `
    SELECT 
      recipes.id,
      recipes.user_id,
      recipes.name,
      ingredients.name AS primary_ingredient, 
      recipe_types.name AS recipe_type,
      recipes.calories_per_serving,
      recipes.servings,
      recipes.preparation_time_mins,
      recipes.cooking_time_mins,
      recipes.taste_rating,
      recipes.instructions,
      recipes.image_url,
      recipes.date
    FROM recipes 
    JOIN ingredients ON recipes.primary_ingredient = ingredients.id
    JOIN recipe_types ON recipes.recipe_type = recipe_types.id
    ORDER BY $1
    LIMIT $2 OFFSET $3
  `;
  
  const recipeData = await sql.query(fullQuery, params);

If I hard code the ORDER BY line, it works fine, so I'm assuming that means I'm not using this sql.query correctly. (I concatenated the 'orderBy' string in it's own variable because I was getting a syntax error if I kept sortBy and sortDirection as separate params and then put 'ORDER BY $1 $2' - it didn't seem to like having two params next to each other).