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

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.

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 ?

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

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.