2010年9月22日星期三

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

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

没有评论: