[TYPO3-english] Tesseract: Multiple complex filters

[Hauke Hain]

Hi there,

I like the Tesseract concept ( http://www.typo3-tesseract.com/en/ ). 
Unfortunately it does not allow own SQL statements - they will be parsed 
with a quite simple parser.

My goal: I want to show a list of products the current fe_user has not 
already commented. (I refer to this list as "the list".)
Each product has got subpages that displays and holds different data:
Product1
 - general information
 - comment
 - special comments
Product2
 - general information
 - comment
 - special comments
...

A user can write either a comment on the "comment" subpage or on the 
"special comments" subpage. The list should show the product name. Any 
subpage of a product has got the product name as the page title. The 
nav_title shows the name as in the example above (e.g. "general 
information").

The data consumer needs the title for an output and the pid to create a 
TypoLink. The data provider selects these columns. As not the whole TYPO3 
page consists of product-pages with subpages, I filter the childpages of the 
parent page of a bunch of product pages (with their subpages). The PId list 
is hardcoded as it will never change. The list should only output one kind 
of link to a product: either to the "comment" or "special comment" page, 
depending on the post variable navtitle. Now I have all products pages that 
could be in the list. The next thing is to remove the pages the user already 
commented. To do so I select the page id of the comment tables (tx_comments 
or tx_comments_special) with a filter for the current fe_user. I combine the 
pid lists of both tables and use the list with "uid IN" on a new select on 
the pages table to get all pids I want to exclude. I filter by "NOT IN 
pr.pid" (or pl.uid), so I have the subpages removed from the list whereas it 
does not matter on which page the user has written an comment. If he get 
links to the "special comments" page (post parameter) but has written 
comments only to the "comment" page, the "special comments" page for the 
product is not shown in the list.

Here is the complete SQL-Statement with the correct filter syntax for the 
value that would be insertet in filters.
SELECT pr.uid, pr.title
  FROM pages pl, pages pr
  WHERE pl.PID
  IN ( 2948, 24, 2730, 797, 2453 )
  AND pl.uid = pr.pid
  AND pr.nav_title LIKE gp:navtitle
  AND pr.pid NOT
  IN (
  SELECT pid
  FROM pages
  WHERE uid
  IN (
  SELECT pid
  FROM tx_comments
  WHERE cruser_id = TSFE:fe_user|user|uid
  UNION
  SELECT pid
  FROM tx_comments_special
  WHERE cruser_id = TSFE:fe_user|user|uid
  )

The problem is, that I am able to create only one select statement with any 
number of simple filters, but none with subselects. I tried the advanced 
output plugin and secondary provider, but with both it was not possible to 
add a second data query with filters that could be conjuncted with a 
previous one.

Does anybody have a clue / idea how I can solve my problem with Tesseract or 
any other extension?
I have many lists on my todo list that needs statements like in this 
scenario and I am looking for an extension (-set) that supports me with that 
time consuming work. I need lists with sorting and many filters ...

Thank you very much for reading this long mail.

Kind regards
Hauke