Search/search indexing for created and lastUpdated

yelto

We are having trouble searching for objects by created and lastUpdated in 2.0, and those searches are important tools for review and QA workflows. How should the created/lastUpdated (and timestamp etc subfields) meta-metadata get indexed? Not under "_metadata" apparently, some sort of explicit mention in search_indexing.conf maybe? The sql queries generated in advanced search for these "meta-metadata" seem.. odd. so it might not be just an indexing issue. What is the 2.0 way of doing this?
Thanks.

monica

You should be able to search the 'created date/time' and last updated using the advanced search or search builder. You can also get this information from the change logs under manage > logs.
If you want to explicitly index these fields to show when you do a basic search, then it might work if you include ca_objects.created and ca_objects.lastModified, keep in mind these values are plain text and not date format. (eg: 11 April 2025 at 9:29:47)
https://manual.collectiveaccess.org/providence/user/dataModelling/primaryTables.html#special-intrinsics

seth

Creation and modification date/times are not indexed. The logs are searched directly. They work fine for every 2.0 system I've seen.

yelto

Thanks @monica and @seth. That's the thing, a query is generated against the index, even from an advanced search form with a single box for created date/time:

2025-05-27 10:25:16 - INFO --> [205::250240] {"query":"\n\t\t\t\t\tSELECT swi.index_id, swi.row_id, swi.boost, swi.field_index\n\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tswi.table_num = 57 AND sw.word = 'created2025-05-26'\n\t\t\t\t\t\t\n\t\t\t\t\t\t \n\t\t\t\t","params":[57,"created2025-05-26"]} 2025-05-27 10:25:16 - INFO --> [205::250240] {"time":0.0005,"numRows":0}

[Same thing for modified.] Note that there are some queries against the change_log tables, although on logged_table_num 94. On the other hand the target object histories report correct creation (and modification) times, so the change_log itself does not look suspect.

Maybe this is incidental and a red herring?

seth

The colon between "created" and the date is being stripped. Try running the search like this in the basic search:

created:2025-05-26

Does that work for you?

yelto

No, same behavior, the colon is also stripped before shipping the query to the db (same query against the sql search index). In case it is relevant, the insert into the search log does contain the colon. Also I've looked for custom metadata elements and access points named "created" and found none, so probably not a name collision.

Also even with the colon the query on the index would get no hits, as there are no "created:%" words at all to be had there. Would the presence of the colon somewhere upstream steer the search away from the index and towards the change log, since that appears to be the right target?

seth

Searches on the "created" and "modified" access points (as defined by the text before the colon) are treated specially. If there's no colon, there's no access point.

Please send me your search.conf file.

yelto

Here is the tiny diff vs the search.conf in 2.0.5:
'16c16'
'< max_indexing_buffer_size = 5000'
'---'
'> max_indexing_buffer_size = 500000'
'26c26'
'< search_sql_search_do_stemming = 1'
'---'
'> search_sql_search_do_stemming = 0'
Thanks

yelto

I've filed a bug report with a shallow fix suggestion as this problem impacts non-english-speaking providence installs
https://github.com/collectiveaccess/providence/issues/1723