How to distinguish different nodes of type "content".

Showing results for 
Search instead for 
Did you mean: 
Active Member II

Re: How to distinguish different nodes of type "content".

Jump to solution

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 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('',store.protocol,'://',store.identifier,'/',n.uuid) AS content
  FROM alf_node AS n
    JOIN alf_qname AS q on (n.type_qname_id = AND q.local_name='content')
    JOIN alf_store AS store ON ( = n.store_id)
    JOIN alf_node_properties AS anp on ( = anp.node_id)
    LEFT JOIN alf_content_data AS acd ON (anp.long_value =
    JOIN alf_child_assoc AS aca ON (aca.child_node_id =
    JOIN alf_node AS nP ON (aca.parent_node_id =
    JOIN alf_qname AS aqn ON (np.type_qname_id =
    WHERE anp.qname_id IN 
      (SELECT id FROM alf_qname
        WHERE local_name='name' 
          AND ns_id IN 
            (SELECT 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'