Structures_DataGrid problems sorting on joined tables
| From: | Alessandro Pasotti | Date: | Tue, 04 Oct 2005 12:30:53 +0000 |
| Subject: | Structures_DataGrid problems sorting on joined tables | ||
| Groups: | php.pear.general | ||
| Request: | Send a blank email to pear-general+get-21223@lists.php.net to get a copy of this message | ||
I have experienced some problems sorting on joined tables, where a conflict on
field names (same field name on more than one joined table) can provoke an
error in the resulting SQL query (the DB engine cannot determine on which
field to order).
[message] => DB Error: unknown error
[userinfo] => SELECT FIRST 10 SKIP 0 * FROM UNITAFISICHE
INNER JOIN APPALTI_UF_OPER ON APPALTI_UF_OPER.IDUNITA=UNITAFISICHE.IDUNITA
WHERE ( IDAPPALTO IN (2)) ORDER BY IDUNITA ASC [nativecode=Dynamic SQL Error
SQL error code = -204 Ambiguous field name between table UNITAFISICHE and
table APPALTI_UF_OPER IDUNITA]
A solution is to add the table name before the sortBy field name:
... ORDER BY ZONE.IDUNITA
After several trials in my subclasses, I ended up modifying the source in
Structures/DataGrid/DataSource/DataObject.php (line 281)
/**
* Sorts the dataobject. This MUST be called before fetch.
*
* @access public
* @param string $sortField Field to sort by
* @param string $sortDir Sort direction : 'ASC' or 'DESC'
*/
function sort($sortField, $sortDir = null)
{
if ($sortDir === null) {
$this->_dataobject->orderBy($this->_dataobject->__table . '.' .
$sortField);
} else {
$this->_dataobject->orderBy($this->_dataobject->__table . '.' .
$sortField . ' ' . $sortDir);
}
}
This patch is probably harmless in other situations and could maybe integrated
in the class.
I don't know if there was another solution, modifying the $_REQUEST suppresses
the error but the sorting arrows disappears too.
Tested with Firebird (not my choice :( )
--
Alessandro Pasotti
ICQ# 245871392
Linux User #167502