postgresql - How to get the name of a document in Alfresco with SQL? -


how retrieve name of document alfresco database (postgresql)? i'm trying list of documents created given user, e.g. admin, starting date, e.g. 2015-05-03:

select child.child_node_name, node.audit_created alf_child_assoc child, alf_node node, alf_node_properties prop, alf_qname qname child.child_node_id = node.id , node.id = prop.node_id , prop.qname_id = qname.id , qname.local_name = 'content' , node.audit_creator = 'admin' , node.audit_created > '2015-05-03' order node.audit_created 

how actual documents , not content items? because displays full node references , want human readable name of document. suggestions?

by way, i'm working on back-end (repository), not on share. , i'm using alfresco 5.0.1.

updated

so here sql need use, 1 cm:content type:

select nd.audit_creator creator,         np.string_value document_name,         nd.audit_created created_on   alf_node nd, alf_node_properties np,         alf_namespace ns, alf_qname qn, alf_qname qn1  nd.id=np.node_id    , qn.ns_id = ns.id    , nd.type_qname_id = qn.id    , ns.uri = 'http://www.alfresco.org/model/content/1.0'    , qn.local_name = 'content'    , qn1.ns_id = ns.id    , np.qname_id = qn1.id    , qn1.local_name = 'name'    , nd.audit_created > '2015-05-06 14:59:00'; 

it return human readable document name, username of creator , date when document created.

and if have custom type of document, let's ep:content namespace http://www.mycomp.com/model/epersonnel/1.0 query work:

select nd.audit_creator creator,         np.string_value document_name,         nd.audit_created created_on   alf_node nd, alf_node_properties np,         alf_namespace ns, alf_namespace ns1, alf_qname qn, alf_qname qn1  nd.id=np.node_id    , qn.ns_id = ns.id    , nd.type_qname_id = qn.id    , ns.uri = 'http://www.mycomp.com/model/epersonnel/1.0'    , qn.local_name = 'content'    , ns1.uri = 'http://www.alfresco.org/model/content/1.0'    , np.qname_id = qn1.id    , qn1.ns_id = ns1.id    , qn1.local_name = 'name'    , nd.audit_created > '2015-05-06 14:59:00'; 

Comments