Home » Structures » Structures_DataGrid » Bug #5606

[2005-10-05 06:50 UTC] ale dot pas at tiscali dot it

Description:
------------
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]

Test script:
---------------
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)

Expected result:
----------------
Sorted column

Actual result:
--------------
[nativecode=Dynamic SQL Error
SQL error code = -204 Ambiguous field name between table

[2005-10-31 18:36 UTC] olivierg at php dot net

I have reproduced this bug but it seems to be related to an underlying DB_DataObject bug or missing feature.

Your workaround is not acceptable to me : you propose to prepend the table name when calling $dataobject->orderBy() from the DataSource_DataObject driver.

But when you join two tables, you can either sort using the fields from the first table, or the second table, or even both. With your workaround, sort links would only work for columns that belong to the first table. It may have fixed your own problem, but it is not a general solution.

I have been trying to solve this issue using DB_DataObject::selectAs() but it always ended up with the following mysql error "Column 'foobar' in order clause is ambiguous".

I forward this issue to DB_DataObject maintainers.

[2005-11-01 22:07 UTC] olivierg at php dot net

Alright, it is possible to set up DB_DataObject so that column names are not considered ambiguous by MySQL. Thanks Alan Knowles for clarifying this.

I think that your problem is no Structures_DataGrid bug but comes from a misconfigured dataobject.

The following works fine here :
$products->selectAs();
$products->joinAdd($stores);
$products->selectAs($stores, 'store_%s');
$datagrid->bind($products);

DB_DataObject will prepend "store_" to all of the column that come from the store table : no conflict anymore. Note that the first call to selectAs(), with no argument, is necessary.

Does it work for you ?

[2005-11-07 13:49 UTC] ale dot pas at tiscali dot it

Sorry, don't works for me: all fields are now prepended
with the table name, that is ok, but the sorting field is
still without the table name, so the conflict is still
here:

SELECT FIRST 10 SKIP 0 UNITAFISICHE.IDUNITA as IDUNITA ,
UNITAFISICHE.IDZONA as IDZONA , UNITAFISICHE.IDSOTTOZONA
as IDSOTTOZONA , UNITAFISICHE.UNITA as UNITA ,
UNITAFISICHE.MISURA as MISURA , UNITAFISICHE.UM as UM ,
UNITAFISICHE.TIPO as TIPO , UNITAFISICHE.FN_O_PLANIMETRIA
as FN_O_PLANIMETRIA , UNITAFISICHE.FN_M_PLANIMETRIA as
FN_M_PLANIMETRIA , UNITAFISICHE.ALTEZZA as ALTEZZA ,
UNITAFISICHE.LARGHEZZA as LARGHEZZA ,
UNITAFISICHE.PROFONDITA as PROFONDITA ,
UNITAFISICHE.DIAMETRO as DIAMETRO , UNITAFISICHE.SETTORE
as SETTORE , UNITAFISICHE.NOME_ZONA as NOME_ZONA ,
UNITAFISICHE.ZONA as ZONA , UNITAFISICHE.TIPOLOGIA as
TIPOLOGIA , UNITAFISICHE.DESCRIZIONE as DESCRIZIONE ,
UNITAFISICHE.NOTE1 as NOTE1 , UNITAFISICHE.NOTE2 as NOTE2
FROM UNITAFISICHE
INNER JOIN APPALTI_UF_OPER ON
APPALTI_UF_OPER.IDUNITA=UNITAFISICHE.IDUNITA WHERE
( APPALTI_UF_OPER.IDAPPALTO IN (1,2)) ORDER BY IDUNITA
ASC

Engine Message :
Dynamic SQL Error
SQL error code = -204
Ambiguous field name between table UNITAFISICHE and table
APPALTI_UF_OPER
IDUNITA

--- code is now:
$do->selectAs();
$dg =& new Structures_DataGrid($numfields);
$dg->setRequestPrefix($do->__table);
$dg->bind($do,
array('formbuilder_integration'=>true));

BTW the query is now longer :)

[2005-11-07 17:48 UTC] ale dot pas at tiscali dot it

After some other trials I've finally found a solution:

$do->selectAs();
// foreach fields
$dg->addColumn(new Structures_DataGrid_Column($label,
$fieldname,
$do->__table . '.' . $fieldname));

This avoids any field name conflict while sorting.