I'm building a site that requires sharing with either group(s) or individual user(s). I know for a fact that google does not use mysql, but i was wondering how i could replicate such feature on my site. On g+, one can:
- Share a post with the "public" (everyone can see it).
- Share a post with "all circles" (everyone in your circles can see it).
- Share a post with both circles and individual users. E.g. post = "my first post" and is shared with family,friends, user 1(Joey tribbiani), user 2 (Ross geller) etc.
Conditions:
- If a post is shared with a circle and a new user is added to the circle, then (s)he should be able to see all the previous posts shared with that circle.
- If a user is removed from a circle. (s)he cannot see posts shared with that circle except posts (s)he has commented on.
Currently my database tables look like this.
Circle_category
Cat_id
Cat_name
user_id
Posts
post_id
user_id
post
is_public
all_circle
Post_to_circle
entry_id
post_id
cat_id
Post_to_user
entry_id
post_id
user_id
Post a user in family circle(which is in Circle_category with cat_id of 1 ) can see
- They can see posts that are public.
- They can see posts shared with all circles.
- They can see posts shared with family circle.
- They can see posts shared with them (Individual user).
SQL
SELECT p.* FROM posts p
JOIN Post_to_circle pc
ON p.post_id = pc.post_id
JOIN Post_to_user pu
ON p.post_id = pu.post_id
WHERE p.is_public = 1
OR all_circle = 1
OR pc.cat_id = $cat_id
OR pu.user_id = $user_id
Quetions:
Firstly, I've been able to get posts from case 1(see all public post), case 2 (Posts shared with all circles) but the other 2 cases do not work. I thought about it and saw that the main problem is that i specified the where clause to get posts where p.is_public = 1 which means it neglets rows where p.is_public = 0. How do i update the query so it shows posts covering all four cases and also covers the conditions we talked about at the beginning.
Secondly, is there a better way to structure my tables? i'm not sure i'm doing it the right way.