[TYPO3-dam] Selection with categories

[Marco Huber]

Hi,

I found a strange behavior using selections in the DAM-List module. In 
my case some files have categories assigned and some files not. As soon 
as I add a category to my selection (click on + or - in the tree frame), 
all files without a category are filtered out. This is, because in the 
SQL statement the CONCAT function is used to filter by category. But 
this function doesn't work with NULL values. And in the joined table all 
files without a category have a NULL in the field tx_dam_cat.uid.
Here an example:

10 files with catA1 (id=1) in folderA
10 files with catA2 (id=2) in folderA
10 files without category in folderB

Example [1]
selection: folderB
result: 10 files from folderB

Example [2]
selection: folderB OR catA
expected result: 20 files (10 files from folderB + 10 files from catA1)
result: 10 files from catA

Example [3]
selection: (folderB OR folderA) AND NOT catA2
expected result: 20 files (10 files from folderB + 10 files from folderA 
without catA2)
result: 10 files from folderA without catA2

The SQL-Statement (a little bit modified to get only the relevant 
fields) for [3] is:
SELECT tx_dam.uid as damuid, tx_dam_cat.uid as catuid, tx_dam.file_path 
as folder
FROM tx_dam
   LEFT JOIN tx_dam_mm_cat ON tx_dam.uid=tx_dam_mm_cat.uid_local
   LEFT JOIN tx_dam_cat ON tx_dam_mm_cat.uid_foreign = tx_dam_cat.uid
WHERE 1=1
   AND tx_dam.pid IN (1) AND tx_dam.sys_language_uid IN (0,-1)
   AND tx_dam.file_status!=255
   AND (tx_dam.file_path LIKE BINARY 'folderB%' OR tx_dam.file_path LIKE 
BINARY 'folderA%')
   AND tx_dam.deleted=0
GROUP BY tx_dam.uid
   HAVING 1
     AND (NOT FIND_IN_SET(2,GROUP_CONCAT(tx_dam_cat.uid)))

When I remove the GROUP BY part (== AND NOT catA2) I get this (correct) 
table:
damuid | catuid | folder
--------------------------
1      | 1      | folderA
2      | 1      | folderA
...
10     | 1      | folderA
11     | 2      | folderA
12     | 2      | folderA
...
20     | 2      | folderA
21     | NULL   | folderB
22     | NULL   | folderB
...
30     | NULL   | folderB

Then the GROUP_CONCAT begins to work and "removes" every row with a NULL 
in field catuid (see MySQL-Doc 
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) 
and after that the NOT FIND_IN_SET begins to work and returns every row 
with no 2 in field catuid. So only the entries in folderA with catuid=1 
remain. The expected result would be, that entries in folderA with 
catuid=1 and entries in folderB would remain.

The fastest solution (query time) I found was to build a subselect in 
the where clause. Then everything works like expected and you can leave 
out the JOINs.

Appending the GROUP BY clause is implemented in the class 
tx_dam_selectionCategory 
(dam/components/class.tx_dam_selectionCategory.php) in the function 
selection_getQueryPart. And the category selection is the only one which 
uses the GROUP BY clause. All other selection component classes append 
their stuff to the WHERE clause.

What do you think about this issue? Shall I create a bug report?


Kind regards,
Marco