ORDER BY on custom properties added to model

[mcs130]

Hello,

Question re: querying back for Document instances when using a custom model XML file.

I have the section of our model XML which we have created and started the server with and then successfully uploaded documents… populating the properties defined:

 <types>
        <!– Enterprise-wide generic document type –>
        <type name="acme:doc">
            <title>ACME Document</title>
            <parent>cm:content</parent>
            <properties>
                <property name="acme:displayName">
                    <type>d:text</type>
                </property>
                <property name="acme:documentType">
                    <type>d:text</type>
                </property>
             …..
              …. 
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

We attempted a CMIS SQL search:

SELECT * FROM acme:doc  WHERE IN_FOLDER('workspace://SpacesStore/88a2b54d-913f-43f9-bc31-05508a6871c5') ORDER BY cmis:name DESC    - THIS WORKED FINE

We then attempted to do this…

SELECT * FROM acme:doc  WHERE IN_FOLDER('workspace://SpacesStore/88a2b54d-913f-43f9-bc31-05508a6871c5') ORDER BY acme:displayName DESC - THIS ERRORS WITH AN EXCEPTION   Note that in this case we are attempting to ORDER BY on one of the custom properties we added…(we made sure to change from cmis:document to acme:doc).  Removal of the ORDER BY results in it WORKING FINE again. 

I ran this in the Chemistry Workbench and from the Logs dialog, we see this (which correlates to message in the dialog that pops up)

08130425 Request failed 500 /solr/alfresco/cmis?q=SELECT+*+FROM+acme%3Adoc++WHERE+IN_FOLDER%28%27workspace%3A%2F%2FSpacesStore%2F88a2b54d-913f-43f9-bc31-05508a6871c5%27%29+ORDER+BY+acme%3AdisplayName+DESC&wt=json&fl=*%2Cscore&rows=100&df=TEXT&start=0&locale=en_US&fq=%7B%21afts%7DAUTHORITY_FILTER_FROM_JSON&fq=%7B%21afts%7DTENANT_FILTER_FROM_JSON‍

Then I realized that we had not specified the properties with the <index> element.  Poking around the other XML files, in particular the cmisModel.xml, I saw many of the cmis:* properties configured with the <index> element.  I then adjusted our custom model XML file below to follow that pattern.

   <types>
        <!– Enterprise-wide generic document type –>
        <type name="acme:doc">
            <title>ACME Document</title>
            <parent>cm:content</parent>

            <properties>
                <property name="acme:displayName">
                    <type>d:text</type>
                    <index enabled="true">
                       <tokenised>true</tokenised>
                    </index>
                </property>
                <property name="acme:documentType">
                    <type>d:text</type>
                    <index enabled="true">
                       <tokenised>true</tokenised>
                    </index>
                </property> 
             …..
              …. 
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I cleared out all but ONE existing folder from the repository and stopped and re-started alfresco and postgress. (I did leave the ONE folder since it belonged to another person working on the project)…   

I then added some new folders and documents and all looks OK…  HOWEVER…again the ORDER BY in the query still chokes and throws the same exception above if I am using any column that is NOT in the cmis:* namespace.  It will honor searching using those custom columns, but ORDERing the results with them is just not working.  Also - I tried using true and false in the <tokenised> element.  Appears to have no effect.

I did not do anything with database/solr settings or otherwise.  Just added the elements above, and restarted.

Any insight would be appreciated.

Thanks

[afaust]

Hello,

can you check the logs on the SOLR server for any error output? I'd assume a 500 response should also leave an error report in there somewhere.

Regards
Axel

[mcs130]

Here is what we are finding: there is in fact a SOLR error encountered attempting to use the ORDER BY involving the custom properties.  Is there something else we should have included in the XML file for configuration?

Thanks

Sep 13, 2012 9:23:35 PM org.apache.solr.common.SolrException log
  10462 SEVERE: org.apache.chemistry.opencmis.commons.exceptions.CmisInvalidArgumentException: Ordering is not support for acme:displayName
  10463 at org.alfresco.opencmis.search.CMISQueryParser.buildOrderings(CMISQueryParser.java:915)
  10464 at org.alfresco.opencmis.search.CMISQueryParser.parse(CMISQueryParser.java:154)
  10465 at org.alfresco.solr.AlfrescoSolrDataModel.parseCMISQueryToAlfrescoAbstractQuery(AlfrescoSolrDataModel.java:1038)
  10466 at org.alfresco.solr.query.CmisQParserPlugin$CmisQParser.parse(CmisQParserPlugin.java:88)
  10467 at org.apache.solr.search.QParser.getQuery(QParser.java:131)
  10468 at org.apache.solr.handler.component.QueryComponent.prepare(QueryComponent.java:89)
  10469 at org.apache.solr.handler.component.SearchHandler.handleRequestBody(SearchHandler.java:174)
  10470 at org.apache.solr.handler.RequestHandlerBase.handleRequest(RequestHandlerBase.java:131)
  10471 at org.apache.solr.core.SolrCore.execute(SolrCore.java:1316)
  10472 at org.apache.solr.servlet.SolrDispatchFilter.execute(SolrDispatchFilter.java:338)

[afaust]

Hello,

please note that text properties with tokenized = true are not orderable - those need to be set to either false or both.

Regards
Axel

[mcs130]

We will give that a try…I think we had used false before and had the same issue but I will try the 2 different settings…based on what we show in the XML  file content , I assume this should be the only adjustment.?

Thanks

[mcs130]

Axel

I used the value "both" instead of "false" and IT WORKS CORRECTLY now with our custom properties of type d:text.  Thank you again for your help with this.

Regards

Mark