Building query for ORDER BY ? DESC/ASC doesn't seem to use the values

[k3rne1pan1c]

 let extraClause = "";
    if (extraFilters) {
        if (extraFilters.sorted) {
            if (extraFilters.sortOrder === "DESC") {
                extraClause += ' ORDER BY ? DESC';
            } else {
                extraClause += ' ORDER BY ? ASC';
            }
            whereValues.push(extraFilters.sorted);
        }
    }
    const query = 'SELECT p.*, GROUP_CONCAT(i.image_path SEPARATOR \',\') AS image_paths FROM post p LEFT JOIN Image i ON p.post_id = i.post_id' + (whereClause ? ' WHERE ' + whereClause : '') + ' GROUP BY p.post_id ' + (extraClause ? extraClause : '');

Value + Query

[ 'created_at']
'SELECT p.*, GROUP_CONCAT(i.image_path SEPARATOR \',\') AS image_paths FROM post p LEFT JOIN Image i ON p.post_id = i.post_id GROUP BY p.post_id  ORDER BY ? DESC LIMIT ?'

It only works when it look like this:

..."ORDER BY ? DESC" inside of the query constant

It doesn't work with preparing the statement beforehand. I feel like that they add DESC as a string and then mysql doesn't recognizes it. The SQL is correct in any way, but it doesn't filter.

Executing the statement like this:

 const [rows] = await pool.query<Post[]>(query, whereValues);

[sidorares]

hi @k3rne1pan1c , what do you mean by "doesn't work"? Do you get error response from myqsl server? a local error?

Can you also post actual query and parameters after all conditions? Overall I feel your question is not specific to mysq2 library

[k3rne1pan1c]

const query = `SELECT p.*, GROUP_CONCAT(i.image_path SEPARATOR \',\') AS image_paths FROM post p LEFT JOIN Image i ON p.post_id = i.post_id` + ` GROUP BY p.post_id ORDER BY created_at DESC`

 const [rows] = await pool.query<Post[]>(query);

Keep an eye on the created_at. Running the above query, gives me the correct filtered response, sorted by created_at

const query = `SELECT p.*, GROUP_CONCAT(i.image_path SEPARATOR \',\') AS image_paths FROM post p LEFT JOIN Image i ON p.post_id = i.post_id` + ` GROUP BY p.post_id ORDER BY ? DESC`
    const [rows, field] = await pool.query<Post[]>(query,['createdd_at']);

Using prepared statement and entering the value created_at like this doesn't work.
Since I'm using await, I can't log the output, or the exact query, but I debugging it and figured, that query() will at some point string escape the value I give in and returns "'created_at'".

So what is the right way to put the order value in that array?

Then this query is run by mysql2, which is wrong:

"SELECT p.*, GROUP_CONCAT(i.image_path SEPARATOR ',') AS image_paths FROM post p LEFT JOIN Image i ON p.post_id = i.post_id GROUP BY p.post_id ORDER BY 'created_at' DESC LIMIT 2"

Either 'created_at' needs to be without quotes or ``, but you can't use them since, TS formes them into normal quotes.

If you need any other information, please let me know, what else I can do.

[sidorares]

you might want to use escapeId here - https://github.com/mysqljs/mysql?tab=readme-ov-file#escaping-query-identifiers

Note that you can't have this as a parameter in a real prepared statement ( .query is a client side query building, not a real server side parametrized statement )

[sidorares]

also check out ?? placeholders in the above link

[k3rne1pan1c]

Thanks a lot! It's still a workaround, but I can sleep in peace.

[luizfcbhering]

I have the following error when using the field alias in ODER BY Unknown column 'val_entrada' in 'order clause'". It works in MySQLWorkbech. I use version 8. Can someone help me?