Random sort not working in views

Which server backend are you using, Solr, database or something else?

I need random DB sort for project I'm working on for Database backend, so I created this patch which works with either the standard random sort OR views_random_seed using the following patch: https://www.drupal.org/files/issues/views_random_seed_search_api.patch from here: https://www.drupal.org/project/views_random_seed/issues/2939507

views_random_seed is not required but the attached patch will only work on mysql. Hopefully this is a good starting point for getting this feature added to the module.

I have tested this patch (views sort using global: random field) and it works running core 8.5.5 and search_api 1.8, I also have search_api_sorts beta1 installed.

Thanks a lot for the patch! That's a good start. Several things still need to be addressed, though:

• We need Postgres and SQLite support before committing.
• We also need automatic tests to ensure this works, and keeps working, on all three DBMSs. (Admittedly, testing whether a sort is random does seem tricky, but we at least have to test it doesn't throw any exceptions. And by just checking the order several times, I think we can also find a way that is very improbable to yield a false negative.)
• DBMS-specific code should always live in the DatabaseCompatibility classes (\Drupal\search_api_db\DatabaseCompatibility\GenericDatabase et al.).

Patch #3 is still relevant on D8.7.10

patch does not work for drupal 8.8.1

I was able to apply the patch manually in core 8.8.6 and search_api 8.x-1.16. Working great.

Alright, took a stab at this, @drunken monkey. I don't have experience with Postgres and SQLite so maybe someone can help with that.

I also moved things to the DatabaseCompatibility classes (hope it's what you meant) and wrote a test.

Changed also the status to Feature request as it was simply something that was not covered, not a bug per se.

Ah, seems I missed the whole "partial" search configuration, corrected the patches. However, I do see the tests here run with SQLite so in the CI they might not work. Locally with Mysql it works.

Thanks a lot for your work on this, great job! Yes, moving this code to the DatabaseCompatibilityclasses was exactly what I meant.

I also verified that it works with MySQL, but supporting SQLite and Postgres, too, shouldn’t actually be too hard. Patch attached, please test/review.
(Also moved the $this->searchWithRandom() call to the region where partial search isn’t enabled. Why complicate it with that?)

Looks good to me. I see you added something to the Generic class, I believe that covers SQLLite. I see postgres also seem to use random() for the expression. So it's ok I guess? Test is green so clearly it works for SQLLite.

I'm not gonna RTBC as it's my initial patch, I guess someone else can take a look?

Tests passing for all three environments and you giving your OK is good enough for me. (Especially since no-one else wanted to review over the last month.)
So: committed.
Thanks a lot again, everyone!