Access Files in Box Accounts with Nested Folders

This article is a guide for retreiving file information within a specified folder using the CData Box driver. Since "within a folder" can mean different things, such as including or excluding subfolders, this article explains how to retrieve file information in the following three cases:

1. Retrieve file information only directly within the specified folder.
2. Retrieve all file information including subfolders within the specified folder.
3. Retrieve file information only for files directly within the subfolders of the specified folder.

As a scenario for explanation, suppose folders and files are arranged in Box as follows. There is a folder called FolderA directly under the root, and there are three files and two folders directly under it. The two folders are SubFolderA and SubFolderB, each containing three files. The ID of FolderA is "123", and this ID is used to retrieve file information using the driver.

FolderA (ID: 123)

• test_FolderA1.txt
• test_FolderA2.txt
• test_FolderA3.txt

• SubFolderA

  • test_SubFolderA1.txt
  • test_SubFolderA2.txt
  • test_SubFolderA3.txt

• SubFolderB

  • test_SubFolderB1.txt
  • test_SubFolderB2.txt
  • test_SubFolderB3.txt

1. Retrieve file information only directly within the specified folder

File information stored in Box is retrieved from the Files table. For example, executing the query "SELECT * FROM Files" retrieves information for all files in Box. To retrieve only the three files directly within FolderA, specify the ID of FolderA in the ParentId filter condition as follows:

SELECT Name, Path FROM Files WHERE ParentId = '123';

Executing the above query retrieves the following file information directly under FolderA:

2. Retrieve all file information including subfolders within the specified folder

To retrieve all file information including subfolders within FolderA, specify the ID of FolderA in the SearchRootId filter condition as follows:

SELECT Name, Path FROM Files WHERE SearchRootId = '123';

Executing the above query retrieves the following file information, including the three files in FolderA and all files in the subfolders SubFolderA and SubFolderB. The number of subfolder levels to search can be specified with the DirectoryRetrievalDepth connection property.

3. Retrieve file information only for files directly within the subfolders of the specified folder

To exclude file information directly under FolderA and retrieve only the information of files in the subfolders, use a subquery as follows. Executing this query retrieves the IDs of the two folders in FolderA with the subquery SELECT Id FROM Folders WHERE ParentId = '123', and passes them to the filter condition SELECT Name, Path from Files WHERE ParentId = ?. This means the driver executes SELECT Name, Path from Files WHERE ParentId = SubFolderA's Id and SELECT Name, Path from Files WHERE ParentId = SubFolderB's Id, and combines the results.

SELECT Name, Path from Files WHERE ParentId IN (SELECT Id FROM Folders WHERE ParentId = '123');

Executing the above query retrieves the following file information (only files in the subfolders SubFolderA and SubFolderB within FolderA):

Free Trial & More Information

Thanks to CData connectivity solutions, working with your Box file data has never been easier. Try CData drivers and connectors. Download a free, 30-day trial.