CODEWITHSUNDEEP
phpmysqlsqloptimizationblacklist
Gury Max
Gury Max

Reputation: 161

Best way to enable blacklists for users

I want to allow my users to create blacklists of content sources (users/categories/words/?). They shouldn't be able to see any content from these sources.

For example: If user A blacklisted user B and then user B uploaded a picture, then user A request to see the gallery he won't see picture from B, but he'll be able to see pictures from user C, D, ...

The problem occurs when one user built a big blacklist (e.g. 100 sources). Then the SQL queries will be really long and complex ("...and author != 'B' and category != 'C'...") what will eventually kill my server.

What are the other ways to handle this problem?

Upvotes: 4

Views: 381

Answers (3)

Nin
Nin

Reputation: 3020

The query can look complex but that doesn't really matter. Make sure you index these columns and preferably use numbers for the categories, authors, etc.

So then you'll have a query like

SELECT * FROM .... WHERE author_id NOT IN (1,12,567,6788) AND category_id NOT IN (6654,23245,89795)

Upvotes: 1

F.P
F.P

Reputation: 17831

First of all, create indices on every column that can be ignored. This way, your match criteria will be found a little bit faster.

Then you could create an in-between table that collects relations between a user and the content he has blacklisted.

Maybe something like this:

userId | blacklistType | blacklistId
1      | user          | 2
1      | category      | 12
1      | word          | 4

Now if you want all categories for user 1, you could make the query

SELECT *
FROM categories
WHERE NOT EXISTS (
    SELECT userId
    FROM blacklist
    WHERE userId = 1
        AND blacklist.blacklistType = 'category'
        AND categories.id = blacklist.blacklistId
)

(I'm not quite sure about the syntax here, but you should get the idea, I hope)

Upvotes: 1

Chris Gessler
Chris Gessler

Reputation: 23123

It sounds to me like you're using dynamic SQL to build this query. You should have the blacklist stored in a table related by UserId, then you can write a stored procedure that uses NOT IN or NOT EXISTS to build the final resultset.

Upvotes: 1

Related Questions