setting content to be viewable by friends only

Question

Hi,

Say I have a table that holds content:

Content(contentID, title, body, privacyType)

Privacy type can be:

Public = 1 (anyone can view it) Private = 2 (only the author can view it) Friends = 3 (only friends can view it)

I have a users table:

Users(userID, username)

THen I have a mapping table for the users friends:

Friends (userID, friendID)

Now when I am listing all the content, it should list content that is either public OR I am a friend of that user (for a logged in user)

How would I do a query for this?

Answer 1

You didn't provide the field where the content authorship is stored, so I'll assume it's content.author:

SELECT  *
FROM    content
WHERE   public = 1
        OR
        (
        public = 3
        AND EXISTS
        (
        SELECT  NULL
        FROM    friends
        WHERE   userId = @me
                AND friendID = content.author
        )
        )

, or better this:

SELECT  *
FROM    content
WHERE   public = 1
UNION ALL
SELECT  *
FROM    content
WHERE   public = 3
        AND EXISTS
        (
        SELECT  NULL
        FROM    friends
        WHERE   userId = @me
                AND friendID = content.author
        )

, if your author field is selective and sorting is cheaper than filtering.

Also note that if your friendship is a symmetrical relation (i. e. if a userA is a friend to userB, then userB is a friend to userA), it's better stored in a table like this:

userA  userB

with a check constraint:

userA < user B

Yuo always put the user with least id first.

In this case, your query like this:

SELECT  *
FROM    content
WHERE   public = 1
UNION ALL
SELECT  *
FROM    content
WHERE   public = 3
        AND EXISTS
        (
        SELECT  NULL
        FROM    friends
        WHERE   userA = @me
                AND userB = content.author
        UNION ALL
        SELECT  NULL
        FROM    friends
        WHERE   userB = @me
                AND userA = content.author
        )