SQL equivalent of XPATH

[amit_wmw]

Hello

Can anyone please suggest me SQL query equivalent of below XPATH query?

Query query = queryManager.createQuery("//app:company_home//element(*, cm:content) [jcr:contains(@cm:author, 'Amit') ]", Query.XPATH);‍

I can able to successfully search content with the help of above XPATH query but I am unable to execute my program using SQL statement as below.

Query query = queryManager.createQuery("select * from cm:content where jcr:path='//app:company_home' and @cm:author='Raj'", Query.SQL);‍

I tried with different combinations but manage to find only below mentioned error while trying to execute the program  :cry:

javax.jcr.query.InvalidQueryException: Query language sql is not supported
   at org.alfresco.jcr.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:79)
   at org.alfresco.sample.FirstJCRClient.main(FirstJCRClient.java:52)‍‍‍

Kindly suggest the correct SQL query.

Thanks
Amit.

[derek]

Hi,
There is no SQL query language implementation - despite the static option that suggests otherwise.  Using the XPath query with the

…//element…‍

would be horrendous (as I'm sure you've discovered) because it has to walk the entire hierarchy and look at the properties of every node do determine who authored a particular document.  Lucene should be used for this type of query.
Regards

[amit_wmw]

Hi Derek

Thanks for your suggestions.

Yes, Xpath is slow. Moreover I think there is no way in XPATH to query and retrieve nodes created between two time slots (i.e. say: to retrieve nodes created between 12.00 PM to 03.00 PM), as <= or >= does not works! While there seems to be a possibility for the same in Lucene using range queries.

I know how to use Lucene through Alfresco API(SearchService) but using pure JCR API how can we do it? Is there any basic example/sample for the same? Can anybody please share.

Thanks

Amit.