CRUD with PHP Doctrine
This piece is building on the prior tutorial, we now have Retrieve, Update and Delete in addition to the Create stuff. The prior tutorial has also been updated since the fromArrayExt method needed some adjustments to manage updating.
Update: Live example of the search part at the dive site (under construction). Try Whalesharks and Night Life, it will return a nice result.
Let’s start in the models, not much has happened here, the only change is the setUp method in Aspect_list:
public function setUp(){
$this->hasOne('Destination as Dest', array('local' => 'dest_id', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
$this->hasOne('Aspect as Asp', array('local' => 'aspect_id', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
}Note the ‘onDelete’ => ‘CASCADE’ parts, if a destination or an aspect is deleted we will delete all connections to it in this table too, pretty self evident. Note that the above way of doing this utilizes INNODB, if you want MyISAM Doctrine will allow you to do this through Application-level Cascades, check the manual and the chapter with this headline for more info. The above way of doing it felt easier though, that’s why I went with it.
Let’s continue with the retrieve part in DestinationCtrl:
function search(){
$res = $this->q->select('me.*, a.*')->from('Destination me')
->innerJoin('me.Asp a')->whereIn('a.id', $this->p['Asp'])
->groupBy('me.id, a.id')->fetchArray();
$this->assign('destinations', Common::sort2dBySubLength($res, 'Asp', 'desc'));
return $this->fetch('search_result.tpl');
}We search only by aspects, no free text or anything, all destinations that have at least one of the checked aspects will be retrieved, all matching aspects will be joined to this set under the ‘Asp’ key. Note the use of models and relations in the DQL, this is not SQL.
In the current application, the above search function is a little bit different to account for the fact that it is getting the data as a json string (by way of Ajax) and needs to echo the result back:
function search(){
$asp = json_decode($this->p['asp']);
$res = $this->q->select('me.*, a.*')->from('Destination me')
->innerJoin('me.Asp a')->whereIn('a.id', $asp)
->groupBy('me.id, a.id')->fetchArray();
$this->assign('destinations', Common::sort2dBySubLength($res, 'Asp', 'desc'));
echo $this->fetch('search_result.tpl');
exit;
}The whole array is also sorted so that we have destinations with many matching aspects on top:
static function sort2dBySubLength($arr, $key, $order){
foreach($arr as $sub) $sc[] = count($sub[$key]);
$order = $order == 'desc' ? SORT_DESC : SORT_ASC;
array_multisort($sc, SORT_NUMERIC, $order, $arr);
return $arr;
}See the sort 2d array tutorial or fluent arrays and strings for more info on how to work with array_multisort.
Let’s move on to the updating:
function createPop(){
$record = $this->find();
$pop = $record->toArray();
$pop['Asp'] = $record->fluent2d('Asp')->flatten('id')->c;
return $pop;
}
function uForm(){
$this->loginChk();
$this->setS('pop', $this->createPop());
return parent::uForm();
}Updating aspects is trivial, they manage with the default stuff in Ctrl with the only change of prepopulating with database data all the time. The checkbox logic though, requires some custom code as you can see.
Since we are not concerned with validating any input here we can set the prepopulation to always rely on the current data in the database, we don’t need to store any temporary form values (that didn’t validate properly) in the session (same goes for the Aspects as stated above). See the second part in this series for info on how to validate form input with PHP Doctrine.
CreatePop() is solely there to populate the checkboxes. We begin with retrieving the current Doctrine_Record, turn it into an array and put an array of id values in the ‘Asp’ key. You might wonder what fluent2d does, it’s part of the fluent arrays and strings library. The fluent2d method in Mdl looks like this:
function fluent2d($rel){
return Arr::fluent2d($this->$rel->toArray());
}Where $rel is Asp in our case. To list everything in order to create the interface you see in the picture above is trivial, in DestinationCtrl:
function assignAllAspects(){
$this->assign('aspects', $this->getCtrl('aspect')->findAllArr());
}
function listAll(){
$this->loginChk();
$this->assign('destinations', $this->findAllArr());
$this->assignAllAspects();
return $this->fetch('list_all.tpl');
}That’s it for now, feel free to download this tiny Smarty and Doctrine framework. Note that for this to work you have to put Smarty and Doctrine in the lib folder. There is a login interface involved, just click submit there without entering anything. There is also an SQL file in the trip_selector folder if you want to try this out with some test data (same as in the picture above).