AnsweredAssumed Answered

ORDER BY on custom properties added to model

Question asked by mcs130 on Sep 14, 2012
Latest reply on Sep 15, 2012 by 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&amp;wt=json&amp;fl=*%2Cscore&amp;rows=100&amp;df=TEXT&amp;start=0&amp;locale=en_US&amp;fq=%7B%21afts%7DAUTHORITY_FILTER_FROM_JSON&amp;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

Outcomes