Re: revised Zend Db Table and JOINs
|
|
|
||||||||
|
|
|
||||||||
|
In reply to this post
by Simon Mundy
|
||||||||
|
|
||||||||
|
|
In reply to this post
by dinok
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
|
||||||||
|
|
||||||||
|
|
|
||||||||
|
|
In reply to this post
by Simon Mundy
Some javascript/style in this post has been disabled (why?)
Hi DinoHere's my 'Directory' class for you to play with. I've attached the required components as a zip file. Below is some sample code to get you started. It obviously deserves a little more documentation(!) but that's a to-do for me in the coming weeks... There's also improvements I'd like to make - being able to specify request fields, adding a $config option to configure the keyfields, etc - that would be nice but are not essential for me right now. There's also a hook for adding 'filters' (i.e. widgets that can affect the results set, like being able to add search keywords, etc...) but I won't go into that yet. If this suits your needs I can supplement this post. Essentially you define 'keyfields' in your model definition for your directory to show. You then pass your model to a Directory object as well as a Request object - the reason for the request object is that it pulls the page number, per-page field, sort field and direction field from the current request. Then you simply fetch the results from the directory and iterate through the resultset. The model definition below is taken from the Zend_Db documentation on the framework Hope this helps! <?php class Bugs extends Zend_Db_Table_Abstract implements Directory_Interface { protected $_name = 'bugs'; // Required to make/show directory protected $_keyfields = array( 'bug_description' => array( 'title' => 'Name', // Display title 'type' => 'text', // Can be used to allocate column types for display 'sort' => 'asc', // Default sort direction if selected ), 'bug_status' => array( 'title' => 'Status', 'type' => 'text', ), 'reported_by' => array( 'title' => 'Reporter', 'refTable' => array('r' => 'accounts'), // Lookup table for JOIN 'refCond' => 'r.account_name = bugs.reported_by', // Lookup condition for JOIN 'refCol' => 'r.account_name', // Evaluated column name 'type' => 'text', ), 'assigned_to' => array( 'title' => 'Assignee', 'refTable' => array('a' => 'accounts'), // Lookup table for JOIN 'refCond' => 'a.account_name = bugs.reported_by', // Lookup condition for JOIN 'refCol' => 'a.account_name', // Evaluated column name 'type' => 'text', ), 'verified_by' => array( 'title' => 'Verifier', 'refTable' => array('v' => 'accounts'), // Lookup table for JOIN 'refCond' => 'v.account_name = bugs.reported_by', // Lookup condition for JOIN 'expr' => 'CONCAT(v.account_name)', // How an expression can also work 'type' => 'text', ), ); public function getKeyfields() { return $this->_keyfields; } public function selectDirectory() { $select = $this->getAdapter()->select(); } } Controller =========================================== $bugsModel = new Bugs(); $directory = new MyDirectory($bugsModel); $director->setRequest($this->getRequest()); // Uses request to determine page number, sort field, etc. $this->view->directory = $directory; View =========================================== <? $key = $this->directory->getKeyfields() ?> <? $rows = $this->directory->fetchDirectory() ?> <? $sort = $this->directory->getSort() ?> <? $dir = ($this->directory->getDir() == 'asc') ? 'desc' : 'asc' ?> <? $cnt = count($key) ?> <h1>List of bugs</h1> <table> <? foreach ($key as $id => $field): ?> <col /> <? endforeach ?> <thead> <tr> <? foreach ($key as $id => $field): ?> <th><a href="<?= $this->url(array('sort' => $id, 'dir' => ($sort == $id) ? $dir : 'asc')) ?>"><?= $this->escape($field['title']) ?></a></th> <? endforeach ?> </tr> </thead> <tbody> <? if (count($rows)): ?> <? foreach ($rows as $row): ?> <tr> <? foreach ($key as $id => $field): ?> <td><?= $this->escape($row->$id) ?></td> <? endforeach ?> </tr> <? endforeach ?> <? else: ?> <tr> <td colspan="<?= $cnt ?>">No records found</td> </tr> <? endif ?> </tbody> </table> -- Simon Mundy | Director | PEPTOLAB """ " "" """""" "" "" """"""" " "" """"" " """"" " """""" "" " 202/258 Flinders Lane | Melbourne | Victoria | Australia | 3000 Voice +61 (0) 3 9654 4324 | Mobile 0438 046 061 | Fax +61 (0) 3 9654 4124 |
||||||||
Directory.zip (6K) | Free Embeddable Forum Powered by Nabble | Help |
