nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to distinguish different nodes of type "content".

Muffex

Hey there.

I've searched several threads regarding SQL statements to gather information on files, folders, etc. I'm well aware that the proper way would be to use the API or CMIS.

But for my case I have to rely on SQL.

When selecting the nodes (from the alf_node table) I filter on alf_qname.local_name = 'content'.
These content nodes, however, contain (uploaded) files, wiki-pages, blog-posts, discussion-topics and other nodes.

How can I properly distinguish these different content nodes? I.e. how to only select (uploaded) files? How to only select blog-posts? etc.

kind regards

Find more posts tagged with

Alfresco Content Services

Accepted answers

Muffex

Well, I finally figured "something" out.

I can retrieve most of the nodes that refer to "files". It's neither well optimized nor can I guarantee it will work.

But for my needs it is currently sufficient.

SELECT n.id AS nodeId, n.uuid AS fileId, n.audit_created AS created, n.audit_modified AS last_updated, anp.string_value AS name, n.audit_creator AS creator, CONCAT('http://alfresco.bas.uni-koblenz.de/share/page/document-details?nodeRef=',store.protocol,'://',store.identifier,'/',n.uuid) AS content
  FROM alf_node AS n
    JOIN alf_qname AS q on (n.type_qname_id = q.id AND q.local_name='content')
    JOIN alf_store AS store ON (store.id = n.store_id)
    JOIN alf_node_properties AS anp on (n.id = anp.node_id)
    LEFT JOIN alf_content_data AS acd ON (anp.long_value = acd.id)
    JOIN alf_child_assoc AS aca ON (aca.child_node_id = n.id)
    JOIN alf_node AS nP ON (aca.parent_node_id = nP.id)
    JOIN alf_qname AS aqn ON (np.type_qname_id = aqn.id)
    WHERE anp.qname_id IN 
      (SELECT id FROM alf_qname
        WHERE local_name='name' 
          AND ns_id IN 
            (SELECT ns2.id FROM alf_namespace AS ns2 WHERE uri LIKE '%content%'))
      AND n.audit_creator NOT LIKE 'System'
      AND aqn.local_name not like 'archiveUser'
      AND aqn.local_name not like 'person'
      AND anp.string_value like '%.%'
      AND anp.string_value NOT LIKE '%~%~%'
      AND anp.string_value NOT LIKE 'dashboard.xml'

All comments

cristinamr

Good morning!

You'll need to retrieve by content type, e.g.:

SELECT string_value AS document_name
FROM alf_node node
INNER JOIN alf_node_properties ON (id=node_id)
INNER JOIN alf_qname ON (qname_id=alf_qname.id)
WHERE ns_id IN
(SELECT ns.id FROM alf_namespace AS ns
WHERE ns.uri='http://www.alfresco.org/model/content/1.0')
AND local_name='name';

You can find more examples here and here.

Cheers,

Cris.

Muffex

Hello @cristinamr,

thanks for your answer, but this is exactly what I did (including the namespace).
But the results contain nodes that refer to wiki-pages, discussion-posts, etc.

Do you know how to filter the results even further to only include files that have actually been uploaded by users?

kind regards

cristinamr

Could you please share with us your tests?

Muffex

Hello again.

Thanks for your patience!

When running exactly the query you sent me the results contain nodes with names like:

Main_Page
links
several ftl-Files
page.component...
post-...
Comments
doclib
documentLibrary
imgpreview
...

The results thus also contain a plethora of nodes referring to "files" which have not been "uploaded" by users.
Therefore my question: "How to distinguish content-nodes that refer to wiki-pages, discussion-posts, uploaded files, etc.?"

kind regards

Muffex

Well, I finally figured "something" out.

I can retrieve most of the nodes that refer to "files". It's neither well optimized nor can I guarantee it will work.

But for my needs it is currently sufficient.

SELECT n.id AS nodeId, n.uuid AS fileId, n.audit_created AS created, n.audit_modified AS last_updated, anp.string_value AS name, n.audit_creator AS creator, CONCAT('http://alfresco.bas.uni-koblenz.de/share/page/document-details?nodeRef=',store.protocol,'://',store.identifier,'/',n.uuid) AS content
  FROM alf_node AS n
    JOIN alf_qname AS q on (n.type_qname_id = q.id AND q.local_name='content')
    JOIN alf_store AS store ON (store.id = n.store_id)
    JOIN alf_node_properties AS anp on (n.id = anp.node_id)
    LEFT JOIN alf_content_data AS acd ON (anp.long_value = acd.id)
    JOIN alf_child_assoc AS aca ON (aca.child_node_id = n.id)
    JOIN alf_node AS nP ON (aca.parent_node_id = nP.id)
    JOIN alf_qname AS aqn ON (np.type_qname_id = aqn.id)
    WHERE anp.qname_id IN 
      (SELECT id FROM alf_qname
        WHERE local_name='name' 
          AND ns_id IN 
            (SELECT ns2.id FROM alf_namespace AS ns2 WHERE uri LIKE '%content%'))
      AND n.audit_creator NOT LIKE 'System'
      AND aqn.local_name not like 'archiveUser'
      AND aqn.local_name not like 'person'
      AND anp.string_value like '%.%'
      AND anp.string_value NOT LIKE '%~%~%'
      AND anp.string_value NOT LIKE 'dashboard.xml'