L. G.
Reputation: 9761
How can I use JOOQ to fetch parent/childs of a self referencing table recursively?
I would like to use JOOQ to fetch parents and childs of a self referencing table recursively.
The goal is to fetch directly in the POJO.
I did not find any example for this in JOOQ documentation or anywhere else but I tought it would be possible combining multiset and recursive JOOQ methods. https://www.jooq.org/doc/3.14/manual/sql-building/sql-statements/with-recursive-clause/ https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/multiset-value-constructor/
Details of my problem and what I did so far (I simplified the table and objects here):
Self referencing table view_item:
- id: string
- parent_id: string
POJO:
public class ViewItem {
private final String id;
private final List<ViewItem> childrens;
public ViewItem(final String id) {
this.id = id;
this.childrens = new ArrayList<>();
}
public Integer getId() {
return id;
}
public void addChild(final ViewItem child) {
this.childrens.add(child);
}
public List<ViewItem> getChildrens() {
return childrens;
}
}
Select with JOOQ:
public List<ViewItem> getViewItems(final String viewId) throws IOException {
final CommonTableExpression<?> cte = DSL.name("t")
.as(DSL
.select(Tables.VIEW_ITEM.ID, Tables.VIEW_ITEM.PARENT_ID,
DSL.multiset(DSL.select().from(DSL.table("t")).join(Tables.VIEW_ITEM)
.on((DSL.field(DSL.name("t", "parent_id")))
.eq(Tables.VIEW_ITEM.ID)))
.as("childrens"))
.from(Tables.VIEW_ITEM).where(Tables.VIEW_ITEM.ID.eq(viewItemId)));
final List<ViewItem> items =
dslContext.withRecursive(cte).selectFrom(cte).fetch().into(ViewItem.class);
return items;
}
But receive below stack trace when running the query:
org.jooq.exception.DataAccessException: SQL [set @t = @@group_concat_max_len; set @@group_concat_max_len = 4294967295; with recursive `t2` as (select `tedcvsrepo`.`view_item`.`sort_order`, `tedcvsrepo`.`view_item`.`structure_id`, `tedcvsrepo`.`view_item`.`template_id`, `tedcvsrepo`.`view_item`.`parent_structure_id`, `tedcvsrepo`.`view_item`.`template`, `tedcvsrepo`.`view_item`.`draft`, `tedcvsrepo`.`view_item`.`draft_reason`, (select coalesce(json_merge_preserve('[]', concat('[', group_concat(json_array() separator ','), ']')), json_array()) from (select * from t2 join `tedcvsrepo`.`view_item` on `t2`.`parent_structure_id` = `tedcvsrepo`.`view_item`.`structure_id`) as `t`) as `childrens` from `tedcvsrepo`.`view_item` where (`tedcvsrepo`.`view_item`.`view_id` = ? and `tedcvsrepo`.`view_item`.`parent_structure_id` = ?) order by `tedcvsrepo`.`view_item`.`sort_order` asc) select `t2`.`sort_order`, `t2`.`structure_id`, `t2`.`template_id`, `t2`.`parent_structure_id`, `t2`.`template`, `t2`.`draft`, `t2`.`draft_reason`, `t2`.`childrens` from `t2`; set @@group_concat_max_len = @t;]; In recursive query block of Recursive Common Table Expression 't2', the recursive table must be referenced only once, and not in any subquery
at org.jooq_3.16.5.MYSQL.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:3102)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:670)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:354)
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:284)
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2843)
at eu.europa.ec.mdm.repository.ViewRepository.getViewMultiselect(ViewRepository.java:116)
at eu.europa.ec.mdm.repository.ViewRepository$$FastClassBySpringCGLIB$$99dace9d.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698)
at eu.europa.ec.mdm.repository.ViewRepository$$EnhancerBySpringCGLIB$$80c1116d.getViewMultiselect(<generated>)
at eu.europa.ec.mdm.repository.ViewRepositoryIt.getViewMultiselectTest(ViewRepositoryIt.java:33)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:95)
at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:91)
at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:60)
at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:98)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
Caused by: java.sql.SQLException: In recursive query block of Recursive Common Table Expression 't2', the recursive table must be referenced only once, and not in any subquery
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219)
at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4327)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:230)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:340)
... 83 more
Am I doing something wrong here or is there simply no way to achieve what I want to do with JOOQ?
Upvotes: 1
Views: 961
Answers (1)
Lukas Eder
Reputation: 220867
Your question is very similar, though not exactly the same as this one: Recursively generate JSON tree from hierarchical table in Postgres and jOOQ
That question doesn't have a very convenient jOOQ answer, and it's using only SQL/JSON on PostgreSQL, which is more powerful regarding recursion than MySQL. Using recursion and Multiset will be very tricky to do completely in jOOQ. Perhaps, just fetch the data in a flat format instead, and assemble it later on in a utility?
There's a feature request to help you do that out of the box in jOOQ, but as for jOOQ 3.17, it's not a priority yet, given the complexity: https://github.com/jOOQ/jOOQ/issues/12341
Upvotes: 0
Related Questions
- MySQL Recursive get all child from parent
- Need sql query to find the lowest level of values in a hierarchy
- MySql Recursive - get all children and parents from a given id
- Retrieve top-level parent MySQL
- Finding all parents in mysql table with single query (Recursive Query)
- jOOQ: Get Referenced Table from Foreign Key
- SQL - Showing all parents from recursive relationship
- Getting hierarchy data from self-referencing tables
- Query parents and children in self-referencing table
- Retrieve parent-child hierarchy from a self-referencing mysql table