To fetch the result, and search functionality, I took the approach of writing SQL Function and use it in the spring boot backend, using native query. To do that I have created a function as shown below:
I have created this function:
CREATE OR REPLACE FUNCTION search_doctors(
query TEXT,
min_years_of_exp INTEGER DEFAULT NULL,
min_rating DOUBLE PRECISION DEFAULT NULL,
consultation_type TEXT DEFAULT NULL,
day_of_week INTEGER DEFAULT NULL,
search_time TIME DEFAULT NULL,
specialization_ids bigint[] DEFAULT NULL,
latitude DOUBLE PRECISION DEFAULT NULL,
longitude DOUBLE PRECISION DEFAULT NULL,
radius DOUBLE PRECISION DEFAULT NULL,
page_num INTEGER DEFAULT NULL,
page_size INTEGER DEFAULT NULL
)
RETURNS refcursor
LANGUAGE plpgsql
AS $$
DECLARE
result_set refcursor;
BEGIN
OPEN result_set FOR
SELECT DISTINCT d.* FROM hc_doctor d
JOIN hc_doctor_search ds ON d.id = ds.doctor_id
LEFT JOIN hc_review r ON d.id = r.doctor_id
JOIN hc_availability a ON d.id = a.doctor_id
JOIN hc_doctor_specialization dspl ON d.id = dspl.doctor_id
WHERE ds.search_vector @@ to_tsquery(query)
AND (min_years_of_exp IS NULL OR d.years_of_experience >= min_years_of_exp)
AND (min_rating IS NULL OR r.rating >= min_rating OR r.rating IS NULL)
AND (consultation_type IS NULL OR a.consultation_type = consultation_type)
AND a.is_active = TRUE
AND (day_of_week IS NULL OR a.day_of_week = day_of_week)
AND (search_time IS NULL OR (a.start_time <= search_time AND a.end_time >= search_time))
AND (specialization_ids IS NULL OR dspl.specialization_id = ANY(specialization_ids))
AND (latitude IS NULL OR longitude IS NULL OR radius IS NULL OR earth_distance(ll_to_earth(latitude, longitude), ll_to_earth(d.latitude, d.longitude)) < radius)
LIMIT COALESCE(page_size, 10) OFFSET COALESCE((page_num - 1) * page_size, 0);
RETURN result_set;
END;
$$;
And repository:
@Query(value = "SELECT * FROM public.search_doctors(?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12) AS result_set",
nativeQuery = true)
List<Doctor> searchDoctorsAdvanced(String query, Integer minYrsOfExp, Double minRating,
String consultationType, Integer dayOfWeek, Time searchTime,
Long[] specializationIds, Double latitude, Double longitude,
Double radius, Integer pageNum, Integer pageSize);
And using this query in service impl:
@Override
public Page<Doctor> searchDoctorsAdvanced(String query, List<Long> specializationIds, Double latitude,
Double longitude, Double radius, Double minRating,
Integer minYrsOfExp, String consultationType, Integer dayOfWeek,
String searchTime, Pageable pageable) {
// Convert specializationIds to array and handle nulls
Long[] specializationIdsArray = specializationIds != null ? specializationIds.toArray(new Long[0]) : new Long[0];
// Convert searchTime to Time and handle nulls
Time timeParam = null;
if (searchTime != null && !searchTime.trim().isEmpty()) {
timeParam = Time.valueOf(searchTime);
}
// Extract pagination parameters
int pageNum = pageable.getPageNumber() + 1; // Adjust for 0-based page index
int pageSize = pageable.getPageSize();
// Call the search_doctors function
List<Doctor> doctors = doctorSearchRepository.searchDoctorsAdvanced(query, minYrsOfExp, minRating,
consultationType, dayOfWeek, timeParam, specializationIdsArray,
latitude, longitude, radius, pageNum, pageSize);
// Call the count_search_doctors function for total count
long totalCount = doctorSearchRepository.countDoctors(query, minYrsOfExp, minRating,
consultationType, dayOfWeek, timeParam, specializationIdsArray,
latitude, longitude, radius);
// Return a Page object with the results
return new PageImpl<>(doctors, pageable, totalCount);
}
Is it because I'm passing Array of Long, and the function accepts Array of BigInt, and db is Postgres.
What approach should I take to overcome this issue?
Log:
2023-12-28 17:59:32.377 DEBUG 48397 --- [nio-8082-exec-1] org.hibernate.SQL : SELECT * FROM public.search_doctors(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AS result_set
Hibernate: SELECT * FROM public.search_doctors(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AS result_set
2023-12-28 17:59:32.378 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [priya]
2023-12-28 17:59:32.378 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [INTEGER] - [null]
2023-12-28 17:59:32.378 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [DOUBLE] - [null]
2023-12-28 17:59:32.378 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [VARCHAR] - [null]
2023-12-28 17:59:32.378 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [5] as [INTEGER] - [null]
2023-12-28 17:59:32.378 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [6] as [TIME] - [null]
2023-12-28 17:59:32.378 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [7] as [VARBINARY] - [[Ljava.lang.Integer;@f7b7734]
2023-12-28 17:59:32.379 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [8] as [DOUBLE] - [null]
2023-12-28 17:59:32.379 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [9] as [DOUBLE] - [null]
2023-12-28 17:59:32.379 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [10] as [DOUBLE] - [null]
2023-12-28 17:59:32.379 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [11] as [INTEGER] - [1]
2023-12-28 17:59:32.379 TRACE 48397 --- [nio-8082-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [12] as [INTEGER] - [20]
2023-12-28 17:59:32.391 WARN 48397 --- [nio-8082-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42883
2023-12-28 17:59:32.391 ERROR 48397 --- [nio-8082-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: function public.search_doctors(character varying, integer, double precision, character varying, integer, unknown, bytea, double precision, double precision, double precision, integer, integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 15
2023-12-28 17:59:32.396 INFO 48397 --- [nio-8082-exec-1] c.a.m.h.c.e.GlobalExceptionHandlers : could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Now, after several attempts and Google, I tried Explicit Type Casting:
@Query(value = "SELECT * FROM public.search_doctors(cast(?1 as text), cast(?2 as integer), cast(?3 as double precision), " +
"cast(?4 as text), cast(?5 as integer), cast(?6 as time), cast(?7 as bigint[]), cast(?8 as double precision), " +
"cast(?9 as double precision), cast(?10 as double precision), cast(?11 as integer), cast(?12 as integer)) AS result_set",
nativeQuery = true)
List<Doctor> searchDoctorsAdvanced(String query, Integer minYrsOfExp, Double minRating,
String consultationType, Integer dayOfWeek, Time searchTime,
Long[] specializationIds, Double latitude, Double longitude,
Double radius, Integer pageNum, Integer pageSize);
Now, error is something like this: ERROR: cannot cast type bytea to bigint[]
2023-12-28 18:41:03.545 DEBUG 51027 --- [nio-8082-exec-2] org.hibernate.SQL : SELECT * FROM public.search_doctors(cast(? as text), cast(? as integer), cast(? as double precision), cast(? as text), cast(? as integer), cast(? as time), cast(? as bigint[]), cast(? as double precision), cast(? as double precision), cast(? as double precision), cast(? as integer), cast(? as integer)) AS result_set
Hibernate: SELECT * FROM public.search_doctors(cast(? as text), cast(? as integer), cast(? as double precision), cast(? as text), cast(? as integer), cast(? as time), cast(? as bigint[]), cast(? as double precision), cast(? as double precision), cast(? as double precision), cast(? as integer), cast(? as integer)) AS result_set
2023-12-28 18:41:03.546 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [priya]
2023-12-28 18:41:03.546 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [INTEGER] - [null]
2023-12-28 18:41:03.547 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [DOUBLE] - [null]
2023-12-28 18:41:03.547 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [VARCHAR] - [null]
2023-12-28 18:41:03.547 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [5] as [INTEGER] - [null]
2023-12-28 18:41:03.547 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [6] as [TIME] - [null]
2023-12-28 18:41:03.547 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [7] as [VARBINARY] - [[Ljava.lang.Long;@77239f9]
2023-12-28 18:41:03.550 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [8] as [DOUBLE] - [null]
2023-12-28 18:41:03.550 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [9] as [DOUBLE] - [null]
2023-12-28 18:41:03.550 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [10] as [DOUBLE] - [null]
2023-12-28 18:41:03.550 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [11] as [INTEGER] - [1]
2023-12-28 18:41:03.550 TRACE 51027 --- [nio-8082-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [12] as [INTEGER] - [20]
2023-12-28 18:41:03.555 WARN 51027 --- [nio-8082-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42846
2023-12-28 18:41:03.555 ERROR 51027 --- [nio-8082-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: cannot cast type bytea to bigint[]
Position: 163
2023-12-28 18:41:03.563 INFO 51027 --- [nio-8082-exec-2] c.a.m.h.c.e.GlobalExceptionHandlers : could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet