CODEWITHSUNDEEP
mysqljoin
AmbroseChapel
AmbroseChapel

Reputation: 12097

How should this multi-table join be done?

I have a discussion board with threads, posts and users.

So now I need to create a join so that, for a given user, I can get a table of threads with unread posts.

How do I create the right join? Users, posts and threads all have a primary key ID of course, which are recorded in the lookup tables, i.e.

TABLE `posts_read`
user  | has_read_post | of_thread
---------------------------------
5     | 100           | 3

and

TABLE `subscribed_to`
user  | reads_thread
---------------------
5     | 3

Example: user 5 is subscribed to thread 3, and has read up to post 100 of thread 3 and, but thread 3 now has 200 posts, the user wants to start reading thread 3 at post 101 because she's 100 posts behind.

Can I do this in one step? Of course she may be subscribed to more than one thread so we should sort by which one has the most unread.

Upvotes: 0

Views: 71

Answers (1)

Kevin Cathcart
Kevin Cathcart

Reputation: 10108

Here is a possible approach for the problem. I'm assuming that posts_read.has_read_post is equivalent to the number of posts read, rather than being a number that uniquely identifies a post among all threads. If it is a unique post identifier, then things are harder.

First create a query that returns two columns. The columns are the thread number for each thread the user is subscribed to. The second column is the number of posts in that thread.

That should be a fairly easy query to design. I'll assume the query returns columns named 'thread' and 'posts'.

Now we create a new query based on that one that contains the unread messages for each such thread. I'm assuming we are interested in user number 5. You will need to substitute the query you already wrote in for OtherQuery (but keep the parentheses).

SELECT other.thread, (other.posts - COALESCE(posts_read.has_read_post,0) )
AS UnreadCount FROM (OtherQuery) other LEFT OUTER JOIN
posts_read ON posts_read.of_thread = other.thread AND posts_read.user=5

Now you probably want to add a 

WHERE UnreadCount > 0
to that query to get only the threads with unread posts. You also want an ORDERBY, but you should have no problem adding that.

Edit: Revising the query, making the assumption that the post identifier is strictly increasing:

SELECT other.thread, (SELECT count(*) FROM posts WHERE
    posts.Thread = subscribed_to.thread
    AND posts.ID > COALESCE(posts_read.has_read_post,0)
    AND Post.Deleted=0) AS UnreadCount
FROM subscribed_to LEFT OUTER JOIN
    posts_read ON posts_read.of_thread = subscribed_to.thread AND
    posts_read.user=subscribed_to.user
WHERE subscribed_to.user=5

This new query counts unread posts by counting the number of non-deleted posts in a thread with a post id greater than the highest post id the user has read. Obviously I made some guesses about what the posts table is named, and how it is structured, but it should not be hard to adjust it to match whatever your table contains posts looks like.

Upvotes: 1

Related Questions