Problem with selecting data before aspect applied to any file

[pogoma]

I have problem when trying to execute CMIS query like this:

SELECT * 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 exception:

org.apache.chemistry.opencmis.commons.exceptions.CmisRuntimeException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: syntax error at end of input
  Position: 983
### 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
  Position: 983
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at end of input
  Position: 983
‍‍‍‍‍‍‍‍‍‍‍

The aspect description is added to db after aspect is applied to first document. Is there any simple way to force Alfresco to add this data on custom model loading?

[kaynezhang]

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

[pogoma]

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?

[kaynezhang]

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.

[pogoma]

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
‍‍‍

[kaynezhang]

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
‍‍‍