[FLOW3-general] joins in select queries with TypoScript

[Robert Wildling]

Thanks, Jigal! I am ashamed for still not being able to use the TSREF 
properly...

Say, do you happen to know out of the blue whether join-statements work 
within renderobj-CONTENT elements? Because this doesn't work at all:

temp.subcontent = COA
temp.subcontent {
   // generate title of the current page
   10 = TEXT
   10.field = title
   10.dataWrap = <h1>|</h1>

   // get content from subpages, column right
   20 = CONTENT
   20 {
     table = pages
     select {
       orderBy = sorting
     }
     renderObj = CONTENT
     renderObj {
       table = tt_content
       select {
         pidInList.field = uid
         selectFields = *
         andWhere = colPos = 1
         orderBy = sorting
       }
       select.join = tx_buzzwords_word ON 
(tt_content.tx_buzzwords_buzzword = tx_buzzwords.uid)

       renderObj = COA
       renderObj {
         5 = TEXT
         5.field = tx_buzzwords_word.buzzword
         5.wrap = <div class="topic-tag">|</div>

         10 = TEXT
         10.field = header
         10.wrap = <div class="headline">|</div>
       }
     }
   }
}


As soon as I comment out the select.join ... line, at least the 10 ind 
the last renderObj is generated (<div class="headline">...)


A workaround with a second select query like this:

temp.subcontent = COA
temp.subcontent {
   [...]
   20 = CONTENT
   20 {
     table = pages
     select {
       orderBy = sorting
     }
     renderObj = CONTENT
     renderObj {
       table = tt_content
       select {
         pidInList.field = uid
         andWhere = colPos = 1
       }

       renderObj = COA
       renderObj {
         2 = CONTENT
         2 {
           table = tx_buzzwords
           select {
             pidInList.field = uid  // ?? tt_content.uid, {field:uid}
                   //    this, it should actually come from
		  //               tx_buzzwords_buzzword
           }
           renderObj = TEXT
           renderObj.field = buzzword
           renderObj.wrap = <div class="topic-tag">|</div>
         }
         [...]
       }
       renderObj.wrap= <article>|</article>
     }
   }
}

arises the problem of how to get uid (/actually the 
tx_buzzwords_buzzwords content) of the actual query.

Do you know what to do?

Thanks!
Regards,
Robert



> Hi,
>
> On 2-5-2012 9:16, Robert Wildling wrote:
>> I am wondering if I am looking at the wrong place, but here:
>> http://typo3.org/documentation/document-library/core-documentation/doc_core_tsref/4.7.0/view/1/7/
>>
>>
>> there is nothing metioned about any join-possibilities in tables queries
>> done with TS. There are other results out there, but where is the real
>> docuemntation about it?
>
> One of the properties of the CONTENT object is 'select'. This has a
> separate section in the documentation:
> http://typo3.org/documentation/document-library/core-documentation/doc_core_tsref/4.7.0/view/1/5/#id552862
>
>
> There you'll find all the options you can use to construct a query. It
> has join, leftjoin and rightjoin possibilities, etc.
>
> TIP:
> To prevent any security problems in your queries it is advised to use
> the 'markers' option as described in that section. You can use
> ###mymarker### style markers in all 'select' sub-properties and define
> the contents (using any stdWrap properties) in the 'markers' property.
> This way the contents of the markers are automatically escaped and
> quoted whenever this is needed, to prevent SQL injections.
>