Typo3 Typoscript-Select use OR-operator in where c

Question

I try select all Files from table sys_files which are linked with a category or the subcategories.

On my Example the main category for files has the ID 1 and there are some sub-categories on it.

I first created the SQL-Code, to try it out directly on the database:

SELECT sys_file.name, sys_file.identifier, sys_category.title 
FROM sys_category 
RIGHT JOIN sys_file_metadata ON (sys_file_metadata.categories = 
sys_category.uid) 
JOIN sys_file ON (sys_file.uid = sys_file_metadata.file) 
WHERE (sys_category.parent = 1) OR (sys_category.uid = 1) 
order By sys_category.title

This works fine as expected.

Now, I tried to do it similar in typoscript, this looks like:

lib.documentindex = CONTENT
lib.documentindex {
  wrap = <ul>|</ul>

  table = sys_category
  select {
    selectFields = sys_file.name, sys_file.identifier, sys_category.title
    rightjoin = sys_file_metadata ON (sys_file_metadata.categories = sys_category.uid) join sys_file ON (sys_file.uid = sys_file_metadata.file)
    where = sys_category.uid = 1 OR sys_category.parent = 1
    orderBy = sys_category.title
  }

  renderObj = COA
  renderObj.wrap = <li>|</li>
  renderObj.10 = TEXT
  renderObj.10 {
    field = identifier
    wrap = <a href="|">
  }
  renderObj.20 = TEXT
  renderObj.20.field = name
  renderObj.30 = TEXT
  renderObj.30.value = </a>

}

And this dosen't work. But really strange is, it works halfway. So if i write the where like this:

where = sys_category.uid = 1 OR sys_category.parent = 1

It displays as all files with a category on which the parent is 1. But it dosen't display files with the category where the id is 1.

Do I now write it like

where = sys_category.parent = 1 OR sys_category.uid = 1

It works the other way around, it displays files with the category where the id is 1. But none where the parent id is 1.

In the official documentation of the select (found here), it just tells on the where-option:

WHERE clause without the word "WHERE".

But this is not all. I tried so much things and pretty everything i tried don't behave like real SQL code. I don't know if this typo3-thing is buggy as hell or if I just use it totally wrong.

Answer 1

I think your query is wrong (even the pure SQL).
categories are never(?) referenced immediately, but always with the mm-records in sys_category_record_mm.
So your join needs to be another one where you join sys_category with sys_file via these mm-records (and the sys_file_metadata records):

SELECT sys_file.name, sys_file.identifier, sys_category.title
FROM sys_category
JOIN sys_category_record_mm 
  ON sys_category_record_mm.uid_local = sys_category.uid
JOIN sys_file_metadata 
  ON sys_file_metadata.uid = sys_category_record_mm.uid_foreign
JOIN sys_file 
  ON sys_file_metadata.file = sys_file.uid
WHERE sys_category_record_mm.tablenames = "sys_file_metadata" 
  AND sys_category_record_mm.fieldname = "categories"
  AND ((sys_category.parent = 1) OR (sys_category.uid = 1)) 
ORDER By sys_category.title

be aware: there are category fields in categorized records, but those only hold a counter of the references (given by the mm-records). It is not the uid of a category.
Might be misleading if you use the category with uid = 1 much often.

Here is the typoscript realizing this Query:

Edit: Included TypoScript (by FuFu) This typoscript-select worked for me, but I had to move categories out of the root to the first page.

lib.documentindex = CONTENT
lib.documentindex {
  wrap = <ul>|</ul>

  table = sys_category
  select {
    pidInList = 1
    recursive = 1000
    selectFields = sys_file.name, sys_file.identifier, sys_category.title
    join = sys_category_record_mm ON (sys_category_record_mm.uid_local = sys_category.uid) JOIN sys_file_metadata ON (sys_file_metadata.uid = sys_category_record_mm.uid_foreign) JOIN sys_file ON (sys_file_metadata.file = sys_file.uid)
    where = (sys_category_record_mm.tablenames = "sys_file_metadata") AND (sys_category_record_mm.fieldname = "categories") AND ((sys_category.parent = 1) OR (sys_category.uid = 1))
    orderBy = sys_category.title
  }

  renderObj = COA
  renderObj.wrap = <li>|</li>
  renderObj.10 = TEXT
  renderObj.10 {
    field = identifier
    wrap = <a href="|">
  }
  renderObj.20 = TEXT
  renderObj.20.field = name
  renderObj.30 = TEXT
  renderObj.30.value = </a>

}

---

As

Answer 2

Did you try it with DataProcessing? You can combine two DatabaseQueryProcessors to get what you need. See: https://docs.typo3.org/typo3cms/TyposcriptReference/7.6/ContentObjects/Fluidtemplate/Index.html#dataprocessing