source: trip-planner/src/main/java/finki/diplomska/tripplanner/repository/jpa/JpaLocationRepository.java@ bdd6491

Last change on this file since bdd6491 was bdd6491, checked in by Ema <ema_spirova@…>, 3 years ago

pre final presentation
  • Property mode set to 100644
File size: 6.2 KB
Line 
1package finki.diplomska.tripplanner.repository.jpa;
2
3import finki.diplomska.tripplanner.models.Location;
4import org.springframework.data.jpa.repository.JpaRepository;
5import org.springframework.data.jpa.repository.Query;
6import org.springframework.data.repository.query.Param;
7import org.springframework.stereotype.Repository;
8
9
10import java.util.List;
11
12@Repository
13public interface JpaLocationRepository extends JpaRepository<Location, Long> {
14
15 @Query(value = "SELECT * FROM locations AS l " +
16 "LEFT JOIN recommended_companion AS rc ON l.id_location = rc.id_location " +
17 "LEFT JOIN companions AS com ON rc.id_companion = com.id_companion " +
18 "LEFT JOIN locations_belong lb ON l.id_location = lb.id_location " +
19 "LEFT JOIN categories AS cate ON lb.id_category = cate.id_category " +
20 "LEFT JOIN regions AS r" +
21 " ON l.id_region = r.id_region" +
22 " LEFT JOIN cities AS cit" +
23 " ON r.id_region = cit.id_region AND cit.id_city = l.id_city " +
24 "WHERE cit.city_name = :locName and com.type = :companion and cate.category_name in (:categories) " +
25 "GROUP BY l.id_location " +
26 "ORDER BY CASE l.priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END", nativeQuery = true)
27 List<Location> findLocationsFromCity(@Param("locName") String locName, @Param("companion") String companion, @Param("categories") List<String> categories);
28
29 @Query(value = "SELECT * FROM locations AS l " +
30 "LEFT JOIN recommended_companion AS rc ON l.id_location = rc.id_location " +
31 "LEFT JOIN companions AS com " +
32 "ON rc.id_companion = com.id_companion " +
33 "LEFT JOIN locations_belong lb " +
34 "ON l.id_location = lb.id_location " +
35 "LEFT JOIN categories AS cate " +
36 "ON lb.id_category = cate.id_category " +
37 "LEFT JOIN regions AS r " +
38 "ON l.id_region = r.id_region " +
39 "LEFT JOIN countries AS country " +
40 "ON r.id_country = country.id_country " +
41 "LEFT JOIN cities AS c " +
42 "ON r.id_region = c.id_region AND c.id_city = l.id_city" +
43 " WHERE country.country_name = :locName and com.type = :companion and cate.category_name in (:categories) AND r.region_name = :region " +
44 "GROUP BY l.id_location ORDER BY CASE l.priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END", nativeQuery = true)
45 List<Location> findLocationsFromCountry(@Param("locName") String ime, @Param("companion") String companion, @Param("region") String region, @Param("categories") List<String> categories);
46
47 @Query(value = "SELECT * FROM locations AS l " +
48 "LEFT JOIN recommended_companion AS rc " +
49 "ON l.id_location = rc.id_location " +
50 "LEFT JOIN companions AS companion " +
51 "ON rc.id_companion = companion.id_companion " +
52 "LEFT JOIN locations_belong lb " +
53 "ON l.id_location = lb.id_location " +
54 "LEFT JOIN categories AS category " +
55 "ON lb.id_category = category.id_category " +
56 "LEFT JOIN cities AS city " +
57 "ON city.id_city = l.id_city " +
58 "WHERE city.id_city = :cityId and companion.id_companion = :companionId and category.id_category IN (:categoryIds) " +
59 "GROUP BY l.id_location ORDER BY CASE l.priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END", nativeQuery = true)
60 List<Location> findLocationsFromCityForm(@Param("cityId") Long cityId, @Param("companionId") Long companionId, @Param("categoryIds") List<Long> categoryIds);
61
62 @Query(value="SELECT * FROM locations AS location " +
63 "LEFT JOIN recommended_companion AS rc " +
64 "ON location.id_location = rc.id_location " +
65 "LEFT JOIN companions AS companion " +
66 "ON rc.id_companion = companion.id_companion " +
67 "LEFT JOIN locations_belong lb " +
68 "ON location.id_location = lb.id_location " +
69 "LEFT JOIN categories AS category " +
70 "ON lb.id_category = category.id_category " +
71 "LEFT JOIN regions AS region " +
72 "ON location.id_region = region.id_region " +
73 "LEFT JOIN cities AS city " +
74 "ON region.id_region = city.id_region " +
75 "AND city.id_city = location.id_city " +
76 "WHERE region.id_region = :regionId AND companion.id_companion = :companionId AND category.id_category IN (:categoryIds) " +
77 "GROUP BY location.id_location ORDER BY CASE location.priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END", nativeQuery = true)
78 List<Location> findLocationsFromRegionForm(@Param("regionId") Long regionId, @Param("companionId") Long companionId, @Param("categoryIds") List<Long> categoryIds);
79
80 @Query(value = "SELECT * " +
81 "FROM locations AS l " +
82 "WHERE l.id_location IN " +
83 "(SELECT pl.id_location FROM planners_contain AS pl WHERE pl.id_planner = :plannerId)", nativeQuery = true)
84 List<Location> getAllLocationsForPlanner(@Param("plannerId") Long plannerId);
85
86
87 @Query(value = "SELECT l.id_location " +
88 "FROM locations AS l " +
89 "WHERE l.id_location IN " +
90 "(SELECT pl.id_location FROM planners_contain AS pl WHERE pl.id_planner = :plannerId)", nativeQuery = true)
91 List<Long> getAllLocationIdsForPlanner(@Param("plannerId") Long plannerId);
92
93 @Query(value = "SELECT *, if(l.id_city is NOT NULL, c.city_name, l.location_name) as result " +
94 "FROM locations AS l " +
95 "LEFT JOIN cities AS c " +
96 "ON l.id_city = c.id_city " +
97 "LEFT JOIN locations_belong AS lb " +
98 "ON l.id_location = lb.id_location " +
99 "WHERE lb.id_category=20", nativeQuery = true)
100 List<Location> getWeekendGetaways();
101
102 @Query(value = "SELECT *, if(l.id_city is NOT NULL, c.city_name, l.location_name) as result " +
103 "FROM locations AS l " +
104 "LEFT JOIN cities AS c " +
105 "ON l.id_city = c.id_city " +
106 "LEFT JOIN locations_belong AS lb " +
107 "ON l.id_location = lb.id_location " +
108 "WHERE lb.id_category=4", nativeQuery = true)
109 List<Location> getVillages();
110
111}
Note: See TracBrowser for help on using the repository browser.