topic Re: Query on cmis:name does not return all results in Alfresco Forum

Query on cmis:name does not return all results

Fabermundi2 — Thu, 10 Oct 2024 09:29:53 GMT

Hello,

I have a problem when doing a query regarding the document name.
When I do the following query to show all documents begining with "C11-4":

select * from cmis:document WHERE cmis:name like 'C11-4%' order by cmis:name desc

I get as first result the document with the name "C11-41000.pdf".
When changing the to show all files beginning with "C11-41"

select * from cmis:document WHERE cmis:name like 'C11-41%' order by cmis:name desc

I get the document "C11-41926" that was not shown with the first query.

Has anyone an idea with the first query does not return all results?

Thank you in advance for your answers.

Re: Query on cmis:name does not return all results

afaust — Fri, 11 Oct 2024 10:55:16 GMT

What is your query consistency mode (set in the request or in your search subsystem)? What type of search system are you using? What version of ACS / ASS (Search Services) are you using?

My first thought: This kind of difference in behaviour points to a SOLR / Search Services based query execution and tokenisation of the query term / name value not matching up. I have seen some similar cases where a number-based name fragment was not matched with a wildcard after the first digit following a token boundary (whitespace, separator, alpha-numeric change). Only with two or more digits before the wildcard would a match work. Ideally, this type of query should be executed against the database where tokenisation would not play a role, and the LIKE operation work exactly like a DB LIKE.

Re: Query on cmis:name does not return all results

schulto — Mon, 14 Oct 2024 08:20:45 GMT

Hello and thank you for your response.

I'm using Alfresco 7.1 with Solr 6 with Search Services 2.0.2.

To have a better use case I've created a folder with only the files

C11-40999.pdf
C11-41000.pdf
C11-41926.pdf

Then I run the query

select * from cmis:document WHERE IN_TREE('workspace://SpacesStore/c22b3fab-ef46-4cc5-8184-47e9e2fd8913') and cmis:name like 'C11-4%'

To get only the two files C11-40999.pdf and C11-41000.pdf as a result.

If I understand this right you think that the search engine groups the files and this could be an explanation for this unwanted result.

Could you explain me, how I can run the query against the database? I used the Node browser at the webinterface to run the query as "cmis-alfresco".

Re: Query on cmis:name does not return all results

Melody242 — Tue, 15 Oct 2024 04:14:35 GMT

@Fabermundi2 gm-socrateswrote:
Hello,
I have a problem when doing a query regarding the document name.
When I do the following query to show all documents begining with "C11-4":

select * from cmis:document WHERE cmis:name like 'C11-4%' order by cmis:name desc

I get as first result the document with the name "C11-41000.pdf".
When changing the to show all files beginning with "C11-41"

select * from cmis:document WHERE cmis:name like 'C11-41%' order by cmis:name desc

I get the document "C11-41926" that was not shown with the first query.

Has anyone an idea with the first query does not return all results?

Thank you in advance for your answers.

To retrieve all documents beginning with "C11-4" in a CMIS query, check if the cmis:name property is case-sensitive and modify the query accordingly. Use wildcard characters for flexibility. Ensure proper indexing for efficient querying.

Re: Query on cmis:name does not return all results

afaust — Wed, 16 Oct 2024 14:11:04 GMT

As long as you need to use IN_TREE, the query will not be able to be rewritten against the database. Only IN_FOLDER (direct parent condition) will work with a database query. For testing purposes, you could remove IN_TREE to switch to a database query as well.

Re: Query on cmis:name does not return all results

Fabermundi2 — Wed, 23 Oct 2024 13:48:53 GMT

Hello and sorry for the late reply.
I've changed the query to use "IN_FOLDER" like this:

select * from cmis:document WHERE IN_FOLDER('workspace://SpacesStore/c22b3fab-ef46-4cc5-8184-47e9e2fd8913') and cmis:name like 'C11-4%'

Unfortunately the result was no change. It only returns me two of the 3 documents.

Re: Query on cmis:name does not return all results

Fabermundi2 — Wed, 23 Oct 2024 13:52:23 GMT

Hello and sorry for the late reply.

- Case sensitivity should be irrelevant because all documents are in the same form and begin with C11-.

- I use a wildcard as my query contains "like 'C11-4%'

- The indexes seems to be good as for example exact searches work. In addition the cm:name is marked as tokenised "both".