How to find all documents(count) inside a parent folder which has a specific Category attached?

Manoj Agrawal

Hi, I have this query to find all documents which has a category added. Although I need to refine it to search all docs inside a specific parent folder with various childs inside it.

Could you please help?

select DATEPART(YEAR, dt.createdate) "CreateYear", count(*) "CreateCnt"
from Calligo.dbo.dtree dt
       join LLATTRDATA lla on dt.dataid = lla.id and lla.defid = 11715186(Category ID) and lla.attrtype = -18 and lla.vernum=dt.VersionNum
where dt.subtype = 144
group by DATEPART(YEAR, dt.createdate)

I appreciate your help.

Manoj

Nizar Ghazal

Quoted Manoj Agrawal on 02/05/2018 10:54 AM:

Hi, I have this query to find all documents which has a category added. Although I need to refine it to search all docs inside a specific parent folder with various childs inside it.

Could you please help?

select DATEPART(YEAR, dt.createdate) "CreateYear", count(*) "CreateCnt"
from Calligo.dbo.dtree dt
       join LLATTRDATA lla on dt.dataid = lla.id and lla.defid = 11715186(Category ID) and lla.attrtype = -18 and lla.vernum=dt.VersionNum
where dt.subtype = 144
group by DATEPART(YEAR, dt.createdate)

I appreciate your help.

Manoj

Hi Manoj,

To limit your results to everything within a specific folder, you can reference AncestorID.

You'll need to JOIN to DTreeAncestors (DBrowseAncestors isn't in your version of CS), specify what the main AncestorID is, which will provide everything within a certain directory:

Here's a simple example, where I want all records from DTREE, LLATTRDATA and DTREEANCESTORS (you'll want to limit this, surely), where it's in a folder with a DataID of 177927.

SELECT * FROM CS16.DTREE dt 
JOIN CS16.LLATTRDATA lla ON dt.DataID=lla.ID 
JOIN CS16.DTreeAncestors dba ON dt.DataID=dba.DataID
WHERE dba.AncestorID = '177927'

This will find all items in sub-containers, too.