Sorry if the following information runs a bit long.
The project revolves around a database storing information from documents created between the 14th and 15th centuries. The client has requested the ability to search between dates, according to a specific ruleset (maybe a bit convoluted but they make sense to me – I’m also an historian).
Search results are shown via a Data Table based on a View. The ruleset for handling dates goes something like this:
- dates are stored both as separate values for Year, Month and Day and as a single (calculated) value in the YYYY-MM-DD format
- the 3 elements may not always be present, historical documents being the way they are; if a given date has only the Month element (for instance) then the calculated YYYY-MM-DD will have ’empty’ components, such as ‘0000-12-00’ (to be defined yet)
- a search may be performed over any one of these fields – Year, Month Day – separately or combined
- the search form has two sets of Y-M-D fields, for starting and ending dates
- searching individual elements – for instance, all documents belonging to a specific year – is made from the first set of Y-M-D fields; using the second set of fields in this context returns an error
- a search between dates can only be performed with full dates with Y-M-D.
There is further logic to verify if the dates belong inside the upper and lower chronological limits and also, when searching between dates, if the ending date is higher than the starting date and other odd bits and pieces. This logic has only been designed and not yet written.
My problem is that, since searches are performed over all the fields in the search form, I find myself with a search beeing made over an unexisting field in the database, since each document has only a creation date. I could add a second date field to the View, but that would not make sense.
As far as I could see, the problem would be solved with a WHERE clause, to control the record set over which the remaining search would be performed. I’m able to use form fields as arguments of the WHERE clause – but they are also used as search arguments by themselves, negating the putative results of the clause.
I asked about a Javascript solution because I’m using it to handle the behavior of the ‘Submit’ button in the search form, before opening the Data Table with the results. This way I can change the contents of the date fields according to the ruleset. So I came up with the following idea:
- any search, with or without a date component, would always have a WHERE clause for searching between dates – most of the time between the starting and ending dates of the full document set
- these starting and ending dates are stored as the single record of an auxiliary table, related to the main document body; this way, each and every document is always related to a starting and ending date (a bit of information that is meant to be used only in the search backoffice and never shown to the end-users)
- if there are valid starting and ending dates supplied in the search form, these values are written to the auxiliary table
- the way I see it, this would allow any search to be performed with a temporal WHERE clause – either with the full starting and ending dates, or with more specific ones
Writing the date values to the auxiliary table would have to be made in the Javascript phase of the process, after pressing the ‘Submit’ button, before opening the Data Table with the WHERE clause.
I don’t know if I’m overthinking this or if there is a blindingly obvious solution that I’m missing. The existing interface isn’t public yet, so I have no URL to show.
Any help is welcome ??
Best regards
jpamado
