Thread: Zend_Db_Table relationships.

Originally Posted by ArticSun

I'm aware that I might be better of creating the relations in the database itself, but I really want to try this at the moment. And if I would arrange this in the database, I would have the same question about querying the data .

I am looking for a proper solution too ... there are many tutorials out there, but most either focus on a single table or they just describe how to select things in the code, but what I really need is a nice example how to put the query into a model class of its own.

Any ideas?

Hello to all.

Firstly they excuse my English, translated with Google.

In an attempt of car learning of Zend, I am making a mini local application in which to store client, product and sales.

I have a table Sales and another DetailSales. In second it is where I store all to those products and units that were acquired in each sale. Thus, each row of this one, is related to an only row of sales.

We could say that a sale can have several rows of detail but each row of detail belongs to a single sale.

However, after constructing the classes so and as it is indicated in the manual, when attempt to make that assumption filtered by means of the relations… does not work!

It lists all the table details sales to me.


[PHP]class Sales extends Zend_Db_Table {

protected $_name = 'sale';
protected $_primary = 'sale_id';

protected $_dependentTables = array('detailsales');
}[/PHP]


[PHP]class DetailSales extends Zend_Db_Table {

protected $_name = 'detailsale';
protected $_primary = 'detail_id';
protected $_referenceMap = array(
'details' => array(
'columns' => 'dsale_id',
'refTableClass' => 'Sales',
'refColumns' => 'sale_id'
)
);
} [/PHP]

[PHP]
$salesTable = new Sales();
$salesRowset = $salesTable->find(1);
$detail1 = $salesRowset->current();
$detailSales = $detail1->findDependentRowset('Sales');
$this->view->detail = $detailSales;[/PHP]



When doing foreach to $this->detail in the view, lists all the lines to me of detail, including which they do not belong on sale.


Some suggestion?

Gracias

