Databases
StackOverflow2013
Postsdbo
POMySQL - Private Posts Display Correct Count
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL - Private Posts Display Correct Count
    primarykey
    data
    text
    <p>So I have 4 tables: users, posts, private, threads. In this example, lizzy created 2 private posts in different threads:</p> <p>'Dating' post is for users 2,5,6 and herself to see the correct count of posts in that thread. </p> <p>'Break Ups' post is for user 2 only and herself to see the correct count of posts in that thread. </p> <p>Displaying the correct count depending on the user viewing the thread is the issue I am having. Here, we are focusing on lizzy, her threads, and post counts:</p> <pre><code>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. </code></pre> <p>So basically, we have a total thread count with all posts in that thread. But if we pull that out with a mysql query, all users will see all lizzy's post_count for each thread she has, when instead, only lizzy and any users she authorized to view certain posts on a thread should see the correct visible non private count for them. What would be the most efficient way to pull out the counts as a row (post_count_if_not_authorized) so we can subtract it from the post_count to show each user the correct count for them only?</p> <p>Something like the below is what I am after (not working of course as is):</p> <pre><code>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) </code></pre> <p><strong>UPDATE:</strong></p> <p>(t.user_id='3' in the WHERE clause is for lizzy in this example, and if $logged_in_id should give the correct count depending on the user, like the counts in the user table above)</p> <p>Here is a <a href="http://sqlfiddle.com/#!2/51286/7" rel="nofollow">fiddle</a>.</p> <p>The output should look like this if tony ($logged_in_id=1) is viewing lizzy's (user_id=3) started threads:</p> <pre><code>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 </code></pre> <p>If steph ($logged_in_id=2) is viewing lizzy's (user_id=3) started threads:</p> <pre><code>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 </code></pre> <p>(Note: The top right part next to the users table shows how these numbers are derived.)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMaverick
    1. USMaverick
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL - Private Posts Display Correct Count
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL - Private Posts Display Correct Count
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL - Private Posts Display Correct Count
      1. USMaverick
      1. VTBountyStart
    1. COCan you explain you users table data?
      singulars
      1. POMySQL - Private Posts Display Correct Count
      1. USMuhammad Raheel
    2. COIt's better formatted now, just a user_id and user_name
      singulars
      1. POMySQL - Private Posts Display Correct Count
      1. USMaverick
    3. COFor clarity, can you add the desired result to your post above.
      singulars
      1. POMySQL - Private Posts Display Correct Count
      1. USStrawberry
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload