topic retrieve description properties from all documents in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/retrieve-description-properties-from-all-documents/m-p/258717#M211847 <HTML><HEAD></HEAD><BODY><SPAN>I have many documents in my share. I want to retrieve all the text in description field from all my documents</SPAN><BR /><BR /><SPAN>How can I achieve this?</SPAN><BR /><BR /><SPAN>I found&nbsp; 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?</SPAN><BR /><BR /><SPAN>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</SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR />SELECT acu.id, string_value AS 'desc'<BR />FROM alf_content_url acu, alf_node_properties, alf_qname an<BR />where acu.id =&nbsp; node_id and qname_id = an.id <BR />and an.id = 29<BR /><BR /><BR />SELECT acu.id, content_url, string_value AS 'title'<BR />FROM alf_content_url acu, alf_node_properties, alf_qname an<BR />where acu.id =&nbsp; node_id and qname_id = an.id <BR />and an.id = 28<BR />and string_value not like '%.ftl'<BR />and string_value not like '%.xls'<BR />and string_value not like '%.js'<BR />and string_value not like '%.xml'<BR />and string_value not like 'success_%'<BR />and acu.id &gt; 959<BR />limit 0,1000000<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><SPAN> So I have found a way to filter all results by type of document as I have made a custom model</SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR />SELECT *<BR />FROM alf_node <BR />where type_qname_id&nbsp;&nbsp;&nbsp; =295<BR />limit 0,1000000<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN>The above query filters the results to my custom model</SPAN><BR /><BR /><SPAN>Using the results I execute the following query to get the title and description</SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />SELECT *<BR />FROM alf_node_properties<BR />where node_id&nbsp; = 1021<BR />and qname_id IN (27,28)<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><SPAN>However this does not solve how I can get he information from specific folders and not the whole site.</SPAN><BR /><BR /><BR /><SPAN>The following does what I need it to do <img id="smileyhappy" class="emoticon emoticon-smileyhappy" src="https://connect.hyland.com/i/smilies/16x16_smiley-happy.png" alt="Smiley Happy" title="Smiley Happy" /></SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />$link = mysqli_connect('localhost', 'root', 'support','alfresco','3306','/opt/alfresco/mysql/tmp/mysql.sock');<BR />if (!$link) {<BR />&nbsp;&nbsp;&nbsp;die('Could not connect: ' . mysql_error());<BR />}<BR />echo "Connected successfully\n";<BR /><BR /><BR />$myFile = "properties.csv";<BR />$fh = fopen($myFile, 'w');<BR />fwrite( $fh,"title,dsec\n");<BR /><BR /><BR />$sql = "SELECT * FROM alf_child_assoc<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where parent_node_id = 60985"; //id of folder to check and get data from al documents within currently set to contracts and bids<BR /><BR />$folders1 = mysqli_query($link,$sql);<BR /><BR /><BR /><BR /><BR />while( $rows = mysqli_fetch_assoc($folders1) ){<BR />&nbsp;&nbsp;&nbsp;if( $rows['type_qname_id'] == 33 ){<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;recursive($link,$rows['child_node_id'],$fh);<BR />&nbsp;&nbsp;&nbsp;}<BR />&nbsp;&nbsp;&nbsp;//echo $rows['child_node_name']."\n";<BR />&nbsp;&nbsp;&nbsp;getProperties($link,$rows['id'],$fh);<BR />}<BR /><BR /><BR />function recursive($link,$node_id,$fh){<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;$sql = "SELECT * FROM alf_child_assoc<BR />&nbsp;&nbsp;&nbsp;where parent_node_id = $node_id";<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;$folders2 = mysqli_query($link,$sql);<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;while( $rows = mysqli_fetch_assoc($folders2) ){<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if( $rows['type_qname_id'] == 33 ){<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;recursive($link,$rows['child_node_id'],$fh);<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;getProperties($link,$rows['id'],$fh);<BR />&nbsp;&nbsp;&nbsp;}<BR />&nbsp;&nbsp;&nbsp;<BR />}<BR /><BR /><BR />function getProperties($link,$id,$fh){<BR />&nbsp;&nbsp;&nbsp;$sql = "SELECT node_id, string_value<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM alf_node_properties<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where node_id&nbsp; = ".$id."<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and string_value not like '%page.component%' <BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and qname_id IN (27,28)";<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;$results = mysqli_query($link,$sql);<BR />&nbsp;&nbsp;&nbsp;$properties = array();<BR /><BR />&nbsp;&nbsp;&nbsp;while( $row = mysqli_fetch_assoc($results) ){<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if( strlen(trim($row['string_value']))&nbsp; &lt; 1 ){<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$properties[] = "null";<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}else{<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$properties[] = $row['string_value'];<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//echo $row['string_value']."\n";<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if(mysqli_num_rows($results) == 1){<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$properties[] = "null";<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;if(isset($properties[0])){<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;fwrite( $fh,$properties[0].",");<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;fwrite( $fh,$properties[1]."\n");<BR />&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;}<BR />&nbsp;&nbsp;&nbsp;<BR />}<BR /><BR />mysqli_close($link);<BR /><BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE></BODY></HTML> Mon, 10 Sep 2012 09:33:04 GMT sharifu 2012-09-10T09:33:04Z retrieve description properties from all documents https://connect.hyland.com/t5/alfresco-archive/retrieve-description-properties-from-all-documents/m-p/258717#M211847 I have many documents in my share. I want to retrieve all the text in description field from all my documentsHow can I achieve this?I found&nbsp; 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 Mon, 10 Sep 2012 09:33:04 GMT https://connect.hyland.com/t5/alfresco-archive/retrieve-description-properties-from-all-documents/m-p/258717#M211847 sharifu 2012-09-10T09:33:04Z Re: retrieve description properties from all documents https://connect.hyland.com/t5/alfresco-archive/retrieve-description-properties-from-all-documents/m-p/258718#M211848 <HTML><HEAD></HEAD><BODY><SPAN>What is the reason for not using the regular alfresco services and API:s? Querying the database directly is something that is not recommended. </SPAN><BR /><BR /><SPAN>Why not just issue a lucene search for all nodes inheriting from cm:content with the cm:titled aspect?</SPAN><BR /><BR /><SPAN>/Erik</SPAN></BODY></HTML> Tue, 11 Sep 2012 12:49:21 GMT https://connect.hyland.com/t5/alfresco-archive/retrieve-description-properties-from-all-documents/m-p/258718#M211848 billerby 2012-09-11T12:49:21Z Re: retrieve description properties from all documents https://connect.hyland.com/t5/alfresco-archive/retrieve-description-properties-from-all-documents/m-p/258719#M211849 <HTML><HEAD></HEAD><BODY><SPAN>I could find a query that displays the results that i wanted correctly. lucene is new to me</SPAN></BODY></HTML> Tue, 11 Sep 2012 13:04:49 GMT https://connect.hyland.com/t5/alfresco-archive/retrieve-description-properties-from-all-documents/m-p/258719#M211849 sharifu 2012-09-11T13:04:49Z