Querying the Alfresco repository (~DQL)

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

bboeri

I understand that Alfresco has no document query language similar to Documentum's DQL.  Can anyone suggest a way to generate reports with the following kinds of information (assuming requisite permissions):

1) Get a complete count of all documents of a specific type
2) Get a complete count of all documents modified between two dates
3) The trickiest one: Get a report of all documents submitted to a workflow within a specific timeframe (e.g., via Activiti), so I could get a list of when each began and completed the workflow, so I could then generate stats such as "average time in the workflow" etc.

I suspect one possibility for 1and 2 would be appropriate Lucene queries, and then looking at a total count of results returned.  In Documentum, however, that wouldn't work because the search system (FAST, and maybe even Verity) returned a maximum result count of from 200-500 results, which in many cases would be far fewer than the possible search results.  Thus, at least for Documentum, the use of DQL.

TIA!
Bob Boeri

jpotts

You might be interested in the querying capability that the CMIS Query Language provides. And it is only one letter different from DQL!

Seriously, though, it does give you a query capability closer to what you are probably used to with DQL.

Here's a Python example that uses cmislib to count all documents of a specific type, sc:whitepaper:

>>> query = 'select cmis:objectId from sc:whitepaper'
>>> rs = repo.query(query)
>>> len(rs)
3‍‍‍‍

Date ranges are similarly easy. Here's the query for a date range that also joins on a custom aspect, sc:webable, to find objects with the published date in the given range:

queryString = """select d.cmis:objectId, w.sc:published
                         from sc:whitepaper as d
                         join sc:webable as w on d.cmis:objectId = w.cmis:objectId
                         where w.sc:published > TIMESTAMP '2006-01-01T00:00:00.000-05:00'
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and w.sc:published < TIMESTAMP '2007-06-02T00:00:00.000-05:00'"""‍‍‍‍‍

If you want to run these on your own using Java instead of Python, check out this custom content types tutorial that includes a section with several sample queries.

Your third request is indeed the trickiest. The easiest thing to do is to set a start date and an end date on the content from within the workflow. Then you can use CMIS Query Language to query for those properties. If you don't like that idea you can instead write data to a "workflow log" object (or whatever you want to call it–it is up to you to define) and then query against those objects.

Activiti may have some built-in reporting that could help you here but I haven't looked into it. This kind of workflow reporting is a requirement we hear a lot.

Hope this has gotten you a little closer to your goal.

Jeff

PS. I recognize your name. Are you the same Bob Boeri that used to edit EContent Mag?