wheresrhys

SQL was the sight of my first forays into programming, back in the days when I managed records for an educational project and came to the heretic conclusion that MS Access was better suited to the task than Excel. But that’s more or less where my learning of SQL stopped, and even then it was limited to SELECT, WHERE and ORDER BY statements (I let Microsoft’s wizards do all the hard work of building multiple table queries).

Fast forward to yesterday and I decided to finally bite the bull by the horns. The back-end of my latest educational endeavour was, I guessed, suffering in speed due to the fact that I made no use of JOIN on my MySQL tables; each time I wanted to get records related to records in another table I would use nested loops in php to get the related records for each row.

In my defence, I largely chose this approach due to another 3 major flaws in  Zend Framework\’s documentation:

1. It doesn’t explicitly mention that when you build a Zend_Db_Select query based on a Zend_Db_Table class (ie a Model) the FROM clause of the query is automatically filled in. Attempting to fill it in yourself causes an error.
2. It doesn’t mention anywhere that in order to use JOIN within a Zend_Db_Select query based on a Zend_Db_Table class you need to use the ->setIntegrityCheck(false) method. Without this all manner of confusing errors occur.
3. This wording: “You can not specify columns from a JOINed tabled to be returned in a row/rowset. Doing so will trigger a PHP error” , coupled with the fact I was getting lots of errors led me to believe that Zend had neglected to add JOIN functionality to its models, so I dropped that line of attack. (In fact, all that quote means is that you cannot change the default behaviour, which is to fetch all columns).

But now I have of course overcome all this, and have managed to eg reduce about 30 lines of code (get a teacher’s classes, then get these classes’ assignments, then get all the attempts at these assignments, and for each of these get the individual puzzle solutions submitted) to a single line using 3 RIGHT JOINs and one INNER JOIN. It’s much neater, and I can only guess at the vast improvements in speed it brings; my guess is “oodles”.

As well as improving my application, learning about Zend_Db_Select (the documentation to this is remarkably well written, considering the surroundings), via the __toString() method, has increased my understanding of the underlying SQL to the point where for the first time I can write non-trivial queries from scratch – eg updating a field based on a join with another table – , which is a great addition to my programming armoury.