[TYPO3-english] Complex Query in Typoscript

[Jigal van Hemert]

Hi M or IAmThatSTrange,

(It's custom in TYPO3 lists to use your real name)

On 22-2-2011 21:23, IAmThatSTrange wrote:
> I have a query that get's exactly what I want in a straight db query, I've
> just no clue how to put this into Typoscript.
>
> the Query:
>
> SELECT t.uid, t.pid, bodytext
> FROM tt_content t
> WHERE pid IN (SELECT p1.uid FROM pages p1 LEFT JOIN pages p2 ON p1.pid =
> p2.uid where p2.pid = 17523 and p1.hidden = 0 and p1.deleted=0)
> order by tstamp desc

I would split it in two parts.

1. Perform the inner query to retrieve the pids. Use renderObj and a 
clever wrap to make a comma separated list of pids.

2. Store the result in the register (LOAD_REGISTER)

3. Perform the outer query. In 4.4+ you can use ###markers### in 
virtually all parts of the select property. Markers have full stdWrap 
support so you can fill them with stored list of pids.
So you can simply use pidInList = ###pidlist###

--------------------

If you want it shorter you can put the results of 1. directly into the 
marker of the pidlist:
markers {
	pidlist.cObject = CONTENT
	pidlist.cObject {
		table = pages
		select {
			selectFields = pages.uid
			leftjoin = pages AS p2 ON pages.pid=p2.uid
			where = p2.pid=17523
		}
		renderObj = ....
	}
The check for deleted and hidden items is done automatically in CONTENT 
objects.

Take one step at a time: first test comma separated list in your page, 
then test the rest with a hardcoded list of pids, finally combine the two.

Documentation for markers: http://goo.gl/VduM7  (#13940: Added marker in 
CONTENT object)

-- 
Kind regards / met vriendelijke groet,

Jigal van Hemert
skype:jigal.van.hemert
msn: jigal at xs4all.nl
http://twitter.com/jigalvh