Author aliases not always recognised when searching for books

[hughrun]

Describe the bug
Searching for a name should return books by author/s with that string in the name or alias fields. e.g. https://bookwyrm.social/search?q=刘慈欣

However this appears to be inconsistent. e.g. https://youtu.be/InCOD_6wBP0

I'm splitting this issue out from #3047 because this looks like a bug, whereas the inability to set a particular form of authors names to match the display language is really an enhancement request.

To Reproduce
Steps to reproduce the behavior:

1. Go to https://reads.netsphere.pub/search?q=레이코+시미즈
2. See search result
3. Go to https://reads.netsphere.pub/search?q=Reiko+Shimizu
4. See no results

Expected behavior
Both of these searches should return the same result

Screenshots
If applicable, add screenshots to help explain your problem.

Instance
reads.netsphere.pub

Additional context
See #3047

I assume this behaviour is determined by search_title_author in bookwyrm.book_search.py but I don't understand enough about the django search functionality to see where this might be going wrong.

---

[mouse-reeve]

The way title/author search works is that a hidden postgres function creates a weighted search vector that includes the title, author, subtitle, and series name it in the search_vector field in the database. This function is executed whenever a book or author is edited. A friend and I wrung ourselves absolutely dry figuring out how to write the SQL for this and as a result made the deeply questionable decision to allow the SQL to live in a random migration file: https://github.com/bookwyrm-social/bookwyrm/blob/main/bookwyrm/migrations/0077_auto_20210623_2155.py#L39. This is obviously a horrible place for it to live.

Author aliases aren't included in the search vector, and I think the first step to updating the SQL query would be removing it from that migration so that it can be edited in a remotely normal way. It may be that the best way to delete the vector is to create a migration that drops it from the database (as the reverse migration in 0077 does).

Once it lives in a relatively sane location, it would just be a matter of editing some confusing and complicated PSQL

[hughrun]

This sounds like a good idea. But it also begs the question: how come aliases seem to work sometimes in author searches?

[mouse-reeve]

I would bet that the author is listed in Chinese on some of the editions, and a different edition than the one that appeared in the search is being displayed. Once the search happens on the database level, there's some complicated logic that decided which edition of a work should be shown, and it can end up showing you the default edition rather than the one matched by postgres

[mouse-reeve]

Hm well that doesn't seem to be the case for the example you linked.

[dato]

and I think the first step to updating the SQL query would be removing it from that migration so that it can be edited in a remotely normal way

Yes, that would unblock any work that needs to touch search_vector... I've prepared a PR for it, #3134.