MySQL - private messages
So I have 4 tables: Users, Posts, Private, Threads. In this example, lizzy has created 2 private messages on different threads:
"Dating" for users 2,5,6 and myself, to see the correct number of posts in this thread.
The "Break Ups" message is for user 2 only and to view the correct number of posts on that thread.
Displaying the correct score based on the user viewing the topic is the problem I am facing. Here we focus on lizzy, her streams and messages:
users (These aren't part of table. Just shows the counts we should display with our query depending on the user_id)
user_id | user_name //thread #: post_count-post_count_if_not_authorized = count to show
--------------------
1 | tony //thread 2: 3-1= 2 posts. thread 3: 2-1= 1 post.
2 | steph //thread 2: 3-0= 3 posts. thread 3: 2-0= 2 posts.
3 | lizzy //thread 2: 3 posts. thread 3: 2 posts.
4 | adam //thread 2: 3-1= 2 posts. thread 3: 2-1= 1 post.
5 | lara //thread 2: 3-0= 3 posts. thread 3: 2-1= 1 post.
6 | alexa //thread 2: 3-0= 3 posts. thread 3: 2-1= 1 post.
posts
post_id thread_id user_id post_name private (0 is public, 1 is private to authorized users)
-----------------------------------------------------
1 1 1 Coding 0
2 2 3 Dating 1
3 2 3 Show Me 0
4 2 3 See Me 0
5 3 3 Break Ups 1
6 3 3 True Love 0
private
private_id post_id authorized_user_id
-----------------------------------------------
1 2 2
2 2 5
3 2 6
4 5 2
threads
thread_id user_id post_count
------------------------------------
1 1 1
2 3 3 | When outputted in php, we should subtract the correct COUNT
3 3 2 | from this depending on the user viewing the thread like above.
So, we have the total number of threads with all messages in this thread. But if we pull this out with a mysql query, all users will see all lizzy post_counts for every thread she has, and instead, only lizzy and any users she allowed to view certain posts in the thread should see the correct visible not closed account for their. What would be the most efficient way to output the counts as a string (post_count_if_not_authorized) so that we can subtract it from the post_count to show each user the correct score for them?
Something like the bottom is what I get (doesn't work of course):
SELECT DISTINCT t.thread_id, t.post_count, t.COUNT(*)
FROM threads as t
JOIN posts as p on p.user_id = t.user_id
LEFT JOIN private pv on pv.post_id = p.post_id
WHERE t.user_id='3'
AND (p.private = 0) OR (pv.authorized_user_id = {$logged_in_id} and p.private = 1)
UPDATE:
(t.user_id = '3' in WHERE clause for lizzy in this example and if $ logged_in_id should give correct count depending on user, like counters in user table above)
Here is a fiddle .
The result should look like this: if tony ($ logged_in_id = 1) is looking at lizzy (user_id = 3), running threads:
thread_id post_count_final (this should have the correct count for this user($logged_in_id) to see, all posts by lizzy - private posts by lizzy that this user is not authorized to see)
2 2
3 1
If steph ($ logged_in_id = 2) is looking at lizzy (user_id = 3), running threads:
thread_id post_count_final (this should have the correct count for this user($logged_in_id) to see, all posts by lizzy - private posts by lizzy that this user is not authorized to see)
2 3
3 2
(Note: The upper right side next to the users table shows how these numbers are produced.)
source to share
SELECT
t.thread_id, t.post_count,
COUNT(IF(ISNULL(pv.private_id) AND p.private='1' AND p.user_id<>'1', null, 1))
FROM threads as t
JOIN posts as p on p.thread_id = t.thread_id
JOIN users as u on u.user_id = p.user_id
LEFT JOIN private as pv on pv.post_id = p.post_id AND pv.authorized_user_id='1'
JOIN users as auth on auth.user_id = '1'
WHERE p.user_id='3' AND t.user_id='3'
GROUP BY t.thread_id;
This should work even if lizzy (login) is looking at lizzy. Adding COUNT (*) will return the same value as t.post_count. You can completely eliminate the use of the threads table and do the counting in the query together, although the query will be heavier.
source to share
If you use the correct syntax group by
does it work?
SELECT t.thread_id, t.post_count, COUNT(*)
FROM threads as t
JOIN posts as p on p.user_id=t.user_id
LEFT JOIN private pv on pv.post_id=p.post_id
WHERE (p.private=0) OR (pv.authorized_user_id={$logged_in_id} and p.private=1)
group by t.thread_id, t.post_count
source to share