Re: [fw-db] Help With Multitable Left Joins

Bill, you were spot on. It did overwrite the results because I wasn't
explicitly listing them. Your solution definitely worked, but after going
through it with a colleague we ended up implementing a
Zend_Db_Table_Row_Abstract:

AssetController:
$assets = $this->_model->getAllAssetsByClient($clientId);
$this->view->partialLoop()->setObjectKey('asset');

View assetRow (which is a partial)
<td><?php echo $this->asset->assetId ?></td>
<td><?php echo $this->asset->assetType ?></td>
<td><?php echo $this->asset->description ?></td>
<td><?php echo $this->asset->timestamp ?></td>
<td><?php echo $this->asset->getContent() ?></td>

And implementing Zend_Db_Table_Row_Abstract for Assets

public function getContent(){
$content = NULL;
$type = $this->assetType;
switch ($type) {
case 'photo':
$photosTable = new Application_Model_Table_Photo();
$photoRow =
$photosTable->fetchRow($photosTable->getDefaultAdapter()->quoteInto('assetId
= ?', $this->assetId));
$content = $photoRow->getThumbnail();
break;
case 'video':
$videosTable = new Application_Model_Table_Video();
$videosRow =
$videosTable->fetchRow($videosTable->getDefaultAdapter()->quoteInto('assetId
= ?', $this->assetId));
$content = $videosRow->getThumbnail();
break;
case 'text':
$textsTable = new Application_Model_Table_Text();
$textsRow =
$textsTable->fetchRow($textsTable->getDefaultAdapter()->quoteInto('assetId =
?', $this->assetId));
$content = $textsRow->content;
break;
}
return $content;
}

Regards
Kyle Schatzle

On Wed, Sep 22, 2010 at 11:58 PM, Bill Karwin <bill@karwin.com> wrote:

>
> On Sep 22, 2010, at 3:31 PM, kschatzle wrote:
>
> public function getAllAssetsByClientId() {
>>
>
> Wouldn't you want to pass a parameter for $clientId or something, so you
> could add it to the WHERE clause of your query?
>
>
> "assetId" should not be NULL in case 1 or 2 above.
>>
>
> This probably happens because your result set contains multiple columns of
> the same name (assetId), but with different values. The result set comes
> back as an associative array, and an assoc array can store only one value
> per key, so PDO overwrites the element of the array with the successive
> assetId columns. Of course, a.assetId is non-null and one other of
> t.assetId, p.assetId, and v.assetId is non-null. The others are null.
>
> Your solution is to list columns explicitly instead of relying on the
> default behavior of selecting "*" from each table. Unfortunately, SQL does
> not have a wildcard that means "all columns except assetId" so you'll have
> to list them all.
>
> Once you list the columns, you can either exclude duplicate columns so they
> aren't included in the query result set, or else alias them so they are
> included, but with a unique column name.
>
>
> $query = $this->select()
> ->setIntegrityCheck(false)
> ->from(array('a' => 'assets')) // this is okay to rely on "*"
> ->joinLeft(array('t' => 'texts') , 'a.assetId = t.assetId',
> array('textId', 'content'))
> ->joinLeft(array('p' => 'photos'), 'a.assetId = p.assetId',
> array('photoId', 'photoPath'=>'path', 'photoFileName'=>'fileName'))
> ->joinLeft(array('v' => 'videos'), 'a.assetId = v.assetId',
> array('videoId', 'videoPath'=>'path', 'videoFileName'=>'fileName'));
>
> I'm guessing that both photos and videos have columns path and fileName,
> and these need to be aliased so one doesn't overwrite the other.
>
> Regards,
> Bill Karwin
>
>