I have a database of about of a million documents that are searchable by fulltext and other fields. Lets say articles. Users can make some of them favourites. Now I want to let them search among their favourites, but also using other attributes and text. For example all favouited articles having the word “star” in them. First I added the user ids of all users who made an item their favorite into a searchable field, and that’s easy, but requires reindexing any item that any user makes their favourite, which seems like a waste of resources because you can’t update individual fields of an item in an index (the “who’s favorite is this item” column). I can also add an IN (a, b, c, …) where clause with all the user’s favourited item ids, but thats problematic when they have lots of them. Any alternative implementation ideas?
You can use a multi value attribute, which would contain the ids of the users that have favourited the document.
As an attribute it can be updated without having to update the whole document/index. You do need to reinclude all the current ids, can’t just directly add one I’d to the list. But getting the current list from the index is easy.
This ( the UPDATE command) works on both plain and RT indexes
Thanks Barry, that’s great!