I am trying to do ordering on query with subquery for a name from uuid. Let's have those two tables in MySQL:

bans:

users:

With this query, I can select whole ban record fetch user's name from users table:

SELECT
    bans.*,
    (SELECT users.name FROM users WHERE users.uuid = bans.uuid) as name
FROM bans
ORDER BY :column;

But I cannot order by name. How to change this query so I can do ordering by users.name? I tried to wrap my SQL select into another select, but it does not work and I am unable to resolve it :D

CodePudding user response：

That is a JOIN query if I ever saw one.

You cannot pass a table or column name as a parameter in either PDO or MYSQLI. The database cannot compile and optimise code with unknown columns or tables in it

SELECT b.*, u.name
FROM bans b
    LEFT JOIN users u ON u.uuid = b.uuid
ORDER BY u.name;

CodePudding user response：

maybe you can try this

select a.id, a.uuid, a.time, a.reason, b.name from bans a
left join users b
on a.uuid = b.uuid
order by b.name;

CodePudding user response：

You cannot use parameterize identifier (database/table/column/etc) names. Your database library is most likely transforming this:

SELECT * FROM t ORDER BY :column

Into this:

SELECT * FROM t ORDER BY 'name'

It is perfectly valid SQL as far as MySQL is concerned, yet it won't produce the expected result since it is ordering by a constant. You need to explicitly specify the column name:

ORDER BY name