Friday (day 5)

Constructing SQL statement for the search
Yep, today I need to finish the Profile search for the admin part. As I mentioned, the search is only by fields of experience, so there is no big and scary where clause /especially if you want to cover all cases, like splitting the words with AND, OR etc. will cause huge where with all possible combinations/.

There are 2 specific requirements here: The results need to be ordered by relevance and the report need to show fields of competence which are match to the search criteria next to each row /user/.

What does it means? Let’s say there are 2 users. First one has following skills like Java, PHP, MySQL, HTML and JavaScript, the second one knew only JavaScript and HTML, so when we search for users matching criterion PHP and HTML, the search results need to be ordered like this:

User1 – matching fields: PHP, HTML – relevance 2 criteria matched
User2 – matching fields: HTML – relevance: 1 criteria matched
and so on.

At first glance it look like I had to build huge SQL including few sub queries or functions for fetching the fields of relevance and matching fields and I was prepared for at least one day to accomplish this task, but in fact it was very fast.

The solution was in GROUP_CONCAT function in mysql. as well as few groupings. I love Mysql!!! 🙂

Here is the final sql which drive this report:

Working over advanced tab navigation
As I mentioned earlier, I’ve decided to split the profile into 4 main parts: General information, Fields of knowledge, Additional information and Disclaimer confirmation. I’ve used the JS Tabber class which working fine, but I’ve decided to duplicate the switch between tabs with next-prev buttons – I wanted to create something like step-by-step wizard.

There were few problems, mostly how to handle events and translate them to the JS Tabber script, but reading the manual and digging the code of the library I’ve managed to handle this. I believe that there is most elegant way to make this script, but the desired functionality is done and most important – it’s working.

Building the interface for the search functionality
Returning on the search functionality I’ve finally decided to store the search criteria in the database. Someone could say that this is not appropriate and the better approach is to store the search results. To prevent double work I’ve spoke with the responsible user from the client side and we decided that first option is what they need.

Briefly, interface of storing the results is very simple. There is a link which store the selected criteria into a table allowing users to assign a name of the set. The second part is to load the stored criteria into the search form, so users can directly submit the form, or they could change criteria.

Working with Ajax in CakePHP is piece of cake 🙂

Conclusion of the day:
I think it’s almost done. There are need to finish the design as well as testing it on different browsers than Firefox /which I know it will be a pain :(/

To be continued…

See also
Building cake application (Part I)
Building cake application (Part II)
Building cake application (Part III)
Building cake application (Part IV)