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
So I have found a way to filter all results by type of document as I have made a custom model
Using the results I execute the following query to get the title and description
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
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
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);
