TYPO3 DatabaseQueryProcessor: Multiple Outputs of same UIDs

Question

I'm trying to get the certain categories (on pid 1) with DatabaseQueryProcessor, which works almost perfectly, but some of the categories are displayed multiple times on the output of the variable.

(I am using headless typo3 10: https://github.com/TYPO3-Initiatives/headless)

brandcategories = JSON
brandcategories {
    dataProcessing {
        10 = TYPO3\CMS\Frontend\DataProcessing\DatabaseQueryProcessor
        10 {
            as = brandcategories
            selectFields = sys_category.uid,sys_category.title
            table = sys_category
            pidInList = 1
            where = parent=13
            where.insertData = 1
            join = sys_category_record_mm on sys_category_record_mm.uid_local = 
            sys_category.uid
        }
    }
}

This gives me the following Output:

 [
  { "data": { "uid": 14, "title": "Barlow Tyrie", "pid": 1, "t3ver_state": 0 } },
  { "data": { "uid": 14, "title": "Barlow Tyrie", "pid": 1, "t3ver_state": 0 } },
  { "data": { "uid": 16, "title": "Dedon", "pid": 1, "t3ver_state": 0 } },
  { "data": { "uid": 17, "title": "Manutti", "pid": 1, "t3ver_state": 0 } },
  { "data": { "uid": 17, "title": "Manutti", "pid": 1, "t3ver_state": 0 } },
  { "data": { "uid": 19, "title": "Extremis", "pid": 1, "t3ver_state": 0 } }
]

As you can see uid 14 is displayed twice, as well as Manutti. It would be great, if someone could tell me, how i can filter this, so I get every ID just once. Thanks!

user_1019850 · Answer

In general you can limit the results of queries like this with two different options:

by usage of the mysql-Expression DISTINCT, I've no experience though if this can be used in TypoScript like selectFields = DISTINCT sys_category.uid,sys_category.title.
On this page is written

There is no support for DISTINCT, a ->groupBy() has to be used instead.

I'm not sure if that is applicable for your TypoScript-problem too.
by grouping the results with groupBy in TypoScript:
groupBy = sys_category.uid

A general explanation about both methods can be found here, the explanation is only related to MySQL and not to TypoScript.

Furthermore you could add more constraints to the join, so that the options above are not required.

TYPO3 DatabaseQueryProcessor: Multiple Outputs of same UIDs

1 Answers