How To Sort A Zend_Db_Table_Rowset
So you figured out how to define the relationships between your Zend_Db_Tables and you have issued a call to findDependentRowset(). You get your Rowset back but you need to sort the results by one of the columns in the dependent table. How do you do that?
The short answer is, you can’t! Unfortunately, this functionality won’t be available until the 1.5 release of the Zend Framework. But you can write your own utitlity function to sort your Rowset for you.
In my project I am working with Patients and their Medications. There is a one to many relationship between patients and medications. One patient can have many medications but a medication belongs to only one patient. I have the following database tables and classes (stripped down for the sake of this example):
-
CREATE TABLE patients (
-
id INT AUTO_INCREMENT NOT NULL,
-
name VARCHAR(50),
-
PRIMARY KEY(id)
-
);
-
-
CREATE TABLE medications (
-
id INT AUTO_INCREMENT NOT NULL,
-
patient_id INT,
-
name VARCHAR(50),
-
PRIMARY KEY(id)
-
);
-
// file: Patients.php
-
-
class Patients extends Zend_Db_Table_Abstract {
-
protected $_name = “patients”;
-
protected $_dependentTables = array(“Medications”);
-
}
-
// file: Medications.php
-
-
class Medications extends Zend_Db_Table_Abstract {
-
protected $_name = “medications”;
-
protected $_referenceMap = array(
-
‘Patient’ => array(
-
‘columns’=>‘patient_id’,
-
‘refTableClass’=>‘Patients’,
-
‘refColumns’=>‘id’
-
)
-
)
-
}
Suppose then that you want to get an alphabetical list of all the medications a particular patient is taking. The code looks like this:
-
// Make sure your Zend_Db_Table class is loaded
-
Zend_Loader::loadClass(“Patients”);
-
-
// Instantiate your Zend_Db_Table class
-
$patients = new Patients();
-
-
// Select Zend_Db_Table_Row for patient with id of 12
-
$patient = $patients->find(12)->current();
-
-
// Select all of the medications for the patient
-
$medications = $patient->findDependentRowset(‘Medications’);
At this point, $medications now contains a Zend_Db_Table_Rowset that holds a list of Zend_Db_Table_Row objects representing the medications associated with our patient. Suppose now you want to sort the list of medications alphabetically by the name of the medication.
I was irked to learn that there was no way to tell findDependentRowset() to order by one of the selected columns. So I decided to start a Utilities class of static methods that I can use for situation such as this. I wrote the function sortRowsetBy()
-
class DU_Utils {
-
-
/**
-
* Sort a Zend_Db_Table_Rowset by the specified column
-
*
-
* @param Zend_Db_Table_Rowset - The Rowset to sort
-
* @param String $colName - The name of the column by which to sort the Rowset
-
* @return Array - An sorted array of Zend_Db_Table_Row objects
-
*/
-
public static function sortRowsetBy($rowSet, $colName, $direction=‘desc’) {
-
foreach($rowSet as $key => $row) {
-
$rows[] = $row; // Convert Rowset into an array of rows
-
$vals[$key] = $row->$colName; // Create array out of specified column name
-
}
-
// Sort the $rows array based on the $vals array
-
($direction == “desc”) ? array_multisort($vals, SORT_DESC, $rows) : array_multisort($vals, SORT_ASC, $rows);
-
-
return $rows;
-
}
-
-
}
The function makes use of a little known PHP function called array_multisort(). This is a peculiar little function to grasp but it’s worth figuring out because of it’s power. There is a good bit of documentation on the PHP website about what the parameters mean and what the function does. In a nutshell, this function sorts one array by another. In our context, the first array is essentially the column you want to sort by and the second array is the table you want to sort. In between the two arrays you can pass some flags to specify how you want to sort the array – ascending or descending – and the type of sort you want to perform – regular, numeric, or string. Note, that both arrays must be the same size in order for array_mulitsort() to do its job.
In conclusion, here is the code to sort our Rowset of medications alphabetically by the name of the medication.
-
// Make sure your Zend_Db_Table class is loaded
-
Zend_Loader::loadClass(“Patients”);
-
-
// Instantiate your Zend_Db_Table class
-
$patients = new Patients();
-
-
// Select Zend_Db_Table_Row for patient with id of 12
-
$patient = $patients->find(12)->current();
-
-
// Select all of the medications for the patient
-
$medications = $patient->findDependentRowset(‘Medications’);
-
-
// Sort the medications
-
$medications = DU_Utils::sortRowsetBy($medications, ‘name’);
-
-
// Note that medications is now an array of Zend_Db_Table_Row objects
-
// not a Zend_Db_Table_Rowset after the sort.
-
-
// Loop throught the sort list of medications
-
foreach($medications as $med) {
-
// Do something with each medication
-
print($med->name);
-
print(“<br/>”);
-
}
Fatal error: Call to undefined function get_avatar() in /var/www/common/wpmu/wp-content/themes/devart/comments.php on line 27