[TYPO3-dev] Use of subqueries

[Rik Willems]

Hi Bernhard,

Thank you for your clear and extensive response.
Basically I want to create a categorization (also the extkey) extension. 
I thought about a specific solution while working on a recent project.

There is a hook in enableFields that allows you to add to the where 
statement. This is in my opinion the only way to create a global 
categorization extension that doesn't require any extension to be 
modified to work.

But, the enableFields function only allows you to add to the 'where' 
part of a statement. But for a join to work you also require the 'from' 
part. A subquery would prove a good solution in that case I believe.

In case this raises any new insights please let me know.

Greets, Rik

Bernhard Kraft schreef:
> Rik Willems schrieb:
> 
>> For an extension I planning I wonder about the following. MySQL offers
>> the opportunity to use subqueries (and subsubqueries etc). Can any one
>> say anything about the use of subqueries in TYPO3 and how they influence
>> performance?
> 
> TYPO3 does not impact on the performance of your queries in any way -
> except that you use API methods for submitting the query and fetching
> the results.
> 
> 
>> An alternative would be to first select through php what you want and
>> than afterward write a query without subqueries.
> 
> It depends on what you want to achieve. In many cases it is also
> possible to avoid subqueries by clever use of "JOIN" or "LEFT JOIN".
> Have a look at the mysql-manual for the syntax of those or any good
> documentation and/or book about SQL and "JOIN" in general to get a
> glimpse how those could help you.
> 
> If you want to retrieve all rows, and associated rows, referenced by a
> database relation, in many cases JOINs are possible.
> 
> Those are even faster in most of the cases.
> 
> Of course you can also use subqueries, but be aware, that a query using
> subqueries could take much longer than other solution.
> 
> Making a query in PHP/TYPO3 and then another one for each result row is
> most probably the slowest solutions, but also possible under special
> circumstances (detail/single view, etc.)
> 
> 
> So it depends on your application. You can of course place a subquery in
> the WHERE part of TYPO3_DB->exec_SELECTquery and other TYPO3_DB API calls.
> 
> 
> greets,
> Bernhard