2

How to query folder and document both so that we can get:

  1. All the subfolder documents.
  2. Only the folder documents.
  3. Count of documents in the subfolder, with the folder name.
palacsint
  • 28,416
  • 10
  • 82
  • 109
sanyog
  • 51
  • 1
  • 7
  • 1
    And what exactly don't work for you? – Tomasz Kowalczyk Jul 31 '14 at 11:39
  • For question 3, are you looking for something like an expanded tree view with a number of contained documents beside each? That's really hard, if not impossible, to achieve via DQL only. I'd recommend several DQLs and additionally some code. – eivamu Jul 31 '14 at 11:42
  • I felt some joins can make out.. – sanyog Jul 31 '14 at 11:45
  • Yes, it could, but if you need the paths you will run into problems with selecting from several types and repeating attributes at the same time, which is impossible if I recall correctly. If you do need _one single_ query for all of this I'd suggest using sub queries. You'll also need some form of `GROUP BY` to get the count for each folder. – eivamu Jul 31 '14 at 11:48
  • 1
    Ok thanks eivamu... I will post if i come across some solution... – sanyog Jul 31 '14 at 11:50
  • It's not that hard to come up with the third one, one implicit join and it's there. – Miki Sep 19 '14 at 23:57

4 Answers4

4

  1. All documents within a folder and all its subfolders:

    SELECT * 
FROM dm_document 
WHERE FOLDER('/MyCabinet/MyFolder', DESCEND)

  2. All documents within a folder without subfolders:

    SELECT * 
FROM dm_document 
WHERE FOLDER('/MyCabinet/MyFolder')
eivamu
  • 3,025
  • 1
  • 14
  • 20
3

I took the liberty to name queries a bit differently than OP asked it, but it fits it too. It is just a bit more clearer what some query returns. I used Temp folder for this example.

  1. All documents in folder including subfolders
SELECT *
FROM dm_document 
WHERE FOLDER('/Temp', DESCEND)
  1. All documents in folder 
SELECT * 
FROM dm_document 
WHERE FOLDER('/Temp')
  1. Count of documents in specific subfolders under some folder
SELECT COUNT(doc.r_object_id) AS doc_ammount, fol.object_name AS folder_object_name 
FROM dm_document doc, dm_folder fol 
WHERE FOLDER('/Temp', descend) AND ANY doc.i_folder_id = fol.r_object_id 
GROUP BY (fol.object_name)
Miki
  • 2,493
  • 2
  • 27
  • 39
1

All the subfolder documents:

SELECT r_object_id, object_name 
FROM dm_document(all) 
WHERE folder('/myCabinet/myFolder/', DESCEND)

Only the folder documents: I guess it's similar to the query above but without the DESCEND keyword. (I can't test it now.) (I'm also not sure that (all) is required or not.)

palacsint
  • 28,416
  • 10
  • 82
  • 109
  • The `(all)` means it'll select all versions. Normally one would do without. `DESCEND` works fine. – eivamu Jul 31 '14 at 11:44
0

In case you have unique folder in docbase and you want to find documents in this folder 

select * from dm_document where any i_folder_id in (select r_object_id from  
dm_folder where object_name='unique_folder_name')
Yuriy N.
  • 4,936
  • 2
  • 38
  • 31