During discussions with @Joanna it emerged that searches for items in an hierarchy didn’t necessarily find the “expected” keywords in the hierarchy and I think this is happening because of a “bug” as follows @sgospodarenko.
In a previous post I had suggested to @Joanna that searching Keywords should be simple with PL5 but she responded that was simply not the case because it didn’t seem to work correctly and I do not understand exactly what DxO think that they are doing because when I said it was simple to do the searching it was based on the database structure(s) available to execute such a search so for scenario 3 (“animals|mammals|bear|black bear”) we have
where it is clear that all the keywords are available for searching and the hierarchical keys can be recreated using the ‘ParentId’.
The ItemsKeywords structure is effectively the link back to the image(s) (‘Items’) using ‘ItemId’ that contain the keywords and a forward link from the ‘Items’ to their keywords using ‘KeywordId’.
But actually it isn’t and therein lies the reason why the searches don’t always work!
I tested the searching and in this particular test where “animals” is checked, “bear” is checked along with “black bear” but “mammals” is left unchecked and the searches return the following responses
Although a search would find “mammals” in ‘Keywords’ (Id=7) there is no entry in ‘ItemsKeywords’ to tie it back to an ‘Items’ image (no ‘KeywordId’ = 7 exists)! I believe the problem is as “subtle” as that the ‘ItemsKeywords’ structure is misnamed it should be ‘ItemsKeywordsSelected’. i.e. it does not contain all the keywords associated with an image but only those that have the check box marked.
So for scenario 3 the hierarchy is re-established for display and output using the ‘Keywords’ structure “black bear” (9) < “bear” (8) < “mammals” (7) < “animals” (6) and the qualifying data for the “selected” (checked) items comes from ‘ItemsKeywords’ where Image 3 is related to ‘Keywords’ entry 9 (“black bear”), 8 (“bear”) and 6 (“animals”) but NOT 7 (“mammals”), hence 7 (“mammals” is not found in a search).
Hence, whenever a search is made for a keyword that is not a “selected” it is not present in ‘ItemsKeywords’ and the search terminates as “NOT Found”.
The simplest way to have resolved this problem was to have an entry for every keyword for each image and carry a flag in the record that identifies that the item is “selected”, the searches would then work (I believe) and the checkboxes can be correctly reconstructed for display and the items included for output!
Either the structure is being “overloaded”, i.e. pressed into service for a purpose it was not intended (searching) or someone thought it could be used for both purposes but that is simply not true in the way that it is currently designed. Unfortunately getting from what is currently in the database to what is required needs migration software as well as a fix.
EDIT 01:- While explaining this issue for my wife at breakfast, we met when I was assigned to help a customer build their Mortgage and Investment systems and my “wife to be” was their Database Administrator, she pointed out that it would be good if all items were selected which does happen when “bear” in the hierarchy is selected but I feel that to be included in the search it is wrong to only include “selected” items!
Either the structure needs to have a new field added or it could be handled by creating another structure, the “real” ‘ItemsKeywords’, e.g. ‘ItemsKeywordsAll’ which would then be used as part of the search but the first time it is created it would need to be populated using the “Leaf” pointers from ‘Keywords’ to ‘ItemsKeywords’, arguably the only pointer currently “guaranteed” to exist and then create an entry for every keyword located using the 'ParentId’s in ‘Keywords’
