The best way to enable blacklists for users

Question

I want to allow my users to create blacklists of content sources (users / categories / words /?). They should not see any content from these sources.

For example: if user A is blacklisted by user B, and then user B uploaded the photo, then user. A request to see the gallery, he will not see the image from B, but he will be able to see the images from the user C, D, ...

The problem occurs when one user has created a large blacklist (for example, 100 sources). Then the SQL queries will be very long and complex ("... and author! = 'B" and category! =' C '... "), which will ultimately kill my server.

What are other ways to solve this problem?

Answer 1

It seems to me that you are using dynamic SQL to build this query. You must have a blacklist stored in the table associated with UserId, then you can write a stored procedure that uses NOT IN or NOT EXISTS to create the final set of results.

Answer 2

First of all, create indexes for each column that you can ignore. This way, your eligibility criteria will be found a little faster.

You can then create a staging table that collects the relationship between the user and the content that he blacklisted.

Maybe something like this:

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

Now, if you need all categories for user 1, you can request

 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 of the syntax here, but you should get this idea, I hope)

Answer 3

The query may look complicated, but it doesn't really matter. Make sure you index these columns and prefer to use numbers for categories, authors, etc.

So you will have a query like

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