topic Re: Problem with selecting data before aspect applied to any file in Alfresco Archive

Problem with selecting data before aspect applied to any file

pogoma — Mon, 24 Feb 2014 09:45:01 GMT

I have problem when trying to execute CMIS query like thisELECT * FROM namespace:aspectName WHERE namespace:lastUpdate<=1392983610113 ORDER BY namespace:lastUpdate ASC‍before aspect 'aspectName' is applied to any document. Result should be the empty set but Alfresco is throwing exceptionrg.a

Re: Problem with selecting data before aspect applied to any file

kaynezhang — Mon, 24 Feb 2014 14:58:48 GMT

In cmis query the FROM part must be the name of an object type or an join clause containing aspect name ,but not an aspect only

Re: Problem with selecting data before aspect applied to any file

pogoma — Mon, 24 Feb 2014 16:08:22 GMT

This query is working in general. I found just one case when its not work - aspect didn't applied to any file (aspect metadata aren't saved in DB yet). And my question is: is there any simple way to make this query works in this case?

Re: Problem with selecting data before aspect applied to any file

kaynezhang — Mon, 24 Feb 2014 17:08:42 GMT

Why not use join query like following


SELECT d.cmis:objectid,a.* FROM cmis:document as d join namespace:aspectName as a on d.cmis:objectid = a.cmis:objectid  WHERE a.namespace:lastUpdate<=1392983610113  ORDER BY a.namespace:lastUpdate ASC
‍‍‍

I think it has same effect like yours and will not cause any error.

Re: Problem with selecting data before aspect applied to any file

pogoma — Tue, 25 Feb 2014 13:10:00 GMT

Now i have message: "Unknown column/property cmisbjectid". I think that column set in my query is empty (query without "order by" is working) coz there is no aspect metadata in db before aspect is applied to any file.

// EDIT:
It should be: "objectId". Still have:


## Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: syntax error at end of input Pozycja: 1040 ### The error may involve alfresco.metadata.query.select_byDynamicQuery-Inline ### The error occurred while setting parameters ### Cause: org.postgresql.util.PSQLException: ERROR: syntax error at end of input Pozycja: 1040 ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at end of input Pozycja: 1040
‍‍‍

Re: Problem with selecting data before aspect applied to any file

kaynezhang — Wed, 26 Feb 2014 06:15:03 GMT

Yes property cmisbjectid should be like this cmisbjectId( Letter i should be capitalized),following query is tested in my envirement.


SELECT d.cmis:objectId,a.* FROM cmis:document as d join ca:aspectContent as a on d.cmis:objectId = a.cmis:objectId  WHERE a.ca:anotherDate<=1392983610113  ORDER BY a.ca:anotherDate ASC
‍‍‍