Jay Paroline

They say that Computer Scientists aren’t satisfied with putting other people out of jobs; they want to put themselves out of a job as well. In that vein I am writing a function that, given basic information about our database tables and their relationships, can build efficient queries automatically based on a complex filter consisting of:

• The information requested
• Any combination of IDs to filter the results based on

For example, you can tell it that you want the names and IDs of Playlists belonging to a user with a certain artist and my function will build the SQL on the fly based on what it knows.
This is extremely useful because I am working on a super secret project* with Katy and her app has no knowledge of hte database but needs to be able to ask for some extremely specific sets of data depending on user behavior. Instead of manually writing N! queries to handle every possible combination of requests, my function does all of the heavy lifting for me.

The code isn’t quite done yet but the code that figures out the join sequence and which key to join on is done, and surprisingly it only takes about 10 lines of code. I still need to add code to handle special cases, and I need to write the code that takes that sequence and turns it into real live SQL so it will surely grow, but that is still far less than I imagined would be required to get this far.

*not actually a secret project, but I haven’t written about it yet and it’s beyond the scope of this post.