topic Cmis query response misses some matching results in Alfresco Forum

Cmis query response misses some matching results

fblanco — Thu, 21 Sep 2023 07:41:11 GMT

Hello,

We have an installation of Alfresco Community 7.2 and Solr 6 in which we have created our own models with specific types for the documents that are uploaded.

One of these types contains a field with the invoice number, of type text, which when used in CMIS searches does not return the correct result.

Specifically, filtering by field values that begin with '9562%' only returns documents with values '9562300xxx', but there are documents with values '9562301xxx' and '9562302xxx' that do not appear in the response.
The query always contains an IN_TREE in the filter, so it is always run against SOLR, never against DB.

This is the CMIS query we are executing:

SELECT * FROM ham:factura_cliente_ham WHERE IN_TREE('70b8de21-d8a3-48d9-af58-d4247d1c732c') AND (cmis:creationDate >= '2023-06-01T00:00:00.000Z' AND cmis:creationDate <= '2023-06-30T23:59:59.000Z') AND cxc:cxcNumFactura LIKE '9562%' ORDER BY cmis:lastModificationDate DESC

Changing the filter to '95623%' or '956230%' still returns only records with '9562300xxx'.

With the filter '9562301xxx' or '9562302xxx' it does return the documents that it had previously omitted.

To run the filter I have tried both "LIKE" and "CONTAINS" operators, and the result is the same.

I have also tried removing the "IN_TREE" operator and using the "LIKE" operator, so the query is executed against the DB, and then it does return all the matching records.

The problem is that when the query is executed against DB we do not have information on the total number of existing records, losing the possibility of paging, which is essential in our case.

We've done a couple of total reindexes but nothing has changed.

Any suggestion will be welcome.

Thanks & Regards

Francesc Blanco

Answers can be in english or spanish.

Re: Cmis query response misses some matching results

angelborroy — Thu, 21 Sep 2023 10:03:39 GMT

How is defined the property cxc:cxcNumFactura in the Content Model?

Re: Cmis query response misses some matching results

fblanco — Thu, 21 Sep 2023 10:13:59 GMT

Hi Angel,

This is the XML property definition in the Content Model:

<property name="cxc:cxcNumFactura">
   <title>Número Factura</title>
   <type>d:text</type>
   <mandatory enforced="true">true</mandatory>
   <default>-</default>
   <index enabled="true">
      <atomic>true</atomic>
      <stored>false</stored>
      <tokenised>both</tokenised>
   </index>
</property>

Thanks!

Re: Cmis query response misses some matching results

angelborroy — Thu, 21 Sep 2023 13:35:39 GMT

Tokenized BOTH doesn't support that kind of queries.

You need to change to FALSE.

This requires re-indexation.

Re: Cmis query response misses some matching results

fblanco — Fri, 22 Sep 2023 06:43:59 GMT

We have changed Tokenised property to FALSE in all the properties of all our custom models, and full re-indexation done, but the behavior don't change.

What kind of queries doesn't support tokenised BOTH? Is for the operators or for the sentence?

Thanks

Re: Cmis query response misses some matching results

fblanco — Thu, 28 Sep 2023 08:58:48 GMT

Hello,
Any other suggestions as to why filtering on cxc:cxcNumFactura LIKE '9562%' does not return documents where the value of cxc:cxcNumFactura is 9562301xxx or 9562302xxx, but filtering on cxc:cxcNumFactura LIKE '9562301%' does return matching documents?
Do you need any extra information?
Thank you so much