AnsweredAssumed Answered

retrieve description properties from all documents

Question asked by sharifu on Sep 10, 2012
Latest reply on Sep 11, 2012 by 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) ){
      
      if( strlen(trim($row['string_value']))  < 1 ){
         
         $properties[] = "null";
      }else{
         $properties[] = $row['string_value'];
      }
      //echo $row['string_value']."\n";
      if(mysqli_num_rows($results) == 1){
         $properties[] = "null";
      }
      
   }   
   
   if(isset($properties[0])){
   
   fwrite( $fh,$properties[0].",");
   
   
   fwrite( $fh,$properties[1]."\n");
   
   }
   
}

mysqli_close($link);

Outcomes