CODEWITHSUNDEEP
javadb2filenet-p8filenet-content-engine
Ingila Ejaz
Ingila Ejaz

Reputation: 233

FileNet: Count of total documents in a FileNet Folder

I am working on a FileNet project where I need to display the count of documents in a folder named Others. I can do it easity via CE API but I need to know where this information is stored in FileNet DB.

The document count I can get from a table called DocVersion, the folder information is stored at Container table. What I need is a query that goes like:

SELECT COUNT(*) FROM DOCVERSION D, CONTAINER C WHERE --container name is 'Others'

Any help would be highly appreciated.

Upvotes: 1

Views: 13635

Answers (6)

Ajay Kumar
Ajay Kumar

Reputation: 3272

Hope this helps.

    public void countobjectsinAllOS() {
    Connection conn = Factory.Connection.getConnection(ConfigInfo.CE_URI);
    Domain domain = Factory.Domain.fetchInstance(conn, null, null);

    Iterator<ObjectStoreSet> it = domain.get_ObjectStores().iterator();

    int countofos = 0;
    while (it.hasNext()) {

        ObjectStore os = (ObjectStore)it.next();
        String osname = os.get_DisplayName();

        System.out.println("Working on Object Store " +osname);
        countofos++;
        countObjectsInAnOS(osname);
    }
    System.out.println("\n\n");
    //System.out.println("Number of Object Stores found in doamin >>\t" + domain.get_Name() + "\t<< is " + countofos);
}

public void countObjectsInAnOS(String OSName) {
    Connection conn = Factory.Connection.getConnection(ConfigInfo.CE_URI);
    Domain domain = Factory.Domain.fetchInstance(conn, null, null);
    ObjectStore os = Factory.ObjectStore.fetchInstance(domain, OSName, null);
    // Create a SearchSQL instance and specify the SQL statement (using the 
    // helper methods).
    SearchSQL sqlObject = new SearchSQL();
    sqlObject.setSelectList("*");
    //sqlObject.setMaxRecords(10);
    sqlObject.setWhereClause("f.This INSUBFOLDER '/'");
    sqlObject.setFromClauseInitialValue("Folder", "f", false);
    domain.get_ObjectStores();
    // Uncomment below lines for Documents    
    //    sqlObject.setSelectList("d.DocumentTitle, d.Id");
    //    sqlObject.setMaxRecords(20);
    //    sqlObject.setFromClauseInitialValue("Document", "d", false);  
    // Check the SQL statement.          

    //Uncomment to see the SQL

    //System.out.println("SQL: " + sqlObject.toString());

    // Create a SearchScope instance. (Assumes you have the object store 
    // object.)
    Boolean continuable = new Boolean(true);

    // Set the page size (Long) to use for a page of query result data. This value is passed 
    // in the pageSize parameter. If null, this defaults to the value of 
    // ServerCacheConfiguration.QueryPageDefaultSize.
    Integer myPageSize = new Integer(10);

    // Specify a property filter to use for the filter parameter, if needed. 
    // This can be null if you are not filtering properties.
    //        PropertyFilter myFilter = new PropertyFilter();
    //        int myFilterLevel = 1;
    //        myFilter.setMaxRecursion(myFilterLevel);
    //        myFilter.addIncludeType(new FilterElement(null, null, null, FilteredPropertyType.ANY, null));

    // Set the (Boolean) value for the continuable parameter. This indicates 
    // whether to iterate requests for subsequent pages of result data when the end of the 
    // first page of results is reached. If null or false, only a single page of results is 
    // returned.
    // Execute the fetchObjects method using the specified parameters.
    //IndependentObjectSet myObjects = search.fetchObjects(sqlObject, myPageSize, myFilter, continuable);
    SearchScope searchScope = new SearchScope(os);
    RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, myPageSize, null, continuable);
    long count = 0;
    Iterator<RepositoryRow> it = rowSet.iterator();
    while (it.hasNext()) {
        it.next();
        count++;
    }
    //System.out.println("Total number of Documents >>\t\t" + count + "\n\n");
    System.out.println("Total number of Folders >>\t\t" + count + "\n\n");

}

