From 2275fe59ab959f1dc4e4c33fc8a3fc72afb4c602 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Thu, 28 Oct 2021 11:27:31 +0200
Subject: [PATCH 1/1] include unlisted places in ordering by housenumber

When ordering results by the fact that they have a housenumber,
also take cases into account where the housenumber is on the
place itself. This may happen when the search includes the name
of the place and the housenumber or for addr:place addresses
where the place is unlisted.
---
 lib-php/SearchDescription.php | 49 +++++++++++++++++++----------------
 1 file changed, 27 insertions(+), 22 deletions(-)

diff --git a/lib-php/SearchDescription.php b/lib-php/SearchDescription.php
index fa622e62..fcb87d43 100644
--- a/lib-php/SearchDescription.php
+++ b/lib-php/SearchDescription.php
@@ -581,32 +581,37 @@ class SearchDescription
 
         // Sort by existence of the requested house number but only if not
         // too many results are expected for the street, i.e. if the result
-        // will be narrowed down by an address. Remeber that with ordering
+        // will be narrowed down by an address. Remember that with ordering
         // every single result has to be checked.
         if ($this->sHouseNumber && ($this->bRareName || !empty($this->aAddress) || $this->sPostcode)) {
             $sHouseNumberRegex = '\\\\m'.$this->sHouseNumber.'\\\\M';
-            $aOrder[] = ' (';
-            $aOrder[0] .= 'EXISTS(';
-            $aOrder[0] .= '  SELECT place_id';
-            $aOrder[0] .= '  FROM placex';
-            $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';
-            $aOrder[0] .= "    AND housenumber ~* E'".$sHouseNumberRegex."'";
-            $aOrder[0] .= '  LIMIT 1';
-            $aOrder[0] .= ') ';
-            // also housenumbers from interpolation lines table are needed
-            if (preg_match('/[0-9]+/', $this->sHouseNumber)) {
-                $iHouseNumber = intval($this->sHouseNumber);
-                $aOrder[0] .= 'OR EXISTS(';
-                $aOrder[0] .= '  SELECT place_id ';
-                $aOrder[0] .= '  FROM location_property_osmline ';
-                $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';
-                $aOrder[0] .= '    AND startnumber is not NULL';
-                $aOrder[0] .= '    AND '.$iHouseNumber.'>=startnumber ';
-                $aOrder[0] .= '    AND '.$iHouseNumber.'<=endnumber ';
-                $aOrder[0] .= '  LIMIT 1';
-                $aOrder[0] .= ')';
+
+            // Housenumbers on streets and places.
+            $sChildHnr = 'SELECT * FROM placex WHERE parent_place_id = search_name.place_id';
+            $sChildHnr .= "    AND housenumber ~* E'".$sHouseNumberRegex."'";
+            // Interpolations on streets and places.
+            if (preg_match('/^[0-9]+$/', $this->sHouseNumber)) {
+                $sIpolHnr = 'SELECT * FROM location_property_osmline ';
+                $sIpolHnr .= 'WHERE parent_place_id = search_name.place_id ';
+                $sIpolHnr .= '  AND startnumber is not NULL';
+                $sIpolHnr .= '    AND '.$this->sHouseNumber.'>=startnumber ';
+                $sIpolHnr .= '    AND '.$this->sHouseNumber.'<=endnumber ';
+            } else {
+                $sIpolHnr = false;
+            }
+            // Housenumbers on the object iteself for unlisted places.
+            $sSelfHnr = 'SELECT * FROM placex WHERE place_id = search_name.place_id';
+            $sSelfHnr .= "    AND housenumber ~* E'".$sHouseNumberRegex."'";
+
+            $sSql = '(CASE WHEN address_rank = 30 THEN EXISTS('.$sSelfHnr.') ';
+            $sSql .= ' ELSE EXISTS('.$sChildHnr.') ';
+            if ($sIpolHnr) {
+                $sSql .= 'OR EXISTS('.$sIpolHnr.') ';
             }
-            $aOrder[0] .= ') DESC';
+            $sSql .= 'END) DESC';
+
+
+            $aOrder[] = $sSql;
         }
 
         if (!empty($this->aName)) {
-- 
2.39.5