Issues

ZF-34: Zend_Db_Table: Add join() method

Description

http://framework.zend.com/developer/ticket/17

Hi,

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:


CREATE TABLE `news` (
  `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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=537 ;

CREATE TABLE `categories` (
  `id` smallint(6) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

news.categoryid is related to categories.id.

class News extends Zend_Db_Table {
...
}

class Categories extends Zend_Db_Table {
...
}

$news = new News(...);

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

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

What do you think?

Regards, Johannes

Comments

Hi,

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


class Custom_Zend_Db_Table extends Zend_Db_Table {
    protected $_select;

then I had to chanced the _setup() function:


protected function _setup() {
            parent::_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:


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:


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
                    $this->_select->where($val);
                } 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) {
                $this->_select->order($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!

Nils

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


<?php
    class news extends Custom_Zend_Db_Table {

    }

<?php
    class user extends Custom_Zend_Db_Table {

    }

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


<?php
...
 // ********** news from database ********************************
                $news = new news();
                  //join
                  $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
                  //fetchAll
                  $vars['news'] = $news->fetchAll($where)->toArray();
        // ********** news from database ********************************

that's it....

Changing fix version to 0.8.0.

Recategorize as Zend_Db_Table component.

i did it this way:


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);        
}   

// the JOIN clause
if (!empty($this->_join)) {
  $select->join($this->_join[0], 
  "{$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

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

Linking as duplicate of ZF-1063.