Sql quey to get only folder names

[arroju_srikanth]

Hello All
Im  generating the reports through pentaho
i require to get only folder names list through sql query not the document names

CREATE ALGORITHM=UNDEFINED DEFINER=`alfresco`@`localhost` SQL SECURITY DEFINER VIEW `ZKT_VIEWSUBFOLDER` AS select `alf_node`.`id` AS `node_id`,`alf_node_properties`.`string_value` AS `sub_folder_name` from (`alf_node_properties` join `alf_node` on(((`alf_node_properties`.`node_id` = `alf_node`.`id`) and (`alf_node_properties`.`qname_id` = 280))));
‍‍‍

CREATE ALGORITHM=UNDEFINED DEFINER=`alfresco`@`localhost` SQL SECURITY DEFINER VIEW `ZKT_VIEWSUBFOLDER2` AS select `ZKT_VIEWSUBFOLDER`.`node_id` AS `node_id`,`alf_node_properties`.`string_value` AS `folder`,substring_index(substring_index(substring_index(`ZKT_VIEWSUBFOLDER`.`sub_folder_name`,'|',-(2)),',',-(1)),'|',1) AS `name` from (`ZKT_VIEWSUBFOLDER` join `alf_node_properties` on(((`alf_node_properties`.`node_id` = `ZKT_VIEWSUBFOLDER`.`node_id`) and (`alf_node_properties`.`qname_id` = 280))));
‍‍‍

but wth this query im also getting document names. and some names which doesn' t have .extension are also in the
result.
is there any other way of getting folder names in sql
Thanks,
Srikanth A

[mikeh]

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

[mrogers]

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.

[arroju_srikanth]

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

[mrogers]

Those are folders.

[arroju_srikanth]

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