Skin Spider : Search Page Completed
Posted October 12, 2006 at 2:43 PM by Ben Nadel
To learn all about Skin Spider, click here.
To view the most updated application, click here.
To view the current code base, click here.
Really, all I am doing right now is putting the basic finishing touches on the application. This latest round of updates has beefed up the search page (search.cfm) - you can actually search it now :) The searching is limited, but it works. Currently, you can only search by keyword and tag.
If you look at the top of the page you will see that I am using CFParam tags to make sure that all of the search criteria have a default value. This makes building the search query much less complicated because you can assume that data exists and that it is of a specific type (ie. numeric, string, date, etc). And, speaking of specific type, notice that all of the numeric CFParam tags are in a CFTry / CFCatch block. That is because if someone, for some reason, passes in a non-numeric value (via a hack or the Url for instance), the CFParam tag will throw an error. The CFTry tags provide very fine-tuned error handling. Notice that when I catch a CFParam error, I am assigning a default value to it. Again, this allows my logic in the search query to be much less complicated.
I used to put all the numeric CFParam tags inside one CFTry / CFCatch block and then if any one of them threw an error, I would set them all to defaults. I have moved away from that. Why should one bad apple ruin the bunch? Now, I do CFTry / CFCatch on individual values so that the rest can param regardless of individual errors. This is more work and more typing, but I find this leads to more "expected" results.
Of course, a search page is such mission-non-critical data. You might have pages where you want to set defaults for all values if ANY error occurs. I have not come across such a scenario, but I assume it exists somewhere in someone's business logic.
Also notice that I am putting all the search criteria in the REQUEST.Attributes object. If you will recall, the REQUEST.Attributes object is the union of the ColdFusion URL and ColdFusion FORM scopes. This way, I don't have to care about the search criteria coming from the search form (FORM scope) or some other page, passed in via the URL (URL scope). Again, this just makes our life so much more simple.
Let's take a look at the search query. Remember that all of our database is represented by in-memory ColdFusion queries. This forces us to only use ColdFusion query of queries to get at our database information. ColdFusion query of queries are so freakin awesome, but they do have trade-offs. For one, they cannot do nested queries or sub queries (ex. SELECT * FROM table WHERE id IN ( select .... )). This is something that I have to overcome in our search page; one of the search criteria is tag id. Videos are joined to tags through an intermediary join table. This creates a many to many relationship and would be the perfect place for a sub query.
Since sub queries are out of the question, I have to create a pre-search query where all I get is the join information. In this case, I am getting the video_id from the tag_video_jn table where the tag_id is any tag that we are searching for. Then, once I have that query (a list of matching video IDs), I can use ValueList() in the main search query. Take a look at the code and you will see what I mean.
The search query itself uses some interesting techniques. I am not sure they are really worth going into. They are a bit complicated, but mostly they are just filtering the data based on the search criteria. I don't want to get too deep into SQL since this is about ColdFusion development, but the two do go hand in hand. The code itself is commented very well, so take a look at the search.cfm page to see more about it.
As I started to add more search criteria, maintaining the pagination became very irritating. I knew this would, but I wanted to point it out. Every time I added a new criterion, I had to update the link for the Prev, Next, and Page links for the pagination. I only have a few variables being passed... imagine if I had many, say, from an advanced search page. Obviously, this is something that we are going to have to tighten up in the next round.
Other than the search page, I have also made a minor update to the edit_tag.cfm page. Now, next to each tag listing, there is a link that will take you to the search page, searching on the original tag. Minor, but possibly useful update.
Anyway, the rest of the updates should be basically cleaning up the first round and getting it ready for completion of Iteration One. These are very exciting times.
