ZF-34: Zend_Db_Table: Add join() method

Issue Type: Patch Created: 2006-06-19T23:00:32.000+0000 Last Updated: 2007-07-05T14:43:07.000+0000 Status: Resolved Fix version(s): - 0.9.0 (17/Mar/07)

Reporter: Zend Framework (zend_framework) Assignee: Bill Karwin (bkarwin) Tags: - Zend_Db_Table

Related issues: - ZF-1063




first my compliments on Zend_Db_Table, I really like it.

What do you think about adding a join() function to Zend_Db_Table. This would made it easier to use the join() functions of Zend_Db_Select.

Ok an example:

<pre class="highlight">
  `id` int(11) NOT NULL auto_increment,
  `subject` varchar(100) NOT NULL default '',
  `text` text NOT NULL,
  `categoryid` int(50) default '0',
  `author` text,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `image` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)

CREATE TABLE `categories` (
  `id` smallint(6) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)

news.categoryid is related to

class News extends Zend_Db_Table {

class Categories extends Zend_Db_Table {

$news = new News(...);

$news->join(new Categories(...), 'categoryid', 'news.categoryid =, '*');
//?? ->innerjoin(),->leftjoin()...

It would be very nice if the join() function would handle all nameclashes of column names (,

What do you think?

Regards, Johannes


Posted by Nils W (nils) on 2006-09-14T09:59:38.000+0000


I had the same idea. So I think about it and I find a solution, which maybe is a little bit dirty but it works. Perhaps you have ideas to make it better.

I build a class which extends the Zend_Db_Table.

First of all I add this

<pre class="highlight">
class Custom_Zend_Db_Table extends Zend_Db_Table {
    protected $_select;

then I had to chanced the _setup() function:

<pre class="highlight">
protected function _setup() {
    //This is code from the original _fetch() function.
            $this->_select = $this->_db->select();

            $this->_select->from($this->_name, array_keys($this->_cols));

I initialise 'select' already in the _setup() function. Like this I have the possibility to add a function, which adds joins:

<pre class="highlight">
public function join($join) {
      $this->_select->join($join['name'], $join['name'].'.'.$join['primary'].' = '.$this->_name.'.'.$join['fid'], $join['cols']);

You can see that you need an array, with all the things the join() function from Zend_Db_Select needs. Maybe you have better ideas on this part...

And last but not least you have to change the _fetch() function. I overwrite it like this:

<pre class="highlight">
protected function _fetch($type, $where = null, $order = null, $count = null, $offset = null) {
            // the WHERE clause
            $where = (array) $where;
            foreach ($where as $key => $val) {
                // is $key an int?
                if (is_int($key)) {
                    // $val is the full condition
                } else {
                    // $key is the condition with placeholder,
                    // and $val is quoted into the condition
                    $this->_select->where($key, $val);

            // the ORDER clause
            $order = (array) $order;
            foreach ($order as $val) {

            // the LIMIT clause
            $this->_select->limit($count, $offset);

            // return the results
            $method = "fetch$type";
            return $this->_db->$method($this->_select);

I only delete the first lines and change $select to $this->_select

This is my first change at any Zend class so properly it isn't really good. But I hope you can use this idea and make it better ;o)

Have fun!


Posted by Nils W (nils) on 2006-09-15T02:37:37.000+0000

I forgot an example, so here it is

I have a news table and a user table. Every news a written by a user. First I have the news and user class

<pre class="highlight">
    class news extends Custom_Zend_Db_Table {


<pre class="highlight">
    class user extends Custom_Zend_Db_Table {


And this how I use the join function in the controller:

<pre class="highlight">
 // ********** news from database ********************************
                $news = new news();
                  $users = new user();
                  $join = $users->info();  // with this I get the name of the user table and the primary key
                  $join['fid'] = 'user_id';     // add the id from the news table for the join condition
                  $join['cols'] = 'firstname, surname';   // add the cols you want to have from the join-table
    $news->join($join);  // here I use the join function
                  //where clause
                  $where = 'exp_date >= '.time();   // and I add a where clause
                  $vars['news'] = $news->fetchAll($where)->toArray();
        // ********** news from database ********************************

that's it....

Posted by Bill Karwin (bkarwin) on 2006-11-13T15:15:30.000+0000

Changing fix version to 0.8.0.

Posted by Bill Karwin (bkarwin) on 2007-01-05T17:06:23.000+0000

Recategorize as Zend_Db_Table component.

Posted by Philipp F├╝hrer (flipkick) on 2007-01-31T20:59:06.000+0000

i did it this way:

<pre class="highlight">
protected $_join = array();

public function join($table2, $onCol, $onCol2)
 $tmp = array_keys($this->_db->describeTable($table2));
 foreach ($tmp as $native) {
  $this->_cols[$native] = self::$_inflector->camelize($native);
 $this->_join = array($table2, $onCol, $onCol2);        

<pre class="highlight">
// the JOIN clause
if (!empty($this->_join)) {
  "{$this->_name}.{$this->_join[1]} = {$this->_join[0]}.{$this->_join[2]}");

problems: * no multiple joins in one query possible * table1 and table2 must have different column names (to fetch all data properly) * no updates/inserts/deletes in linked table

Posted by Bill Karwin (bkarwin) on 2007-03-16T00:59:47.000+0000

Resolved by solution for table relationships, see ZF-1063.

Posted by Bill Karwin (bkarwin) on 2007-03-16T01:00:09.000+0000

Linking as duplicate of ZF-1063.

Have you found an issue?

See the Overview section for more details.


© 2006-2022 by Zend by Perforce. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.