[TYPO3-dev] pi_exec_query or exec_SELECTquery?

[Peter Niederlag]

Hi,

Gerrit Kamp schrieb:
[...]
> If you use pi_exec_query, you have all the benefits of filtering out hidden
> records and searchFieldList etc, but since the SELECT statement is defined
> purely on the table and the fieldList, it is impossible to add the
> categoryName from another table. So you are stuck with the categoryID, which
> you cannot sort alphabetically.

There is an extra paramter to pi_exec_query which is described like this:

<snipp>
@param   mixed       If an array, then it must contain the keys "table",
"mmtable" and (optionally) "catUidList" defining a table to make a
MM-relation to in the query (based on fields uid_local and uid_foreign).
If not array, the query will be a plain query looking up data in only
one table.
</snapp>

I can't remember if the query is build in a way so it can be used for
sorting as well. The main drawback is that you can only join in *one*
mm-table at a time.

> Alternatively, using exec_SELECTquery, you can build any SQL statement you
> want, but you loose the benefits of filtering out hidden or deleted records
> and the searchFieldList.

Well, it is not hard to add these features in your own queries by
adopting/using the appropriate API:

<snipp>
1)
$addWhere .= $this->cObj->enableFields($table).chr(10);  // This adds
WHERE-clauses that ensures deleted, hidden, starttime/endtime/access
records are NOT selected,

2.)
$WHERE.=$this->cObj->searchWhere($this->piVars['sword'],$this->internal['searchFieldList'],$table).chr(10);
</snapp>

> Is my understanding correct? Any suggestions for how to proceed?
> 
>  
> 
> 2. SQL Alias AS
> 
> I cannot get the SQL alias 'AS' functioning correctly in the
> exec_SELECTquery. I read in a 2005 posting that aliases are not yet
> incorporated in this function. Is that still the case?

nope, I have been using aliases already *long* time ago. IIRC it works
out of the box if you use it like

$fields='field1 as foo1, whatever as foolme'

or like

$from='table as foobar'
(using $GLOBALS['TYPO3_DB']->exec_SELECTquery)

Hth,
Peter
-- 
Peter Niederlag
http://www.niekom.de * TYPO3 & EDV Dienstleistungen *
http://www.typo3partner.net * professional services network *