Issues

ZF-5620: Column 'xxx' in field list is ambiguous

Description

PATCH:

$ diff -u Zend/Paginator/Adapter/DbSelect.php Zend/Paginator/Adapter/DbSelect.php.new --- Zend/Paginator/Adapter/DbSelect.php 2008-11-04 23:49:22.000000000 +0200 +++ Zend/Paginator/Adapter/DbSelect.php.new 2009-01-26 22:44:30.000000000 +0200 @@ -163,7 +163,7 @@ if ($part[1] == '*' || $part[1] instanceof Zend_Db_Expr) { $columns[] = $part[1]; } else {

  •                  $columns[] = $rowCount->getAdapter()->quoteIdentifier($part[1], true);
    
  •                  $columns[] = $rowCount->getAdapter()->quoteIdentifier($part[0] . '.' . $part[1], true);
    
                 }
             }
    

Without Zend_Paginator everything works well (I use Zend_Db_Select).

SQL query :

SELECT DISTINCT j.JobId, j.Type, j.Name AS JobName, j.Level, j.ClientId, DATE_FORMAT(j.StartTime, '%y-%b-%d %H:%i') AS StartTime, DATE_FORMAT(j.EndTime, '%y-%b-%d %H:%i') AS EndTime, j.VolSessionId, j.VolSessionTime, j.JobFiles, j.JobBytes, j.JobErrors, j.PoolId, j.FileSetId, j.PurgedFiles, j.JobStatus, TIMEDIFF(EndTime, StartTime) AS DurationTime, s.JobStatusLong, c.Name AS ClientName, p.Name AS PoolName, f.FileSet FROM Job AS j LEFT JOIN Status AS s ON j.JobStatus = s.JobStatus LEFT JOIN Client AS c ON j.ClientId = c.ClientId LEFT JOIN Pool AS p ON j.PoolId = p.PoolId LEFT JOIN FileSet AS f ON j.FileSetId = f.FileSetId WHERE (('2008-01-22 09:48:06' <= j.StartTime) AND (j.StartTime <= '2009-01-23 09:48:06')) ORDER BY JobId ASC

I try to use Zend_Paginator and get an error:

Zend_Db_Statement_Exception: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'Name' in field list is ambiguous in /opt/prog/webacula/trunk/library/Zend/Db/Statement/Pdo.php on line 238

Code : $select = new Zend_Db_Select($db);

// make SQL query code ...

$paginator = Zend_Paginator::factory($select); Zend_Paginator::setDefaultScrollingStyle('Sliding'); $paginator->setItemCountPerPage(self::ROW_LIMIT_JOBS); $paginator->setCurrentPageNumber($this->_getParam('page', 1)); $this->view->paginator = $paginator; $paginator->setView($this->view);

--- Zend/Db/Statement/Pdo.php line 228 public function _execute(array $params = null) { print_r($this->_stmt);

result below:

PDOStatement Object ( [queryString] => SELECT COUNT(DISTINCT JobId,Type,Name,Level,ClientId,DATE_FORMAT(j.StartTime, '%y-%b-%d %H:%i'),DATE_FORMAT(j.EndTime, '%y-%b-%d %H:%i'),VolSessionId,VolSessionTime,JobFiles,JobBytes,JobErrors,PoolId,FileSetId,PurgedFiles,JobStatus,TIMEDIFF(EndTime, StartTime),JobStatusLong,Name,Name,FileSet) AS zend_paginator_row_count FROM Job AS j LEFT JOIN Status AS s ON j.JobStatus = s.JobStatus LEFT JOIN Client AS c ON j.ClientId = c.ClientId LEFT JOIN Pool AS p ON j.PoolId = p.PoolId LEFT JOIN FileSet AS f ON j.FileSetId = f.FileSetId WHERE (('2008-01-22 19:40:51' <= j.StartTime) AND (j.StartTime <= '2009-01-23 19:40:51')) )

ps. Zforum topic: http://zfforums.com/zend-framework-components-13/…

Comments

Possible, Paginator does not understand the LEFT JOIN ?

This should be resolved in revision 14136, along with ZF-5231