Open cococov opened 10 months ago
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!
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).
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.
Is there a way to (at least in a dangerous way) do this?