I want to generate a search query for "Items". This will allow users to search for albums and photos because both of them have a one-to-one relationship to items. To accomplish this, I will need to come up with a query that performs a union on the results from friends, friends of friends, public and everything in the white list. I will also have to remove results from the black list. As input, I will receive the user's id and their search query. Please check the attached schema.
Each Item has a privacy_id which states whether or not it sharable to no one, friends, friends of friends, or everyone. There is also an privacy_include and
privacy_exclude table that are the white list and black list respectively.
As a user performing a search I should receive only the items I am allowed to see. So automatically give the user results that are labeled public, then search through their friends to find photos only sharable, and etc.
It would help if you had the friends table... which I have attached with the updated schema.
A note about the friends table... to prevent duplicate entries in the table, the user with the greater user id will always be user 2 and the user with the smaller id will be user 1 in the table.
Deadline is very important!
Payment: after successful completion of work.