topic Re: Sql quey to get only folder names in Alfresco Archive

Sql quey to get only folder names

arroju_srikanth — Fri, 06 May 2011 06:21:34 GMT

Hello AllIm generating the reports through pentahoi require to get only folder names list through sql query not the document namesCREATE ALGORITHM=UNDEFINED DEFINER=`alfresco`@`localhost` SQL SECURITY DEFINER VIEW `ZKT_VIEWSUBFOLDER` AS select `alf_node`.`id` AS `node_id`,`alf_node_properties`.`str

Re: Sql quey to get only folder names

mikeh — Fri, 06 May 2011 08:29:40 GMT

I strongly recommend you do NOT try to query the private data structures of the Alfresco database and instead use one of the many public APIs available.

Thanks,
Mike

Re: Sql quey to get only folder names

mrogers — Fri, 06 May 2011 08:41:23 GMT

While seconding Mike's advice above, I do note that you are using the wrong table for folder names which should be queried through the child assoc table.

Re: Sql quey to get only folder names

arroju_srikanth — Fri, 06 May 2011 08:52:23 GMT

Dear Mike,
I'm new to Alfresco Can you Show me sample Example how to call Alfresco Service in Pentaho Report
Groovy Script. now I'm able to get the folder names with this Query.


SELECT a.id AS nodeid, b.string_value AS foldername FROM alf_node a, alf_node_properties b WHERE a.type_qname_id =24 AND b.qname_id =27 AND a.store_id != 5  AND a.id = b.node_id
‍‍‍

but im also getting some of the nodes which are not actually folders in Alfresco
ex:Company Home,Data Dictionary,scripts etc.

Thanks,
Srikanth

Re: Sql quey to get only folder names

mrogers — Fri, 06 May 2011 09:07:23 GMT

Those are folders.

Re: Sql quey to get only folder names

arroju_srikanth — Fri, 06 May 2011 09:14:01 GMT

but those are not shown in document library of alfresco when i searched for folder Computer Home .i did not got any result.