Upvotes: 0

Tilo
Tilo

Reputation: 1220

Here some more example for direct DB queries (not ACCE/FEM SQL) to find unfiled doc, but can be change to find object filed in specific folder. (change where rel.Head_id IS NULL to your folder)

Tested and run on MS SQL. 

--Count by Doc Class
SELECT Doc.object_class_id, count(distinct Doc.version_series_id) as '# Docs' , ClassDef.symbolic_name as ClassName
FROM DocVersion doc
LEFT JOIN Relationship rel ON doc.object_id = rel.Head_id
INNER JOIN ClassDefinition ClassDef ON doc.object_class_id = ClassDef.[object_id]
where rel.Head_id IS NULL and doc.is_current=1
group by Doc.object_class_id, ClassDef.symbolic_name
order by '# Docs' desc

--by creator
SELECT Doc.creator, count(distinct Doc.version_series_id) as '# Docs'
FROM DocVersion doc
LEFT JOIN Relationship rel ON doc.object_id = rel.Head_id
where rel.Head_id IS NULL and doc.is_current=1
group by Doc.creator 
order by '# Docs' desc

--list Of Docs
SELECT Doc.creator, doc.create_date, doc.u32_documenttitle , doc.modify_date ,doc.modify_user , ClassDef.symbolic_name as ClassName
FROM DocVersion doc
LEFT JOIN Relationship rel ON doc.object_id = rel.Head_id
INNER JOIN ClassDefinition ClassDef ON doc.object_class_id = ClassDef.[object_id]
where rel.Head_id IS NULL and doc.is_current=1
order by Doc.create_date

Upvotes: 0

Christian Bobadilla
Christian Bobadilla

Reputation: 1

These is the right query to get the total amount of documents in a specific folder...

db2 "select count(*) from OSDBUSER.Relationship r, OSDBUSER.Container c, OSDBUSER.DOCVERSION d where r.Tail_Id = c.Object_Id and r.Head_Id = d.Object_Id and c.name = 'Error'"

In this query 'Error' is the name of the Folder to count the docs....

Upvotes: 0

bwstudio
bwstudio

Reputation: 11

Thanks for your query and I have tried it. There is one issue in your query. You used the head_id and tail_id in the wrong direction. The correct query should be as following.

select count(r.Object_Id) 
from DocVersion d, Container c, Relationship r 
where r.Tail_Id = c.Object_Id and d.Object_Id = r.Head_Id --// you can exclude this if in the Folder filed only documents and not custom objects. 
and c.Object_Id = {folder-id} --// or use c.Name = 'Other' - you can't use PathName field in DB query.

Upvotes: 1

swepss
swepss

Reputation: 501

You are right. Documents placed in DocVersion, Folders places in Container table. But link between Documents and Folders placed in Relationship table.

If you use FileNet API you can try to use next FN query

Select d.Id from Document d
where d.This INFOLDER '/bla/bla/bla'

or INSUBFOLDER operator. And at the next step you will need to count result set.

If you want to get information from Database directly, you can try to use next query.

select count(r.Object_Id) from DocVersion d, Container c, Relationship r
where r.Head_Id = c.Object_Id
and d.Object_Id = r.Tail_Id --// you can exclude this if in the Folder filed only documents and not custom objects.
and c.Object_Id = {folder-id} --// or use c.Name = 'Other' - you can't use PathName field in DB query.

Upvotes: 2

Archangle
Archangle

Reputation: 322

I have not try the code in application but, it will be like..

SELECT count (*) as Row_Count

FROM Container c, DocVersion d

WHERE c.object_id = d.object_class_id

AND c.name = 'Others'

Hope this will help you with your idea.

Upvotes: 0

Related Questions