Use a select statement to recursively retrieve all entries of a table which's parent element do not have a certain column set to one

TYPO3 Questions TypoScript
1

Hi everyone,

I’m trying to achieve something like

SELECT subject, uid FROM tt_board WHERE NOT EXISTS (( WITH RECURSIVE cte AS ( SELECT uid, parent FROM tt_board x WHERE uid =x.uid0 UNION ALL SELECT t.uid, t.parent FROM tt_board t INNER JOIN cte ON t.parent = cte.uid ) SELECT 1 FROM cte WHERE xc_angularcli_suite_locked = 1 ));

using the typoscript select-statement but can’t seem to realize that since the “where” field and the “select” field are seperate in typoscript.

I’ve come so far:

markers {
			BOARDPAGE.data 				= field : xc_recently_commented_pages_boardPage
			DISPLAY_LOCKED.data 		= field : xc_recently_commented_pages_displayLockedMessages
			DISPLAY_LOCKED_SUBS.data	= field : xc_recently_commented_pages_displayLockedSubMessages
		}

		where 			= pid = ###BOARDPAGE### AND ( ###DISPLAY_LOCKED### = 1 || ###DISPLAY_LOCKED### = 0 AND xc_angularcli_suite_locked = 0 ) AND ( ###DISPLAY_LOCKED_SUBS### = 0 || < SQL Statement above here > )
		pidInList		= TEXT
		pidInList.data	= field : xc_recently_commented_pages_boardPage
		recursive     	= 10
		selectFields  	= subject, message, crdate, uid, author

Does someone know how to use the SQL-Statement as a further condition in “where”?

2

Hi, could you show your entire TypoScript including the content objects you’ve used?
Best regards
Max

1 Like
3

Of course, here it is:

@import 'EXT:angularcli_suite/Configuration/TypoScript/TTBoardMessage/setup.typoscript'

page = PAGE

page.includeCSS {
    WaXCode_AngularCLISuite_RecentlyCommentedPages_Listing_ngStyles = EXT:angularcli_suite/Resources/Public/JS/Angular/RecentlyCommentedPages/Listing/styles.css
}

page.includeJS {
    WaXCode_AngularCLISuite_RecentlyCommentedPages_Listing_ngRuntime        = EXT:angularcli_suite/Resources/Public/JS/Angular/RecentlyCommentedPages/Listing/runtime.js
    WaXCode_AngularCLISuite_RecentlyCommentedPages_Listing_ngRuntime.type   = module
    WaXCode_AngularCLISuite_RecentlyCommentedPages_Listing_ngPolyfills      = EXT:angularcli_suite/Resources/Public/JS/Angular/RecentlyCommentedPages/Listing/polyfills.js
    WaXCode_AngularCLISuite_RecentlyCommentedPages_Listing_ngPolyfills.type	= module
    WaXCode_AngularCLISuite_RecentlyCommentedPages_Listing_ngMain           = EXT:angularcli_suite/Resources/Public/JS/Angular/RecentlyCommentedPages/Listing/main.js
    WaXCode_AngularCLISuite_RecentlyCommentedPages_Listing_ngMain.type		= module
}

