[TYPO3] select with join in a FE-Plugin

[Bernd Wilke]

In a FE-plugin I like to make a select with join and doesn't know how to do
it wuth typo3-functions.

to become more specific:

I have a listview with detailview (generated by kickstarter).
in this list I want to get some (sub)selections.
While doing these selections in the shown elements it is easy:

Kickstarter generated:
	$res = $this->pi_exec_query($table, 0/1, $langsel);
I expanded this to:
	$res = $this->pi_exec_query($table, 0/1, $langsel.$subselection);
with $subselection = ' AND feld="wert"'.

In my case these elements are part of a hierarchy of three tables like:

	country = uid, name, ....

	city = uid, name, ... , countryid, ...

	street = uid, name, ... , cityid, ....

with street as my list.

I want to get queries like "all streets from city 'XYZ'" or "all streets
from country 'ABC'"

while overlaying the method 'pi_make_list()' I can do these selections when
the elements are inserted into the list. fulfilling my query it is inserted
into tRows[], else no insert is done.

But there is a catch: :-/

there are a lot of elements and the visitor has to browse through differnt
pages ( pi_list_browseresults() ).

This method doesn't know about the reduced number of results (so I
decrement $this->internal['res_count'] for each element thrown away)

The original structure of <$results_at_a_time> elements per page stays and
each page shows only the remaining elements out of the <$results_at_a_time>
elements.
That is because in pi_list_query() a limit of <$results_at_a_time> elements
is set to the query. 
Overlaying also this method seems very difficult.


in SQl I would like to do this:
SELECT street, city, country 
	WHERE country.uid=city.countryid 
	  AND city.uid=street.cityid 
	  AND country.name="ABC"

so that the query for the first request gives a correct number of results (
pi_exec_query(.., 1,..) )
and then ( pi_exec_query(.., 0,..) ) every time just <$results_at_a_time>
elements per page.

Bernd Wilke
-- 
----------------
Bernd Wilke     
Annweilerstr.20 
40229 Düsseldorf
0211/229 2800