Repository: digitalfabrik/entitlementcard
Issue: Search loads very long on staging server

The deployed backend on the staging server needs very long to respond to a search query.
The local version is very fast (at least for me and @maxammann )

Changed files:
backend/src/main/kotlin/xyz/elitese/ehrenamtskarte/database/repos/AcceptingStoresRepository.kt

diff --git a/backend/src/main/kotlin/xyz/elitese/ehrenamtskarte/database/repos/AcceptingStoresRepository.kt b/backend/src/main/kotlin/xyz/elitese/ehrenamtskarte/database/repos/AcceptingStoresRepository.kt
index 32b19e41..17d9ee9f 100644
--- a/backend/src/main/kotlin/xyz/elitese/ehrenamtskarte/database/repos/AcceptingStoresRepository.kt
+++ b/backend/src/main/kotlin/xyz/elitese/ehrenamtskarte/database/repos/AcceptingStoresRepository.kt
@@ -8,8 +8,6 @@ import xyz.elitese.ehrenamtskarte.database.PhysicalStores
 
 object AcceptingStoresRepository {
 
-    fun findAll() = AcceptingStoreEntity.all()
-
     fun findByIds(ids: List<Int>) = AcceptingStoreEntity.find {
         AcceptingStores.id inList ids
     }
@@ -19,26 +17,38 @@ object AcceptingStoresRepository {
     // TODO Fulltext search is possible with tsvector in postgres: https://www.compose.com/articles/mastering-postgresql-tools-full-text-search-and-phrase-search/
     // TODO Probably not relevant right now
     fun findBySearch(searchText: String?, categoryIds: List<Int>?): SizedIterable<AcceptingStoreEntity> {
+        val categoryMatcher =
+            if (categoryIds == null)
+                Op.TRUE
+            else
+                Op.build { AcceptingStores.categoryId inList categoryIds }
+
+        val textMatcher =
+            if (searchText == null)
+                Op.TRUE
+            else
+                OrOp(
+                    listOf(
+                        AcceptingStores.name ilike "%$searchText%",
+                        AcceptingStores.description ilike "%$searchText%",
+                        Addresses.location ilike "%$searchText%",
+                        Addresses.postalCode ilike "%$searchText%",
+                        Addresses.street ilike "%$searchText%"
+                    )
+                )
 
-        val matchCategoryIds = if (categoryIds != null) Op.build { AcceptingStores.categoryId inList categoryIds } else Op.TRUE
-        val matchSearchText = if (searchText != null) {
-            val lowerCaseSearchText = searchText.toLowerCase()
-            Op.build {
-                ((AcceptingStores.name.lowerCase() like "%${lowerCaseSearchText}%") or
-                        (AcceptingStores.description.lowerCase() like "%${lowerCaseSearchText}%") or
-                        exists(PhysicalStores.select(
-                            PhysicalStores.storeId eq AcceptingStores.id and
-                                    exists(Addresses.select(
-                                        Addresses.id eq PhysicalStores.addressId and
-                                                (Addresses.location.lowerCase() like "%${lowerCaseSearchText}%") or
-                                                (Addresses.postalCode.lowerCase() like "%${lowerCaseSearchText}%") or
-                                                (Addresses.street.lowerCase() like "%${lowerCaseSearchText}%")
-                                    ))
-                        )))
-            }
-        } else Op.TRUE
-        return AcceptingStoreEntity.find {
-            matchSearchText.and(matchCategoryIds)
-        }
+        return (AcceptingStores leftJoin PhysicalStores leftJoin Addresses)
+            .slice(AcceptingStores.columns)
+            .select(categoryMatcher and textMatcher)
+            .let { AcceptingStoreEntity.wrapRows(it) }
     }
 }
+
+// Postgres' "like" operation uses case sensitive comparison by default.
+// Postgres has a builtin "ilike" operation which does case sensitive comparison.
+
+class InsensitiveLikeOp(expr1: Expression<*>, expr2: Expression<*>) : ComparisonOp(expr1, expr2, "ILIKE")
+
+infix fun <T : String?> Expression<T>.ilike(pattern: String): InsensitiveLikeOp =
+    InsensitiveLikeOp(this, stringParam(pattern))
+