lib.recentlyCommentedPages = CONTENT
lib.recentlyCommentedPages {
	table = pages
	wrap  = |

	select {
		orderBy		= content.xc_recently_commented_pages_comment_time DESC
		max     	= TEXT
		max.data	= field : xc_recently_commented_pages_resultCount
    	join		= tt_content content ON content.pid = pages.uid AND content.xc_recently_commented_pages_comment != '' AND content.xc_recently_commented_pages_comment_time <= CURRENT_TIMESTAMP() AND content.deleted = 0 AND content.hidden = 0 AND ( content.starttime = 0 OR content.starttime <= UNIX_TIMESTAMP()) AND ( content.endtime = 0 OR content.endtime >= UNIX_TIMESTAMP()) AND ###VALID### LIKE CONCAT( '%', content.fe_group,'%') AND slug LIKE CONCAT( '%',###ORIGIN###,'%')
		
		markers {
			VALID.data 	= TSFE:fe_user|user|usergroup	
			ORIGIN.data = field : xc_recently_commented_pages_urlOrigin
		}
		
		pidInList		= TEXT
		pidInList.data	= field : xc_recently_commented_pages_newsRoot

		recursive     	= 10
		selectFields  	= slug, content.uid AS content_uid, content.xtension_script, content.xtension_css_selector, content.bodytext, content.xc_recently_commented_pages_comment, content.xc_recently_commented_pages_comment_time, content.xtension_feed_configuration, content.xc_searchbar_inlineview_disallowed, content.xc_searchbar_inlineview_max_disallowed
  }
  
  renderObj = COA
	renderObj {
		10 = TEXT
		10 {
			field     = xc_recently_commented_pages_comment_time
      		strtotime = 1
      		strftime  = %d.%m.%Y
			wrap      = {<<<°>>>timeOfComment<<<°>>>:<<<°>>>|<<<°>>>,
		}
    
		11 = TEXT
		11 {
			field = slug
			wrap  = <<<°>>>slug<<<°>>>:<<<°>>>|<<<°>>>,
		}

		12 = TEXT
		12 {
			field = content_uid
			wrap  = <<<°>>>uid<<<°>>>:<<<°>>>|<<<°>>>,
		}

		13 = TEXT
		13 {
			field = bodytext
			wrap  = <<<°>>>bodytext<<<°>>>:<<<[>>>|<<<]>>>,
		}

		14 = TEXT
		14 {
			field = xc_recently_commented_pages_comment
			wrap  = <<<°>>>comment<<<°>>>:<<<°>>>|<<<°>>>,
		}

		15 = TEXT
		15 {
			field = xc_searchbar_inlineview_disallowed
			wrap  = <<<°>>>disallowed<<<°>>>:<<<°>>>|<<<°>>>,
		}

		16 = TEXT
		16 {
			field = xtension_feed_configuration
			wrap  = <<<°>>>feed<<<°>>>:<<<°>>>|<<<°>>>,
		}

		17 = TEXT
		17 {
			field = xtension_script
			wrap  = <<<°>>>xscript<<<°>>>:<<<°>>>|<<<°>>>,
		}

		18 = TEXT
		18 {
			stdWrap.replacement {
        10 {
            search  = [%UID%]
            replace = [%CUID%]
        }
    	}

			field = xtension_css_selector
			wrap  = <<<°>>>css_selector<<<°>>>:<<<[>>>|<<<]>>>,
		}

		19 = TEXT
		19 {
			field = xc_searchbar_inlineview_max_disallowed
			wrap  = <<<°>>>disallowedMax<<<°>>>:<<<°>>>|<<<°>>>}<<<^>>>
		}
	}
}

lib.recentlyCommentedPages_Images = CONTENT
lib.recentlyCommentedPages_Images {
	table = pages
	wrap  = |

	select {
		max           = 1000
    	join          = tt_content content ON content.pid = pages.uid AND content.xc_recently_commented_pages_comment != '' AND content.deleted = 0 AND content.hidden = 0 JOIN sys_file_reference sfr ON sfr.uid_foreign = content.uid AND sfr.fieldname = "image" JOIN sys_file ON sys_file.uid = sfr.uid_local JOIN sys_file_storage ON sys_file.storage = sys_file_storage.uid
		pidInList     = 1
		recursive     = 10
		selectFields  = content.uid AS content_uid, sfr.uid AS fileID, identifier, configuration
  }
  
  renderObj = COA
	renderObj {
		10 = TEXT
		10 {
			field = content_uid
			wrap  = {<<<°>>>uid<<<°>>>:<<<°>>>|<<<°>>>,
		}

		11 = TEXT
		11 {
			field = identifier
			wrap  = <<<°>>>identifier<<<°>>>:<<<°>>>|<<<°>>>,
		}

		12 = TEXT
		12 {
			field = configuration
			wrap  = <<<°>>>configuration<<<°>>>:<<<[>>>|<<<]>>>,
		}

		13 = TEXT
		13 {
			field = fileID
			wrap  = <<<°>>>file<<<°>>>:<<<°>>>|<<<°>>>}<<<^>>>
		}      
	}
}

