ACS- DB query to get the UUIDs of all documents belonging to a particular site?

Question

Hi,

I am trying to get all the UUID of the documents in a particular ACS site- All documents in the site are of a custom doctype(lets say: customDocType)-

I am using the below db query:
SELECT
nodes.UUID
FROM
alf_node nodes, alf_qname names
WHERE
nodes.type_qname_id=names.id 
AND names.local_name='customDocType';

This gives me a list of UUIDs across the repository-

Could any one let me know whether there is an additional query condition I could add to the above db query which would give me only the UUIDs of documents within a particular ACS site.

Thank You...

Regards.

afaust · Accepted Answer

Querying documents for a specific site via the database is not possible using a simple query condition. You would need to create a stored procedure / function that is able to recursively iterate over the folder/file structure (via alf_child_assoc table) to do the actual query.

This is one of the main reasons that Alfresco cannot / does not support SITE or ANCESTOR queries as transactional metadata queries (TMQ) using Alfresco FTS.