CODEWITHSUNDEEP
mysql
user3462020
user3462020

Reputation: 61

Order results by another table?

I'm working on a custom forum system and I'm trying to figure out how to put a thread on the top of the list if a user posts in it.

I've got this for my query

SELECT 
user_threads.threadID, 
user_threads.title, 
user_threads.uid, 
user_threads.postDate,
thread_messages.posted
FROM 
    user_threads,
    thread_messages
WHERE
    parent = :parent
GROUP BY
    user_threads.title
ORDER BY 
    thread_messages.posted
DESC

Which doesn't appear to be working. if I post in a new thread, it remains where it is on the list.

Upvotes: 1

Views: 28

Answers (4)

TheConstructor
TheConstructor

Reputation: 4465

You need to tie the tables together. You can do this by adding something like user_threads.threadID = thread_messages.threadID (second column name is guessed and needs to be checked) to the WHERE or by rewriting the query like this:

SELECT
    user_threads.threadID,
    user_threads.title,
    user_threads.uid,
    user_threads.postDate,
    thread_messages.posted
FROM
    user_threads
JOIN
    thread_messages ON thread_messages.threadID = user_threads.threadID
WHERE
    parent = :parent
GROUP BY
    user_threads.title
ORDER BY
    thread_messages.posted
DESC

And also not, that you probably should replace

GROUP BY
    user_threads.title

by something like

GROUP BY
    user_threads.threadID

or

GROUP BY
    user_threads.threadID, user_threads.title

as sooner or later your thread-titles may be reused while the ID should be "forever" unique. Also threadID will most probably lead to a faster query as the database only needs to compare numbers versus text of various lengths.

Upvotes: 0

fsalazar_sch
fsalazar_sch

Reputation: 455

Use aliases for the tables 'user_threads' and 'thread_messages' i.e.

 SELECT 
A.threadID, 
A.title, 
A.uid, 
A.postDate,
B.posted
FROM 
    user_threads A,
    thread_messages B
WHERE
    parent = :parent
   //also do it with 'parent' field if parent is a field of user_threads 
   //A.parent else thread_messages B.parent
GROUP BY
    A.title
ORDER BY 
    B.posted
DESC

Upvotes: 0

Barmar
Barmar

Reputation: 781068

You need to join the tables by the threadID. Also, if you just want one row per thread, you need to use the date of the last post.

SELECT 
user_threads.threadID, 
user_threads.title, 
user_threads.uid, 
user_threads.postDate,
MAX(thread_messages.posted) AS last_post
FROM 
    user_threads
LEFT JOIN
    thread_messages ON thread_messages.threadID = user_threads.threadID
WHERE
    parent = :parent
GROUP BY
    user_threads.threadID
ORDER BY 
    last_post DESC

I used LEFT JOIN so that threads will be shown even if they don't have anything in thread_messages. If this is not needed, you can use a regular JOIN (aka INNER JOIN).

Upvotes: 1

theHacker
theHacker

Reputation: 4043

You are missing the JOIN condition. Currently you are getting the cross product of user_threads and thread_messages. That means you got rows for each thread and post in the result.

Try adding something link this

FROM 
    user_threads
JOIN
    thread_messages USING(threadID)

to get each thread only once in your result set.

Upvotes: 0

Related Questions