lib.WaXCode_AngularCLISuite_AppMenu_Viewer_typo3Host = TEXT
lib.WaXCode_AngularCLISuite_AppMenu_Viewer_typo3Host.value = getIndpEnv : TYPO3_HOST

lib.recentlyCommentedPages_In_Container = CONTENT
lib.recentlyCommentedPages_In_Container {
	table = pages
	wrap  = |

	select {
		orderBy       = c2.sorting ASC
		max           = 1000
    	join          = tt_content content ON ( content.pid = pages.uid AND content.xc_recently_commented_pages_comment != '' AND content.xc_recently_commented_pages_comment_time <= CURRENT_TIMESTAMP() AND content.bodytext IS NULL ) JOIN tt_content c2 ON ( content.uid = c2.tx_container_parent AND c2.deleted = 0 AND c2.hidden = 0 AND ( c2.starttime = 0 OR c2.starttime <= UNIX_TIMESTAMP()) AND ( c2.endtime = 0 OR c2.endtime >= UNIX_TIMESTAMP()))
		pidInList     = 1
		recursive     = 10
		selectFields  = slug, content.uid AS content_uid, c2.uid AS real_uid, c2.bodytext AS real_bodytext, c2.xtension_script AS real_xscript, c2.xtension_css_selector AS real_css_selector, c2.xtension_feed_configuration AS content_feed, content.xc_recently_commented_pages_comment, content.xc_recently_commented_pages_comment_time
  }
  
  renderObj = COA
	renderObj {
		10 = TEXT
		10 {
			field     = xc_recently_commented_pages_comment_time
      		strtotime = 1
      		strftime  = %d-%m-%Y
			wrap      = {<<<°>>>timeOfComment<<<°>>>:<<<°>>>|<<<°>>>,
		}
    
		11 = TEXT
		11 {
			field = slug
			wrap  = <<<°>>>slug<<<°>>>:<<<°>>>|<<<°>>>,
		}

		12 = TEXT
		12 {
			field = content_uid
			wrap  = <<<°>>>uid<<<°>>>:<<<°>>>|<<<°>>>,
		}

		13 = TEXT
		13 {
			field = real_bodytext
			wrap  = <<<°>>>bodytext<<<°>>>:<<<[>>>|<<<]>>>,
		}

		14 = TEXT
		14 {
			field = content_feed
			wrap  = <<<°>>>feed<<<°>>>:<<<°>>>|<<<°>>>,
		}

		15 = TEXT
		15 {
			field = real_xscript
			wrap  = <<<°>>>xscript<<<°>>>:<<<°>>>|<<<°>>>,
		}

		16 = TEXT
		16 {
			stdWrap.replacement {
        10 {
            search  = [%UID%]
            replace = [%CUID%]
        }
    	}
			
			field = real_css_selector
			wrap  = <<<°>>>css_selector<<<°>>>:<<<[>>>|<<<]>>>,
		}

		17 = TEXT
		17 {
			field = real_uid
			wrap  = <<<°>>>ownUID<<<°>>>:<<<°>>>|<<<°>>>,
		}

		18 = TEXT
		18 {
			field = xc_recently_commented_pages_comment
			wrap  = <<<°>>>comment<<<°>>>:<<<°>>>|<<<°>>>}<<<^>>>
		}          
	}
}