I have no idea how to solve JOIN problem, but i want to say that have same problem too - i`ve readed reference manual and API guide, and i found nothing. Maybe it would be pesimistic but i think that there is no support for JOINs in Zend_DB_Table (unless you make extension to Zend_DB_Table class).
Maybe someone should ask developement team, find out and post information here?

I've got the same feeling. I have an intersection table that I use to tag images called image_tags.

I tried to set up the dependencies in my table classes like this:
[php]
class Image extends Zend_Db_Table
{
protected $_name = 'image';

protected $_dependentTables = array('ImageTag');
}
[/php]

[php]
class ImageTag extends Zend_Db_Table
{
protected $_name = 'image_tag';
protected $_sequence = false;
protected $_referenceMap = array(
'Image' => array( 'columns' => array('imageId'),
'refTableClass' => 'Image'

),
'Tag' => array( 'columns' => array('tagName'),
'refTableClass' => 'Tag'

)
);
}
[/php]
[php]
class Tag extends Zend_Db_Table
{
protected $_name = 'tag';
protected $_sequence = false;
protected $_primary = 'name';
protected $_dependentTables = array('ImageTag');
}
[/php]
I think that I followed the manual on these model classes...
Anyway I was trying to use the findManyToManyRowset in order to return all the images with a particular tag (couldn't begin to figure out how to use this to select all the images from a particular SET of tags...)
here is a code snippet of the method that was handleing this task
[php]
public function fetchByTag($tag, $order = null, $count = null, $offset = 0)
{
//get the tag, fetch the result set.
$set = $this->_tag->find($tag);
$tag = $set->current();

//get Images via ImageTag, generate html.
$set = $tag->findManyToManyRowset($this->_image,$this->_imageTag);
foreach ($set as $image){
$this->_html.= $this->generateHtml($img);
}
return $this->_html;
}
[/php]
this yields a really fun error, which I tracked down to Zend_Db_Table_Row_Absract at the following location: (zf v1.0)
[php]
$interInfo = $intersectionTable->info();
$interName = $interInfo['name'];
$matchInfo = $matchTable->info();
$matchName = $matchInfo['name'];
$matchMap = $this->_prepareReference($intersectionTable, $matchTable, $matchRefRule);

for ($i = 0; $i < count($matchMap[Zend_Db_Table_Abstract::COLUMNS]); ++$i) {
$interCol = $db->quoteIdentifier('i', true) . '.' . $db->quoteIdentifier($matchMap[Zend_Db_Table_Abstract::COLUMNS][$i], true);
$matchCol = $db->quoteIdentifier('m', true) . '.' . $db->quoteIdentifier($matchMap[Zend_Db_Table_Abstract::REF_COLUMNS][$i], true);
$joinCond[] = "$interCol = $matchCol";
}
$joinCond = implode(' AND ', $joinCond);

$select = $db->select()
->from(array('i' => $interName), array())
->join(array('m' => $matchName), $joinCond, '*');
[/php]
with a little debugging it is clear that the trouble starts with $matchCol in that for loop, and then the fatal error occurs in the select at the end of this snippet.

Anyway I solved the problem thus:
[php]
public function fetchByTag($tagList, $order = null, $count = null, $offset = 0)
{
//get many to many ImageTag, build WHERE clause from the CSV tagList parameter, fetch the result set.
//this would be easier if I could get the damn framework getManytoMany to work
$where = "tagName in ('".str_replace(',',"','",$tagList)."')";
if(!$order === 'rand()') $order = "'$order'";
$sql = "SELECT * from image, image_tag
WHERE image.id = image_tag.imageId AND $where
ORDER BY $order
LIMIT $count
OFFSET $offset";
$registry = Zend_Registry::getInstance();
$db = $registry->get('db');
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$set = $db->fetchAll($sql);
//for each ImageTag get parent image, generate html.
$this->_html = "\n<table><tr><td>";
foreach($set as $img){
$this->_html.= $this->generateHtml($img);
}
$this->_html .= "</td></tr></table>";
return $this->_html;
}
[/php]

this works very well but has some questionable logic (for rand()) and of course it's a much longer method than the first one, and I'm lazy....

Anybody have any ideas?

Here is an example how to make a query using 2 tables (relation is one to one)
I used the Zend_Db_Table_Abstract class. Mitarbeiter inherits by Person

[PHP]class MitarbeiterTabelle extends Zend_Db_Table_Abstract{
protected $_name = 'mitarbeiter';
protected $_dependentTables = array('PersonenTabelle');

protected $_referenceMap = array(
'PersonenTabelle' => array(
'columns' => array('personen_id'),
'refTableClass' => 'PersonenTabelle',
'refColumns' => array('personen_id')
)
);
} //class[/PHP]
[PHP]
class PersonenTabelle extends Zend_Db_Table_Abstract{
protected $_name = 'person';
protected $_dependentTables = array('MitarbeiterTabelle');

} //class[/PHP]

[PHP]
class Person {
protected $personen_id;
protected $vorname;
protected $name;
protected $strasse_hausnr;
protected $hausnummer;
protected $plz;
protected $ort;
protected $email;
protected $handy;
protected $telefonnr;
protected $fax;
protected $geburtsdatum;


public function Person(
$personen = NULL,
$vorname = NULL,
$name = NULL,
$strasse_hausnr = NULL,
$plz = NULL,
$ort = NULL,
$email = NULL,
$handy = NULL,
$telefonnr = NULL,
$fax = NULL,
$geburtsdatum = NULL
){
// Auslesen
if($personen_id){
$personenTabelle = new PersonenTabelle();
$personen = $personenTabelle->fetchRow('personen_id='.$personen_id);
$this->personen_id = $personen->personen_id;
$this->vorname = $personen->vorname;
$this->name = $personen->name;
$this->strasse_hausnr = $personen->strasse_hausnr;
$this->plz = $personen->plz;
$this->ort = $personen->ort;
$this->email = $personen->email;
$this->handy = $personen->handy;
$this->telefonnr = $personen->telefonnr;
$this->fax = $personen->fax;
$this->geburtsdatum = $personen->geburtsdatum;
}
}

public function __set($varname, $value) {
$this->$varname = $value;
}

public function __get($varname) {
return $this->$varname;
}

public static function getAll() {
$personenTabelle = new PersonenTabelle();
$allePersonen = $personenTabelle->fetchAll();
foreach ($allePersonen as $person) {
$personen[] = new Person ($person->personen_id);
}
return $personen;
}//getALL
}class Person[/PHP]

[PHP]
class Mitarbeiter extends Person{

private $mitarbeiter_id;
private $mitarbeitertyp;

public function Mitarbeiter($mitarbeiter_id =NULL,$mitarbeitertyp =NULL){
if($mitarbeiter_id){

$mitarbeiterTabelle = new MitarbeiterTabelle();
$mitarbeiter = $mitarbeiterTabelle->fetchRow('mitarbeiter_id='.$mitarbeiter_id);
//$mitarbeiter1 = $mitarbeiter->current();
$person1 = $mitarbeiter->findParentRow('PersonenTabelle');

$this->mitarbeiter_id = $mitarbeiter->mitarbeiter_id;
$this->mitarbeitertyp = $mitarbeiter->mitarbeitertyp;
$this->personen_id = $person1->personen_id;
$this->vorname = $person1->vorname;
$this->name = $person1->name;
$this->strasse_hausnr = $person1->strasse_hausnr;
$this->plz = $person1->plz;
$this->ort = $person1->ort;
$this->email = $person1->email;
$this->handy = $person1->handy;
$this->telefonnr = $person1->telefonnr;
$this->fax = $person1->fax;
$this->geburtsdatum = $person1->geburtsdatum;

}



}//MitarbeiterKonstruktor

public function __set($varname, $value) {
$this->$varname = $value;
}//set

public function __get($varname) {
return $this->$varname;
}//get



public static function getAll(){


$mitarbeiterTabelle = new MitarbeiterTabelle();
$allemitarbeiter = $mitarbeiterTabelle->fetchAll();
foreach ($allemitarbeiter as $mitarbeitereinzeln) {
$mitarbeiter[] = new Mitarbeiter ($mitarbeitereinzeln->mitarbeiter_id);
//mit dieser ID, in Aufgabe entsprechende Zeile holen versuchen;-)
}

return $mitarbeiter;

}//getAll
}class Mitarbeiter[/PHP]

now how to make a query;-)
make a contoller class with the following function:

[PHP]public function mitarbeiteranzeigenAction(){
$mitarbeiter=Mitarbeiter::getAll();
$this->view->mitarbeiter=$mitarbeiter;
}//anzeigen[/PHP]

All $mitarbeiter are sent to the view

Thank you for sharing!

I do not know if this solution is correct,but it works fine, so here it is.

#application/models/MenuContent.php
[PHP]<?php
class MenuContent extends Zend_Db_Table_Abstract
{
protected $_name = "content_menu";
protected $_primary = "IDmenu";
protected $_dependentTables = array("MenuItems");
protected $_referenceMap = array(
"onUpdate" => "cascade",
"onDelete" => "cascade"
);
/*
* insert data into tables content_menu,menu_items
* @return SUCCESS or ERROR!
*/
public function save($data = NULL)
{
if ($data !== NULL)
{
try
{
$this->getAdapter()->beginTransaction();

$content_menu_data['name'] = $data['name'];
$content_menu_data['is_active'] = $data['is_active'];
$content_menu_data['date_created'] = $data['date_created'];
$content_menu_data['date_modified'] = $data['date_modified'];
$content_menu_data['count_shown'] = $data['count_shown'];
$content_menu_data['site'] = $data['site'];

$this->insert($content_menu_data);

$data['IDmenu'] = $this->getAdapter()->lastInsertId('content_menu','IDmenu');

$items = new MenuItems();
$items->insert($data);

$this->getAdapter()->commit();

return "SUCCESS";
}
catch (Exception $e)
{
$this->getAdapter()->rollBack();
throw $e;
return "ERROR!";
}

}
else
{
return "ERROR!";
}
return "ERROR!";
}
}[/PHP]

#/application/controllers/MenuController.php
[PHP].
.
.
.
.
.
$insertion = new MenuContent();
$return = $insertion->save($data);

if ($return == "SUCCESS")
{
$this->_redirect('menu');
}
else
{
$this->render('error');
}
.
.
.
.
.
[/PHP]

and another solution


#application/models/Contents.php

[PHP]
<?php
class Contents extends Zend_Db_Table_Abstract
{
protected $_name = "content";
protected $_dependentTables = array(
"pg_class",
"content_html",
"content_flash",
"content_php",
"content_menu",
"dictionary",
"dictionary_data"
);
protected $_referenceMap = array(
"onUpdate" => "cascade",
"onDelete" => "cascade"
);

/*
* @return Zend_Db_Table_Rowset
*/
public function getAll()
{
$rowset = $this->fetchAll(
$this->select('pg_class.relname, content.*')
->order('name')
->setIntegrityCheck(false)
->from('content')
->joinLeft('pg_class','content.tableoid = pg_class.oid' )
);

return $rowset;
}
}[/PHP]

#application/controllers/ContentController.php
[PHP].
.
.
.
.
$contents = new Contents();

$this->view->contents = $contents->getAll();
.
.
.
.
.
[/PHP]
if anybody can correct my mistakes (not in my English but in code ) please post here.

Thanks for attention