是否可以在JOIN中使用占位符？

Question

I have this following SQL query which is used to output forum posts with posts' relevant data in relational tables (user that posted it, forum/thread that it's a child of etc).

I am using PDO positional placeholders (hence the ?'s)

SELECT {Lots of select stuff here}
FROM tab_posts tposts
INNER JOIN tab_users tuser ON tuser.id=?
INNER JOIN tab_users tusers ON tposts.user_id=tusers.id
INNER JOIN tab_ranks tranks ON tusers.rank_id=tranks.id
INNER JOIN tab_avatars tavatars ON tusers.avatar_id=tavatars.id
INNER JOIN tab_threads tthreads ON tposts.thread_id=tthreads.id
INNER JOIN tab_forums tforums ON tthreads.forum_id=tforums.id
WHERE tposts.thread_id=? AND tforums.rank_id <= tuser.rank_id

So in this query I'm using 2 placeholders, first one is the ID of the user retrieved from session, the other is the thread id retrieved from the request (to ensure we're only listing posts that belong to that thread).

tuser is the session user, tusers is the alias for the user that submitted the post. I'm only requiring tuser to check whether the session user is allowed to view those posts (by access rank, the rank_id in the forums table is the minimum allowed rank).

I would just like to confirm whether using a placeholder in the JOIN section (or rather, a variable at all) is valid? Or am I limited to using tables? I'm pretty sure I'm using it incorrectly, in which case how else could I be checking for the user's rank?

I'm also not sure if my other JOIN's are valid actually, bah ! (I'm new to using JOIN if you couldn't tell)

Answer 1

You can use placeholders in a JOIN's ON clause when it is appropriate to do so. The ON condition just has to be some expression which evaluates to TRUE when compared row to row between two tables, so a condition like

FROM
  t1
  LEFT JOIN t2 ON t1.id = t2.id AND t2.othercol = ?

would be a valid use of the ? placeholder in the ON. In a joining condition like the one above, both the match on id columns and the othercol column matching the ? input value must be true for the join to be met. You'll see this usage commonly in LEFT JOINs when more than just a column match is required to relate the tables, but adding a WHERE condition would incorrectly cause the LEFT JOIN to omit NULLs and therefore behave like an INNER JOIN.

In your case however, it looks like you really just need to move that user condition into the WHERE clause to filter the result set to the requested user id.

SELECT {Lots of select stuff here}
FROM tab_posts tposts
  INNER JOIN tab_users tusers ON tposts.user_id = tusers.id
  INNER JOIN tab_ranks tranks ON tusers.rank_id = tranks.id
  INNER JOIN tab_avatars tavatars ON tusers.avatar_id = tavatars.id
  INNER JOIN tab_threads tthreads ON tposts.thread_id = tthreads.id
  INNER JOIN tab_forums tforums ON tthreads.forum_id = tforums.id
WHERE 
  /* This condition belongs in the WHERE */
  tusers.id = ?
  tposts.thread_id = ?
  AND tforums.rank_id <= tuser.rank_id

Your remaining joins look correct, as long as your tables are actually laid out the way the joins express them.

To generalize this just a little more, ? placeholders may be used anywhere that you would otherwise use a scalar value in your query; that is, a string or numeric value (or boolean, blob, etc). So you can use them as function arguments for example:

/* Truncate a column to some ? number of characters */
SELECT LEFT(column, ?)

Or a scalar value in the SELECT list:

/* Add an additional string suffix to every row in a column */
SELECT CONCAT(column, ?)

You cannot use a ? in place of a table or column name, though. They only work for scalar values.