[6a3a178] | 1 | package finki.diplomska.tripplanner.repository.jpa;
|
---|
| 2 |
|
---|
| 3 | import finki.diplomska.tripplanner.models.Location;
|
---|
| 4 | import org.springframework.data.jpa.repository.JpaRepository;
|
---|
| 5 | import org.springframework.data.jpa.repository.Query;
|
---|
| 6 | import org.springframework.data.repository.query.Param;
|
---|
| 7 | import org.springframework.stereotype.Repository;
|
---|
| 8 |
|
---|
| 9 |
|
---|
| 10 | import java.util.List;
|
---|
| 11 |
|
---|
| 12 | @Repository
|
---|
| 13 | public 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);
|
---|
[eed0bf8] | 46 |
|
---|
| 47 | @Query(value = "SELECT * FROM locations AS l " +
|
---|
[188ee53] | 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);
|
---|
[ceaed42] | 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);
|
---|
[6a3a178] | 85 | }
|
---|