Selecting what’s NOT there in MySQL
Relational databases are a fantastic way to organize data. Sometimes though, I have to come up with contrived solutions to render results that should, in my mind, be much simpler generate.
Here was my problem:
I’m working on a client project right now that is basically a glorified upload/download center with users. He can upload documents and then assign permission as to whether or not each user can view the document on a user by user basis. It’s small-scale and simple and, up until this dilemma, has been a piece of cake to implement.
Because I’m so nice, on a the document overview pages, I’ve provided an “at-a-glance” list of his users sorted into two columns - those who can view this particular document, and those who can’t.
Building the list of users who can view it was easy. I looked at two tables - my users table and my permissions table. To build this result set I just query:
SELECT user.id, user.name FROM users,permissions WHERE permission.user_id=users.id AND permission.project_id=[dynamically generated project id]
Where I found myself getting tripped up was when I tried to build the list of users who could not view this particular document. Because a user can have access to multiple documents, simple changing the last part to != wouldn’t work - you could, potentially, have the same name listed as being able to see this document and not being able to see it.
I realized I could just grab all of the users and perform a second query for each user as I cycled through them in a script, but that’s lazy. I’m also trying to hone my MySQL chops, and figured there had to be an elegant solution out there.
Here’s what I eventually came up with:
SELECT users.id,users.name IF(FIND_IN_SET([dynamically generated project id], GROUP_CONCAT(project_id)),’true’,'false’) AS project_match FROM users, permissions WHERE users.id = permissions.user_id AND project_match=’false’ GROUP BY users.id
It wasn’t everything I hoped for - the result set is obviously still returning a list of users that is not exclusively those who CANNOT see the document - but it gets the job done in a single query and only requires a quick true/false check in my script as I cycle through the users.
Anyone out there ever had a similar problem? Do you have a better solution? I’d love to hear feedback!