Using FUNCTION in datamanager query

Support
#1

Hi,

Can we use functions created in database in datamanager also i want to add addtional values in the function which is not part of query as i have added but getting parameter not found.


casesesDl.setQuery("SELECT e FROM User e " +                               
                                " where(a.id IS NULL  "+
                                " and calculate_haversine_distance(e.latitude,e.longitude,:lat2,:long2) = :casesDistance " +
                                 ");
                        casesesDl.setParameter("lat2", casesBranchidMapping.getLatitude());
                        casesesDl.setParameter("long2", casesBranchidMapping.getLongitude());
                        casesesDl.setParameter("casesDistance", casesDistance);
                        casesesDl.setSort(Sort.by(Sort.Direction.DESC,"createdOn"));
                        casesesDl.load();
                        
                        calculate_haversine_distance - > This is the function present in database calculating the distance

Caused by: io.jmix.core.DevelopmentException: Parameter 'long2' is not used in the query

#2

I had remodified the query still getting error as i am trying to case string to decimal

casesesDl.setQuery("SELECT e FROM Cases e " +
                                " LEFT JOIN UserCaseAllocation a ON e.caseId = a.caseId " +
                                " where (a.caseId IS NULL OR a.toUser in :userIdList) "+
                                " and lower(e.vertical) = :vertical "+
                                " and function('calculate_haversine_distance',CAST(e.latitude AS DECIMAL),CAST(e.longitude AS DECIMAL),CAST( " +casesBranchidMapping.getLatitude()+  " AS DECIMAL),CAST( " +casesBranchidMapping.getLongitude()+ " AS DECIMAL)) <= " + casesDistance +
                                " and e.deleteFlag = 'F' ");
                        casesesDl.setParameter("userIdList", userIdList);
                        casesesDl.setParameter("vertical", verticalComboBox.getValue().toLowerCase());
                        casesesDl.setSort(Sort.by(Sort.Direction.DESC,"createdOn"));
                        casesesDl.load();


Exception in All users selection:- [Ljava.lang.StackTraceElement;@7c9a6efa
io.jmix.data.impl.jpql.JpqlSyntaxException: Errors found for input jpql:[SELECT e FROM Cases e  LEFT JOIN UserCaseAllocation a ON e.caseId = a.caseId  where (a.caseId IS NULL OR a.toUser in :userIdList)  and lower(e.vertical) = :vertical  and function('calculate_haversine_distance',CAST(e.latitude AS DECIMAL),CAST(e.longitude AS DECIMAL),CAST( 28.6520165 AS DECIMAL),CAST( 77.1948144 AS DECIMAL)) <= 5 and e.deleteFlag = 'F']
CommonErrorNode [<unexpected: [@72,179:208=''calculate_haversine_distance'',<38>,1:179], resync=function('calculate_haversine_distance',CAST(e.latitude AS DECIMAL),CAST(e.longitude AS DECIMAL),CAST( 28.6520165 AS DECIMAL),CAST( 77.1948144 AS DECIMAL)) <= 5>]
#3

Hi!
Try replacing the CAST function call as here:
https://docs.jmix.io/jmix/data-access/jpql-extensions.html
image

1 Like