lib.posts = CONTENT
lib.posts {
	table = tt_board
	wrap  = |

	select {
		orderBy 		= crdate DESC
		max     		= TEXT
		max.data		= field : xc_recently_commented_pages_resultCount

		markers {
			BOARDPAGE.data 				= field : xc_recently_commented_pages_boardPage
			DISPLAY_LOCKED.data 		= field : xc_recently_commented_pages_displayLockedMessages
			DISPLAY_LOCKED_SUBS.data	= field : xc_recently_commented_pages_displayLockedSubMessages
		}

		where 			= pid = ###BOARDPAGE### AND ( ###DISPLAY_LOCKED### = 1 || ###DISPLAY_LOCKED### = 0 AND xc_angularcli_suite_locked = 0 )
		pidInList		= TEXT
		pidInList.data	= field : xc_recently_commented_pages_boardPage
		recursive     	= 10
		selectFields  	= subject, message, crdate, uid, author
  	}
  
  	renderObj = COA
	renderObj {
		10 = TEXT
		10 {
			field	= subject
      		wrap    = {<<<°>>>subject<<<°>>>:<<<°>>>|<<<°>>>,
		}
    
		11 = TEXT
		11 {
			field = message
			wrap  = <<<°>>>message<<<°>>>:<<<°>>>|<<<°>>>,
		}

		12 = TEXT
		12 {
			field = uid
			wrap  = <<<°>>>uid<<<°>>>:<<<°>>>|<<<°>>>,
		}

		13 = TEXT
		13 {
			field = author
			wrap  = <<<°>>>author<<<°>>>:<<<°>>>|<<<°>>>,
		}

		14 = TEXT
		14 {
			field 		= crdate
			strftime	= %d.%m.%Y
			wrap  		= <<<°>>>creation<<<°>>>:<<<°>>>|<<<°>>>}<<<^>>>
		}
	}
}

lib.postsSlug = CONTENT
lib.postsSlug {
	table = pages
	wrap  = |

	select {
		orderBy 		= crdate DESC
		max     		= TEXT
		max.data		= field : xc_recently_commented_pages_resultCount
		where 			= uid = ###BOARDPAGE###
		pidInList     	= 1
		recursive     	= 1000
		selectFields  	= slug

		markers {
			BOARDPAGE.data 	= field : xc_recently_commented_pages_boardPage
		}
  }
  
  renderObj = COA
	renderObj {
		10 = TEXT
		10 {
			field	= slug    
			wrap 	= | 		
		}
	}
}

tt_content.recentlyCommentedPagesListing =< lib.contentElement
tt_content.recentlyCommentedPagesListing {
  templateName = Listing

  templateRootPaths {
    10 = EXT:angularcli_suite/Resources/Private/Templates/RecentlyCommentedPages
  }

  partialRootPaths {
    10 = EXT:angularcli_suite/Resources/Private/Partials/RecentlyCommentedPages
  }

  variables {
	postsSlug 			< lib.postsSlug
	posts 				< lib.posts 
    listing 			< lib.recentlyCommentedPages
	containerListing	< lib.recentlyCommentedPages_In_Container
	images				< lib.recentlyCommentedPages_Images
	typo3Host			< lib.WaXCode_AngularCLISuite_AppMenu_Viewer_typo3Host

	limit 					= TEXT
	limit.data				= field : xc_recently_commented_pages_resultCount
	phoneSubstitutes 		= TEXT
	phoneSubstitutes.data 	= field : xc_recently_commented_pages_phoneSubstitutes
	boardPage 				= TEXT
	boardPage.data 			= field : xc_recently_commented_pages_boardPage
  }
}
4

According to TypoScript reference, you can assign SQL statements to the “where” property in TypoScript:

where = (title LIKE '%SOMETHING%' AND NOT doktype)

A possible solution could look like this:

