retrieve description properties from all documents

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.

sharifu

I have many documents in my share. I want to retrieve all the text in description field from all my documents

How can I achieve this?

I found  the information is saved in alf_node_properties table under column string_value however how would i collect this information for each document as the title is a separate record to the description. Whats link them together?

I have the following query so far which pull up description for every record. But i only want description and title of certain documents not records. May also want depending on what directory they are under

SELECT acu.id, string_value AS 'desc'
FROM alf_content_url acu, alf_node_properties, alf_qname an
where acu.id =  node_id and qname_id = an.id 
and an.id = 29


SELECT acu.id, content_url, string_value AS 'title'
FROM alf_content_url acu, alf_node_properties, alf_qname an
where acu.id =  node_id and qname_id = an.id 
and an.id = 28
and string_value not like '%.ftl'
and string_value not like '%.xls'
and string_value not like '%.js'
and string_value not like '%.xml'
and string_value not like 'success_%'
and acu.id > 959
limit 0,1000000
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

So I have found a way to filter all results by type of document as I have made a custom model

SELECT *
FROM alf_node 
where type_qname_id    =295
limit 0,1000000
‍‍‍‍‍‍

The above query filters the results to my custom model

Using the results I execute the following query to get the title and description

SELECT *
FROM alf_node_properties
where node_id  = 1021
and qname_id IN (27,28)
‍‍‍‍‍‍

However this does not solve how I can get he information from specific folders and not the whole site.


The following does what I need it to do

$link = mysqli_connect('localhost', 'root', 'support','alfresco','3306','/opt/alfresco/mysql/tmp/mysql.sock');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
echo "Connected successfully\n";


$myFile = "properties.csv";
$fh = fopen($myFile, 'w');
fwrite( $fh,"title,dsec\n");


$sql = "SELECT * FROM alf_child_assoc
      where parent_node_id = 60985"; //id of folder to check and get data from al documents within currently set to contracts and bids

$folders1 = mysqli_query($link,$sql);




while( $rows = mysqli_fetch_assoc($folders1) ){
   if( $rows['type_qname_id'] == 33 ){
      recursive($link,$rows['child_node_id'],$fh);
   }
   //echo $rows['child_node_name']."\n";
   getProperties($link,$rows['id'],$fh);
}


function recursive($link,$node_id,$fh){
   
   $sql = "SELECT * FROM alf_child_assoc
   where parent_node_id = $node_id";
   
   $folders2 = mysqli_query($link,$sql);
   
   
   while( $rows = mysqli_fetch_assoc($folders2) ){
      if( $rows['type_qname_id'] == 33 ){
         recursive($link,$rows['child_node_id'],$fh);
      }
      getProperties($link,$rows['id'],$fh);
   }
   
}


function getProperties($link,$id,$fh){
   $sql = "SELECT node_id, string_value
         FROM alf_node_properties
         where node_id  = ".$id."
         and string_value not like '%page.component%' 
         and qname_id IN (27,28)";
   
   $results = mysqli_query($link,$sql);
   $properties = array();

   while( $row = mysqli_fetch_assoc($results) ){
      
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if( strlen(trim($row['string_value']))&nbsp; < 1 ){
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$properties[] = "null";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}else{
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$properties[] = $row['string_value'];
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//echo $row['string_value']."\n";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if(mysqli_num_rows($results) == 1){
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$properties[] = "null";
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;if(isset($properties[0])){
&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;fwrite( $fh,$properties[0].",");
&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;fwrite( $fh,$properties[1]."\n");
&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;}
&nbsp;&nbsp;&nbsp;
}

mysqli_close($link);

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

billerby

What is the reason for not using the regular alfresco services and API:s? Querying the database directly is something that is not recommended.

Why not just issue a lucene search for all nodes inheriting from cm:content with the cm:titled aspect?

/Erik