Support paging and sorting for custom queries [DATAGRAPH-653] #1219
Labels
type: enhancement
A general enhancement
Milestone
Comments
|
Luanne Misquitta commented PR raised neo4j/neo4j-ogm#19 |
|
Yang commented Any update on this? |
|
Luanne Misquitta commented
|
|
Sharif El-Borgi commented
|
|
Luanne Misquitta commented
|
|
Sharif El-Borgi commented Hi Luanne, My apologies to ask again, this is quite an urgent problem for us. As we have an issue where loadAll on the session with Pagination doesnt calculate the counts correctly and of course in certain cases causes no results when we know logically there should be. But because the calculations are incorrect - this as you can imagine is quite an issue. Any news welcome ... Hope to hear from you Sharif |
|
Luanne Misquitta commented Hi Sharif, We've got a planning meeting next week- we'll update this issue post that. Thanks |
|
Luanne Misquitta commented Hi Sharif, We're targeting 4.1 GA for paging and sorting of custom queries |
|
John Deverall commented
public class PageHack {
public static <T> List<T> pageHack(List<T> dtoList, int pageNumber, int pageSize) {
// start position of the page within our collection is the page number * page size
int fromIndex = pageNumber * pageSize;
if (fromIndex >= dtoList.size()) {
return new ArrayList<T>();
}
int toIndex = fromIndex + pageSize;
if (toIndex >= dtoList.size()) {
toIndex = dtoList.size();
}
return dtoList.subList(fromIndex, toIndex);
}
}We use it in our controller classes to get a page worth of resources from the total collection along the lines of: List<MyResource> myResources = PageHack.pageHack(myResources, pageable.getPageNumber(), pageable.getPageSize());
Page<MyResource> page = new PageImpl<MyResource>(myResources, pageable, totalCollectionFromDatabase.size());Obviously there is a performance overhead to this but we're in a position where we can wear it in the meantime until this feature is complete. Hope this helps |
|
Luanne Misquitta commented Sorting and paging now supported for custom queries. If exact counts are required in pages, a countQuery must be supplied, otherwise, an estimated count is used |
|
Sivaprasad Sreenivasan commented
|
|
Pagination is working but ORDER BY $sortBy is not working I tried different types of queries but I couldn't find a way to sort the custom query dynamically. |
|
There are several ways to do this: @Test
void findAllByExampleWithSort(@Autowired PersonRepository repository) {
Example<PersonWithAllConstructor> example = Example.of(personExample(TEST_PERSON_SAMEVALUE));
List<PersonWithAllConstructor> persons = repository.findAll(example, Sort.by(Sort.Direction.DESC, "name"));
assertThat(persons).containsExactly(person2, person1);
}
@Test
void findAllByExampleWithPagination(@Autowired PersonRepository repository) {
Example<PersonWithAllConstructor> example = Example.of(personExample(TEST_PERSON_SAMEVALUE));
Iterable<PersonWithAllConstructor> persons = repository.findAll(example, PageRequest.of(1, 1, Sort.by("name")));
assertThat(persons).containsExactly(person2);
}Or @Query("MATCH (n:PersonWithAllConstructor{name::#{#part1 + #part2}}) return n :#{orderBy(#sort)}")
Optional<PersonWithAllConstructor> getOptionalPersonViaQueryWithSort(@Param("part1") String part1,
@Param("part2") String part2, Sort sort);
@Test
void loadOptionalPersonWithAllConstructorWithSpelParametersAndDynamicSort(@Autowired PersonRepository repository) {
Optional<PersonWithAllConstructor> person = repository
.getOptionalPersonViaQueryWithSort(TEST_PERSON1_NAME.substring(0, 2), TEST_PERSON1_NAME.substring(2), Sort.by("n.name").ascending());
assertThat(person).isPresent();
assertThat(person.get().getName()).isEqualTo(TEST_PERSON1_NAME);
}Main takeaway though: All derived (aka generated) queries will work with pagination and sorting out of the box. Nothing todo from your side. Anyway, I can't work much from your question without context. |
|
@michael-simons Second example works for me thank you. But now I can't use pagination with SpEL support. |
|
Isn't the documentation helpful? https://docs.spring.io/spring-data/neo4j/docs/6.0.5/reference/html/#custom-queries.spel |
|
I read this but I still get the same error. Just a brief example of my query. |
|
Same issue as @mhmtszr.
|
|
Please share your boot and SDN versions. |
Thanks @michael-simons
|
|
Please update to boot 2.4.4 (brings 6.0.7) which is required for the above
feature.
where-design-meets-art ***@***.***> schrieb am Mi. 7. Apr.
2021 um 17:07:
… Please share your boot and SDN versions.
Thanks @michael-simons <https://github.com/michael-simons>
[image: image]
<https://user-images.githubusercontent.com/81367957/113889577-08b2f300-97cc-11eb-9ede-d5091fcd32fb.png>
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
<#1219 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AAEAQL46KI7SB2IYFWS3OBTTHRYLLANCNFSM4X66JHXA>
.
|
Thanks @michael-simons
|
|
Your pageable must refer `p.name`, `name` is not enough. Cypher does not
“import” all properties from the `p` object into a global space, they must
be referred to explicit.
where-design-meets-art ***@***.***> schrieb am Mi. 7. Apr.
2021 um 19:37:
… Please update to boot 2.4.4 (brings 6.0.7) which is required for the above
feature. where-design-meets-art *@*.***> schrieb am Mi. 7. Apr. 2021 um
17:07:
… <#m_1867936364516869155_>
Please share your boot and SDN versions. Thanks @michael-simons
<https://github.com/michael-simons> https://github.com/michael-simons
[image: image]
https://user-images.githubusercontent.com/81367957/113889577-08b2f300-97cc-11eb-9ede-d5091fcd32fb.png
— You are receiving this because you were mentioned. Reply to this email
directly, view it on GitHub <#1219 (comment)
<#1219 (comment)>>,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAEAQL46KI7SB2IYFWS3OBTTHRYLLANCNFSM4X66JHXA
.
Thanks @michael-simons <https://github.com/michael-simons>
I did so and i got rid of the error above. I still cannot make it work
though...
{
"timestamp": "2021-04-07T17:35:14.770+00:00",
"status": 500,
"error": "Internal Server Error",
"trace": "org.springframework.dao.InvalidDataAccessResourceUsageException: Variable `name` not defined (line 1, column 131 (offset: 130))\n\"MATCH (p:Part {spaceName: $spaceName}) WHERE $partId IS NULL OR toUpper(p.name) CONTAINS toUpper(trim($partId)) RETURN p ORDER BY name DESC SKIP $skip LIMIT $limit\"\n ^; Error code 'Neo.ClientError.Statement.SyntaxError'\n\tat org.springframework.data.neo4j.core.Neo4jPersistenceExceptionTranslator.translateImpl(Neo4jPersistenceExceptionTranslator.java:105)\n\tat org.springframework.data.neo4j.core.Neo4jPersistenceExceptionTranslator.translateExceptionIfPossible(Neo4jPersistenceExceptionTranslator.java:91)\n\tat org.springframework.data.neo4j.core.DefaultNeo4jClient.potentiallyConvertRuntimeException(DefaultNeo4jClient.java:174)\n\tat org.springframework.data.neo4j.core.DefaultNeo4jClient.access$400(DefaultNeo4jClient.java:55)\n\tat org.springframework.data.neo4j.core.DefaultNeo4jClient$DefaultRecordFetchSpec.all(DefaultNeo4jClient.java:315)\n\tat java.base/java.util.Optional.map(Optional.java:265)\n\tat org.springframework.data.neo4j.core.Neo4jTemplate$DefaultExecutableQuery.getResults(Neo4jTemplate.java:643)\n\tat org.springframework.data.neo4j.repository.query.Neo4jQueryExecution$DefaultQueryExecution.execute(Neo4jQueryExecution.java:51)\n\tat org.springframework.data.neo4j.repository.query.AbstractNeo4jQuery.execute(AbstractNeo4jQuery.java:85)\n\tat org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)\n\tat org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)\n\tat org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)\n\tat org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\n\tat org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\n\tat org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\n\tat org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)\n\tat com.sun.proxy.$Proxy190.findPartsBySpaceNameAndNameContaining(Unknown Source)\n\tat net.kprism.bomerang.api.SpaceController.findPartsInSpace(SpaceController.java:36)\n\tat net.kprism.bomerang.api.SpaceController$$FastClassBySpringCGLIB$$4b5cbb94.invoke(<generated>)\n\tat org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)\n\tat org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)\n\tat org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)\n\tat org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\n\tat org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)\n\tat org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)\n\tat net.kprism.bomerang.api.SpaceController$$EnhancerBySpringCGLIB$$33bbc59b.findPartsInSpace(<generated>)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)\n\tat org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)\n\tat org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)\n\tat org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)\n\tat org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)\n\tat org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)\n\tat org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)\n\tat org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:652)\n\tat org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:733)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\n\tat org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\n\tat org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\n\tat org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\n\tat org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)\n\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)\n\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)\n\tat org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)\n\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1707)\n\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)\n\tat org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)\n\tat java.base/java.lang.Thread.run(Thread.java:834)\n",
"message": "Variable `name` not defined (line 1, column 131 (offset: 130))\n\"MATCH (p:Part {spaceName: $spaceName}) WHERE $partId IS NULL OR toUpper(p.name) CONTAINS toUpper(trim($partId)) RETURN p ORDER BY name DESC SKIP $skip LIMIT $limit\"\n ^; Error code 'Neo.ClientError.Statement.SyntaxError'",
"path": "/api/space/parts"
}
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
<#1219 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AAEAQL65MCNUG7T2SWWET5TTHSJ6TANCNFSM4X66JHXA>
.
|
|
@michael-simons tried with p.name also - brand new error attached below, still not working :( The Pageable payload looks correct: |
|
I cannot read the trace. Could you please post it as standard java stack trace? |
|
|
Hmm… This looks like it fails when executing the count query… Can you confirm this? For example commenting it out? |
|
Hello, @michael-simons This is the error that we receive after commenting the count query: |
|
That should not happen with a Slicing query as quoted here: @Query(
value = "MATCH (p:Part {spaceName: $spaceName}) " +
"WHERE $partId IS NULL OR toUpper(p.name) CONTAINS toUpper(trim($partId)) " +
"RETURN p :#{orderBy(#pageable)} SKIP $skip LIMIT $limit",
countQuery = "MATCH (p:Part {spaceName: $spaceName}) " +
"WHERE $partId IS NULL OR toUpper(p.name) CONTAINS toUpper(trim($partId)) " +
"RETURN count(*)"
)
Slice<Part> findPartsBySpaceNameAndNameContaining(String spaceName, @Nullable String partId, Pageable pageable);Can you by chance reduce your project to a reproducer? It's really hard to pick the details from the comments here, as much as I'd love to help. |
|
Hello @michael-simons. To try and get this working I dug a little deeper into the SDN code and found that when it creates the PreparedQuery for the count query it is including a |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Vince Bickers opened DATAGRAPH-653 and commented
Please see:
http://stackoverflow.com/questions/30624435/paging-and-sorting-in-spring-data-neo4j-4/30625554
Affects: 4.0 M1, 4.0 RC1
Issue Links:
("is depended on by")
Referenced from: commits 8b76138
6 votes, 13 watchers
The text was updated successfully, but these errors were encountered: