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
Attachments:
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:
<pre class="highlight">
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
Posted by Nils W (nils) on 2006-09-14T09:59:38.000+0000
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
<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() {
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:
<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
$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
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">
<?php
class news extends Custom_Zend_Db_Table {
}
<pre class="highlight">
<?php
class user extends Custom_Zend_Db_Table {
}
And this how I use the join function in the controller:
<pre class="highlight">
<?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....
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)) {
$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
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.