Typo3 Typoscript-Select在以下条件下使用OR运算符 [英] Typo3 Typoscript-Select use OR-operator in where condition

问题描述

我尝试从与类别或子类别链接的表sys_files中选择所有文件.

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

在我的示例中，文件的主要类别的ID为1，上面有一些子类别.

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

我首先创建了SQL代码，以直接在数据库中进行尝试:

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

这按预期工作正常.

现在，我尝试在打字稿中执行类似的操作，

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

它显示为父类别为1的所有文件. 但它不会显示ID为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.

我现在这样写吗

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

反之亦然，它显示类别为id为1的文件，但不显示父项id为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.

在select的官方文档中(请在此处)，它只是告诉where-option:

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

没有单词"WHERE"的WHERE子句.

WHERE clause without the word "WHERE".

但这还不是全部.我做了很多事情，几乎所有尝试的行为都不像真正的SQL代码.我不知道这笔错字是不是像地狱般的越野车，还是我完全错误地使用它.

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.

推荐答案

我认为您的查询是错误的(甚至是纯SQL).
类别从不立即引用(?)，但始终与sys_category_record_mm中的mm记录一起引用. 因此，您的联接需要是另一联接，您可以通过以下mm记录(和sys_file_metadata记录)将sys_category与sys_file联接:

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

请注意::分类记录中有类别字段，但是这些字段仅包含引用的计数器(由mm记录提供).它不是类别的uid.
如果您经常使用uid = 1的类别，可能会产生误导.

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:

包含的TypoScript(由FuFu提供) 这种笔录选择很适合我，但是我不得不将类别从根目录移到第一页.

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

这篇关于Typo3 Typoscript-Select在以下条件下使用OR运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！