Omri Shiv's Blog

I have been working on a Rails application for a demonstration at Case. The application is a pretty standard CRM system whose use is to allow faculty on campus to find one another when they need specific things done that are out of their expertise. For instance, a medical student may have a business idea where he may need a rapid prototyping machine with a specific process. He can log onto the system and be connected with any faculty member who has listed this ability.

Apart from being secured behind Case’s Single Sign On as well as the possibility of integrating the Case LDAP Phone Book, the app has few “Wow” technologies. It includes auto-complete on almost all text entry fields so that as a faculty member starts looking for a “Rapid Prototyper”, the minute they type the first R, any machine that includes an R will show up. As they continue typing, the list will be weened out.

What is not so notable but took an incredibly amount of work is one of the requirements:
For example, if a user comes to the system in need of a faculty member with specific expertise, they should be presented with every single expertise present in the system. There are checkboxes next to each one and the user can be as broad or as narrow with their criteria as possible. If the user doesn’t check any boxes, every faculty member (or equipment) is returned. If they check enough boxes so that no member matches the criteria, none are returned.

Here is where it gets interesting.

We have a users table which has all the personal information. We have an equipment table which contains all the equipment. Lastly, we have a process table which contains a list of process names.

There is also a table in the database that links all of these up. This table has a row for each process that is associated with a user and/or a machine. So for instance, if, in my process table, I have a process named “cooking” and it’s table id is 3, and in my user table, I am user 4, there would be a row in this pivot table that would say user_id 4, process_id 3.

The important thing to understand is that one user may be associated with multiple processes, so for instance, we can have multiple rows like this:

user_id process_id
4 3
4 5
3 2
4 2

When the page is loaded, the process database is queried and a list of processes as well as checkboxes is created on the page. The user will then select the processes they are interested in. When they click submit, the magic happens.

Each checkbox value is actually the number of the process_id. Therefore, when the user clicks submit, the application has a list of these IDs that the user is searching for.

A SQL query for getting a user with an ID would look something like this:

SELECT * FROM user_processes WHERE process_id = 3

The question is, how do we get more specific in our query. Let’s say we wanted any user that could cook as well as “chop” (id 5). Instinctively, we would come up with this query:

SELECT * FROM user_processes WHERE (process_id = 3 AND process_id = 5)

However, it turns out that this simple idea turned into a week long headache for me. After trying every possible google search, I finally stumbled upon this question posted on Stack Overflow and came up with what may be the most complex SQL query I have ever seen (though, probably not complex for SQL gurus):

					SELECT  *
					FROM    (
					        SELECT  DISTINCT user_id
					        FROM    user_processes
					        ) mo
					WHERE   NOT EXISTS
					        (
					        SELECT  NULL
					        FROM    ("+@string+

					                ") list
					        WHERE   NOT EXISTS
					                (
					                SELECT  NULL
					                FROM    user_processes mii
					                WHERE   mii.user_id = mo.user_id
					                        AND mii.process_list_id = list.process_list_id
					                )
					        )")

Where @string is dynamicaly generated by Ruby based on the checkbox parameters:

			if (params[:query].length == 1)
				@string =  "SELECT "+params[:query][0]+" AS process_list_id"
			elsif (params[:query].length > 1)
				@string =  "SELECT "+params[:query][0]+" AS process_list_id"
				1.upto(params[:query].length-1) do |n|
					@string+=(" UNION ALL SELECT "+ params[:query][n].to_s)
				end
			end

This was quite a bit of work, but I’m glad it’s finally done. It was a great learning experience and I have to thank my friend Alex Budkie for putting up with me while I complained to him about how much easier this should have been (and for helping me interpret the Stack Overflow response).