lib.posts = CONTENT
lib.posts {
	table = tt_board
	wrap  = |

	select {
		orderBy 		= crdate DESC
		max     		= TEXT
		max.data		= field : xc_recently_commented_pages_resultCount

		markers {
			BOARDPAGE.data 				= field : xc_recently_commented_pages_boardPage
			DISPLAY_LOCKED.data 		= field : xc_recently_commented_pages_displayLockedMessages
			DISPLAY_LOCKED_SUBS.data	= field : xc_recently_commented_pages_displayLockedSubMessages
		}

		where = (NOT EXISTS (( WITH RECURSIVE cte AS ( SELECT uid, parent FROM tt_board x WHERE uid =x.uid0 UNION ALL SELECT t.uid, t.parent FROM tt_board t INNER JOIN cte ON t.parent = cte.uid ) SELECT 1 FROM cte WHERE xc_angularcli_suite_locked = 1 ))) ## query from example above
		pidInList		= TEXT
		pidInList.data	= field : xc_recently_commented_pages_boardPage
		recursive     	= 10
		selectFields  	= subject, message, crdate, uid, author
  	}
  
  	renderObj = COA
	renderObj {
		10 = TEXT
		10 {
			field	= subject
      		wrap    = {<<<°>>>subject<<<°>>>:<<<°>>>|<<<°>>>,
		}
    
		11 = TEXT
		11 {
			field = message
			wrap  = <<<°>>>message<<<°>>>:<<<°>>>|<<<°>>>,
		}

		12 = TEXT
		12 {
			field = uid
			wrap  = <<<°>>>uid<<<°>>>:<<<°>>>|<<<°>>>,
		}

		13 = TEXT
		13 {
			field = author
			wrap  = <<<°>>>author<<<°>>>:<<<°>>>|<<<°>>>,
		}

		14 = TEXT
		14 {
			field 		= crdate
			strftime	= %d.%m.%Y
			wrap  		= <<<°>>>creation<<<°>>>:<<<°>>>|<<<°>>>}<<<^>>>
		}
	}
}

I am not sure if the code above actually works, but maybe you have an idea how to solve your problem.
For more information, please refer to the TypoScript reference:
https://docs.typo3.org/m/typo3/reference-typoscript/13.4/en-us/Functions/Select.html#where

Let me know, if it works!
Greetings
Max

5

Hi Max,

thx for your help so far. Unfortunatly it does not work.

Isn’t there a way to pass the whole query?

Something like maybe:

lib.posts = CONTENT
lib.posts {
	table = tt_board
	wrap  = |

		markers {
			BOARDPAGE.data 				= field : xc_recently_commented_pages_boardPage
			DISPLAY_LOCKED.data 		= field : xc_recently_commented_pages_displayLockedMessages
			DISPLAY_LOCKED_SUBS.data	= field : xc_recently_commented_pages_displayLockedSubMessages
		}

           select.query = SELECT uid... WHERE pid = ###BOARDPAGE### AND ( NOT EXISTS...
  
  	renderObj = COA
	renderObj {
		10 = TEXT
		10 {
			field	= subject
      		wrap    = {<<<°>>>subject<<<°>>>:<<<°>>>|<<<°>>>,
		}
    
		11 = TEXT
		11 {
			field = message
			wrap  = <<<°>>>message<<<°>>>:<<<°>>>|<<<°>>>,
		}

		12 = TEXT
		12 {
			field = uid
			wrap  = <<<°>>>uid<<<°>>>:<<<°>>>|<<<°>>>,
		}

		13 = TEXT
		13 {
			field = author
			wrap  = <<<°>>>author<<<°>>>:<<<°>>>|<<<°>>>,
		}

		14 = TEXT
		14 {
			field 		= crdate
			strftime	= %d.%m.%Y
			wrap  		= <<<°>>>creation<<<°>>>:<<<°>>>|<<<°>>>}<<<^>>>
		}
	}
}
6

you’re welcome.
Unfortunately, there is no way to pass entire SQL queries in TypoScript. Maybe there is a syntax error in my TypoScript example, but according to the docs, using SQL in the where property (like in the docs) should work. As I don’t know, what you’re actually building, you’ll need to figure out the exact query by yourself.

You can start with applying a simple where condition in your TypoScript and work through it step-by-step. Also, you can use a debugger to debug the “CONTENT” content object (located in vendor/typo3/cms-frontend/Classes/ContentObject/ContentContentObject.php) in order to get an understanding what’s happening under the hood.

Greetings
Max

1 Like