Error in element query when includeSubfolders is true on root folders

[aelvan]

Description

There seems to be a bug when querying Assets and using includeSubfolders(true). Short story, if the folderId that's passed into folderId() is the root folder of a volume, that folder is omitted from the resulting query.

Steps to reproduce

I have a folder structure like this:

— Volume root folder (folderId: 1)
—— Subfolder (folderid: 11)
——— Another subfolder (folderId: 12)

If I do...

$count = Asset::find()->folderId(1)->includeSubfolders(true)->limit(null)

...only assets in the subfolders are returned, not assets in the root folder itself.

The query generated (using getRawSql()) is:

'SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `assets`.`volumeId`, `assets`.`folderId`, `assets`.`filename`, `assets`.`kind`, `assets`.`width`, `assets`.`height`, `assets`.`size`, `assets`.`focalPoint`, `assets`.`dateModified`, `volumeFolders`.`path` AS `folderPath`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_aCkeditorField`, `content`.`field_aPlainTextField`, `content`.`field_linkTest`, `content`.`field_linkitField`, `content`.`field_preparseNumberField`, `content`.`field_someTextField`, `content`.`field_testMovePreparseField`, `content`.`field_testPreparseField`, `content`.`field_text`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `assets` `assets` ON `assets`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id`
WHERE ((`assets`.`folderId` = 11) OR (`assets`.`folderId` = 12)) AND (`elements_sites`.`siteId`=\'1\') AND (`content`.`siteId`=\'1\') AND (`elements`.`archived`=FALSE) AND (`elements_sites`.`enabled`=TRUE)
ORDER BY `elements`.`dateCreated` DESC) `subquery`
INNER JOIN `assets` `assets` ON `assets`.`id` = `subquery`.`elementsId`
INNER JOIN `volumefolders` `volumeFolders` ON `assets`.`folderId` = `volumeFolders`.`id`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
ORDER BY `elements`.`dateCreated` DESC'

The folderIds queried in the WHERE are the two subfolders, not the root folder itself: WHERE ((assets.folderId = 11) OR (assets.folderId = 12))....

If I change the query to...

$count = Asset::find()->folderId(11)->includeSubfolders(true)->limit(null)

...I get the expected result, all Assets in the two subfolders. And the query has the correct folderIds in the WHERE: WHERE ((assets.folderId = 11) OR (assets.folderId = 12))....

If I do it on folderId 12, I also get the expected result; only Assets in the subfolder with id 12.

If I do...

$count = Asset::find()->folderId(1)->includeSubfolders(false)->limit(null)

I get the expected result; only Assets that are in the root folder.

This has been tested both on a local volume and a S3 volume with the same results.

Additional info

• Craft version: 3.0.1
• PHP version: 7.0.27
• Database driver & version: Mysql 5.6.38
• Plugins & versions: -

[brandonkelly]

Fixed for the next release, thanks!