Changeset 76712b2 for trip-planner/src/main/java/finki/diplomska/tripplanner/repository/jpa

Timestamp:

01/28/22 18:45:54 (2 years ago)

Author:

Ema <ema_spirova@…>

Branches:

master

Children:

6fe77af

Parents:

b738035

Message:

search all locations by city or region

Location:

trip-planner/src/main/java/finki/diplomska/tripplanner/repository/jpa

Files:

• JpaLocationRepository.java (modified) (1 diff)
• JpaRegionRepository.java (modified) (1 diff)

trip-planner/src/main/java/finki/diplomska/tripplanner/repository/jpa/JpaLocationRepository.java

@@ -109 +109 @@
     List<Location> getVillages();
 
-    @Query(value = "SELECT * FROM locations AS l WHERE l.id_city = :cityId", nativeQuery = true)
-    List<Location> getLocationsForCity(@Param("cityId") Long cityId);
+    @Query(value = "SELECT * FROM locations AS l " +
+            "LEFT JOIN cities AS c " +
+            "ON l.id_city = c.id_city " +
+            "LEFT JOIN regions AS r " +
+            "ON l.id_region = r.id_region " +
+            "WHERE c.city_name = :place OR r.region_name = :place", nativeQuery = true)
+    List<Location> getAllLocations(@Param("place") String place);
 }

trip-planner/src/main/java/finki/diplomska/tripplanner/repository/jpa/JpaRegionRepository.java

@@ -3 +3 @@
 import finki.diplomska.tripplanner.models.Region;
 import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.data.jpa.repository.Query;
 import org.springframework.stereotype.Repository;
+
+import java.util.List;
 
 @Repository
 public interface JpaRegionRepository extends JpaRepository<Region, Long> {
+
+    @Query(value="SELECT r.region_name " +
+            "FROM regions AS r " +
+            "UNION all " +
+            "SELECT c.city_name FROM cities AS c", nativeQuery = true)
+    List<String> getAllCitiesAndRegions();
 }