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.

diff --git a/lib-php/SearchDescription.php b/lib-php/SearchDescription.php
index fa622e62634c0d4c9c29c58bc70ba478f6012ca6..fcb87d433a606a114f9ae27f2dbcde1989cb34df 100644 (file)
--- 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)) {