Left Join with EntityViews and where on both sides. #1410

chrigiansen · 2021-12-22T20:50:02Z

chrigiansen
Dec 22, 2021

Hello

I am currently trying to implement a left join in which there are where clauses on both sides. In SQL I managed to get it to work as follows:
select * from game g left join bet b on b.game_id = g.id where g.tournament_id = 14001 and b.better_id is null or b.better_id = 1;

I figured, I should use a correlation mapping to map the subview, in this case the Bet into the Game.

What I am missing, is to have a query on that correlation mapper, that allows me to only join when a specific criteria is met.

As I am using a criteria builder that has the entity Game as its root element, I am unable to add a clause for Bet. Game does not have any knowledge of Bet in this context.

Thanks

Answered by chrigiansen

Dec 23, 2021

Thanks!

It seems to work as I expected it.

I had to change from EMBEDDING_VIEW() to VIEW_ROOT() as I would get an exception. I also introduced a parameter.
@Mapping("Bet[game = VIEW_ROOT() AND better.id = :betterId]")
Does this make sense to you?

Regards

View full answer

beikov · 2021-12-22T23:17:33Z

beikov
Dec 22, 2021
Maintainer

I suppose you are not used to the join condition semantics of the various join types as it seems that what you want is a simple query like this:

select * from game g left join bet b on b.game_id = g.id and b.better_id = 1 where g.tournament_id = 14001

For every row from game every row from bet that meets the condition, produces a result, hence you have for N rows of game with M rows of bet that match, a total of N * M rows. If there is no row from bet meeting the condition, a left join retains the row, whereas an inner join would filter out the row.

Since implicit joins in Blaze-Persistence are left joins by default, you can make use of the array expression syntax in entity views which translates to the join condition:

@EntityView(Game.class)
interface GameView {
    @Mapping("Bet[game = EMBEDDING_VIEW() AND betterId = 1]")
    BetView getBet();
}

@EntityView(Bet.class)
interface BetView {
...
}

0 replies

chrigiansen · 2021-12-23T00:40:03Z

chrigiansen
Dec 23, 2021
Author

Thanks!

It seems to work as I expected it.

I had to change from EMBEDDING_VIEW() to VIEW_ROOT() as I would get an exception. I also introduced a parameter.
@Mapping("Bet[game = VIEW_ROOT() AND better.id = :betterId]")
Does this make sense to you?

Regards

10 replies

beikov Dec 23, 2021
Maintainer

Ahh, for some reason I used _ instead of this as per: https://persistence.blazebit.com/documentation/1.6/core/manual/en_US/#entity-array-expressions

chrigiansen Dec 23, 2021
Author

You mean?

	@Mapping("Bet[_.game = VIEW() AND better.id = :betterId]")
	BetView getBet();

Doesn't work either:

Caused by: java.lang.IllegalArgumentException: There are error(s) in entity views!
Exception while resolving type for attribute bet[ch.nextbet.view.BettingView.getBet]:
java.lang.IllegalArgumentException: Attribute '_' not found on type 'Bet'
	at com.blazebit.persistence.parser.PathTargetResolvingExpressionVisitor.visit(PathTargetResolvingExpressionVisitor.java:244)
	at com.blazebit.persistence.parser.expression.PropertyExpression.accept(PropertyExpression.java:41)
	at com.blazebit.persistence.parser.PathTargetResolvingExpressionVisitor.visit(PathTargetResolvingExpressionVisitor.java:337)
	at com.blazebit.persistence.parser.expression.PathExpression.accept(PathExpression.java:85)
	at com.blazebit.persistence.view.impl.ScalarTargetResolvingExpressionVisitor.resolve(ScalarTargetResolvingExpressionVisitor.java:733)
	at com.blazebit.persistence.view.impl.ScalarTargetResolvingExpressionVisitor.visit(ScalarTargetResolvingExpressionVisitor.java:714)
	at com.blazebit.persistence.view.impl.ScalarTargetResolvingExpressionVisitor.visit(ScalarTargetResolvingExpressionVisitor.java:680)
	at com.blazebit.persistence.parser.predicate.EqPredicate.accept(EqPredicate.java:57)
	at com.blazebit.persistence.view.impl.ScalarTargetResolvingExpressionVisitor.visit(ScalarTargetResolvingExpressionVisitor.java:670)
	at com.blazebit.persistence.parser.predicate.CompoundPredicate.accept(CompoundPredicate.java:77)
	at com.blazebit.persistence.view.impl.ScalarTargetResolvingExpressionVisitor.visit(ScalarTargetResolvingExpressionVisitor.java:318)
	at com.blazebit.persistence.parser.expression.ArrayExpression.accept(ArrayExpression.java:45)
	at com.blazebit.persistence.parser.PathTargetResolvingExpressionVisitor.visit(PathTargetResolvingExpressionVisitor.java:337)
	at com.blazebit.persistence.parser.expression.PathExpression.accept(PathExpression.java:85)
	at com.blazebit.persistence.view.impl.metamodel.MetamodelBuildingContextImpl.getPossibleTargetTypes(MetamodelBuildingContextImpl.java:379)
	at com.blazebit.persistence.view.impl.metamodel.AttributeMapping.getPossibleTargetTypes(AttributeMapping.java:374)
	at com.blazebit.persistence.view.impl.metamodel.AbstractAttribute.<init>(AbstractAttribute.java:165)
	at com.blazebit.persistence.view.impl.metamodel.AbstractMethodAttribute.<init>(AbstractMethodAttribute.java:66)
	at com.blazebit.persistence.view.impl.metamodel.AbstractMethodSingularAttribute.<init>(AbstractMethodSingularAttribute.java:69)
	at com.blazebit.persistence.view.impl.metamodel.attribute.MappingMethodSingularAttribute.<init>(MappingMethodSingularAttribute.java:34)
	at com.blazebit.persistence.view.impl.metamodel.MethodAttributeMapping.getMethodAttribute(MethodAttributeMapping.java:795)
	at com.blazebit.persistence.view.impl.metamodel.ManagedViewTypeImpl.<init>(ManagedViewTypeImpl.java:319)
	at com.blazebit.persistence.view.impl.metamodel.ViewTypeImpl.<init>(ViewTypeImpl.java:51)
	at com.blazebit.persistence.view.impl.metamodel.ViewMappingImpl.getManagedViewType(ViewMappingImpl.java:890)
	at com.blazebit.persistence.view.impl.metamodel.MetamodelBuildingContextImpl.getManagedViewType(MetamodelBuildingContextImpl.java:234)
	at com.blazebit.persistence.view.impl.metamodel.ViewMetamodelImpl.<init>(ViewMetamodelImpl.java:88)
	at com.blazebit.persistence.view.impl.EntityViewManagerImpl.<init>(EntityViewManagerImpl.java:271)
	at com.blazebit.persistence.view.impl.EntityViewConfigurationImpl.createEntityViewManager(EntityViewConfigurationImpl.java:206)
	at com.blazebit.persistence.integration.quarkus.runtime.EntityViewRecorder.lambda$entityViewManagerSupplier$1(EntityViewRecorder.java:88)
	at com.blazebit.persistence.view.EntityViewManager_e7b03adab716ed2bac3c0e8627cad1f71a2d1b56_Synthetic_Bean.create(Unknown Source)
	at com.blazebit.persistence.view.EntityViewManager_e7b03adab716ed2bac3c0e8627cad1f71a2d1b56_Synthetic_Bean.create(Unknown Source)
	at io.quarkus.arc.impl.AbstractSharedContext.createInstanceHandle(AbstractSharedContext.java:96)
	at io.quarkus.arc.impl.AbstractSharedContext$1.get(AbstractSharedContext.java:29)
	at io.quarkus.arc.impl.AbstractSharedContext$1.get(AbstractSharedContext.java:26)
	at io.quarkus.arc.impl.LazyValue.get(LazyValue.java:26)
	at io.quarkus.arc.impl.ComputingCache.computeIfAbsent(ComputingCache.java:69)
	at io.quarkus.arc.impl.AbstractSharedContext.get(AbstractSharedContext.java:26)
	at com.blazebit.persistence.view.EntityViewManager_e7b03adab716ed2bac3c0e8627cad1f71a2d1b56_Synthetic_Bean.get(Unknown Source)
	at com.blazebit.persistence.view.EntityViewManager_e7b03adab716ed2bac3c0e8627cad1f71a2d1b56_Synthetic_Bean.get(Unknown Source)
	at ch.nextbet.repository.BetterRepository_Bean.create(Unknown Source)
	at ch.nextbet.repository.BetterRepository_Bean.create(Unknown Source)
	at io.quarkus.arc.impl.RequestContext.getIfActive(RequestContext.java:68)
	at io.quarkus.arc.impl.ClientProxies.getDelegate(ClientProxies.java:27)
	at ch.nextbet.repository.BetterRepository_ClientProxy.arc$delegate(Unknown Source)
	at ch.nextbet.repository.BetterRepository_ClientProxy.getForPersonAndTournament(Unknown Source)
	at ch.nextbet.service.BetService.getBetsForBetter(BetService.java:22)
	at ch.nextbet.service.BetService_ClientProxy.getBetsForBetter(Unknown Source)
	at ch.nextbet.resource.BetResource.getBetsForBetter(BetResource.java:33)
	at ch.nextbet.resource.BetResource_Subclass.getBetsForBetter$$superforward1(Unknown Source)
	at ch.nextbet.resource.BetResource_Subclass$$function$$1.apply(Unknown Source)
	at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:54)
	at io.quarkus.security.runtime.interceptor.SecurityHandler.handle(SecurityHandler.java:47)
	at io.quarkus.security.runtime.interceptor.AuthenticatedInterceptor.intercept(AuthenticatedInterceptor.java:29)
	at io.quarkus.security.runtime.interceptor.AuthenticatedInterceptor_Bean.intercept(Unknown Source)
	at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:41)
	at io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:41)
	at io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:32)
	at ch.nextbet.resource.BetResource_Subclass.getBetsForBetter(Unknown Source)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:170)
	at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:130)
	at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:660)
	at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:524)
	at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget$2(ResourceMethodInvoker.java:474)
	at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364)
	at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:476)
	at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:434)
	at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:408)
	at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:69)
	at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:492)
	at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke$4(SynchronousDispatcher.java:261)
	at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(SynchronousDispatcher.java:161)
	at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364)
	at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:164)
	at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:247)
	at io.quarkus.resteasy.runtime.standalone.RequestDispatcher.service(RequestDispatcher.java:73)
	at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler.dispatch(VertxRequestHandler.java:151)
	at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler$1.run(VertxRequestHandler.java:91)
	at io.quarkus.vertx.core.runtime.VertxCoreRecorder$13.runWith(VertxCoreRecorder.java:543)
	at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2449)
	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1478)
	at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
	at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:833)

	at com.blazebit.persistence.view.impl.EntityViewManagerImpl.<init>(EntityViewManagerImpl.java:285)
	at com.blazebit.persistence.view.impl.EntityViewConfigurationImpl.createEntityViewManager(EntityViewConfigurationImpl.java:206)
	at com.blazebit.persistence.integration.quarkus.runtime.EntityViewRecorder.lambda$entityViewManagerSupplier$1(EntityViewRecorder.java:88)
	at com.blazebit.persistence.view.EntityViewManager_e7b03adab716ed2bac3c0e8627cad1f71a2d1b56_Synthetic_Bean.create(Unknown Source)
	at com.blazebit.persistence.view.EntityViewManager_e7b03adab716ed2bac3c0e8627cad1f71a2d1b56_Synthetic_Bean.create(Unknown Source)
	at io.quarkus.arc.impl.AbstractSharedContext.createInstanceHandle(AbstractSharedContext.java:96)
	at io.quarkus.arc.impl.AbstractSharedContext$1.get(AbstractSharedContext.java:29)
	at io.quarkus.arc.impl.AbstractSharedContext$1.get(AbstractSharedContext.java:26)
	at io.quarkus.arc.impl.LazyValue.get(LazyValue.java:26)
	at io.quarkus.arc.impl.ComputingCache.computeIfAbsent(ComputingCache.java:69)
	at io.quarkus.arc.impl.AbstractSharedContext.get(AbstractSharedContext.java:26)
	at com.blazebit.persistence.view.EntityViewManager_e7b03adab716ed2bac3c0e8627cad1f71a2d1b56_Synthetic_Bean.get(Unknown Source)
	at com.blazebit.persistence.view.EntityViewManager_e7b03adab716ed2bac3c0e8627cad1f71a2d1b56_Synthetic_Bean.get(Unknown Source)
	... 49 more

beikov Dec 23, 2021
Maintainer

Well, that sucks. Can you try to rename the field in the entity to e.g. theGame for test purposes?

chrigiansen Dec 23, 2021
Author

Thanks, this works as expected. Do you plan on fixing this issue, or should I rename all the entity reference fields? Having the field name equal to the entity name seems quite common.

beikov Dec 23, 2021
Maintainer

Yeah I will fix this. This will just be a temporary workaround: #1411

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Left Join with EntityViews and where on both sides. #1410

{{title}}

Replies: 2 comments 10 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

Select a reply

Left Join with EntityViews and where on both sides. #1410

chrigiansen Dec 22, 2021

Replies: 2 comments · 10 replies

beikov Dec 22, 2021 Maintainer

chrigiansen Dec 23, 2021 Author

beikov Dec 23, 2021 Maintainer

chrigiansen Dec 23, 2021 Author

beikov Dec 23, 2021 Maintainer

chrigiansen Dec 23, 2021 Author

beikov Dec 23, 2021 Maintainer

chrigiansen
Dec 22, 2021

Replies: 2 comments 10 replies

beikov
Dec 22, 2021
Maintainer

chrigiansen
Dec 23, 2021
Author

beikov Dec 23, 2021
Maintainer

chrigiansen Dec 23, 2021
Author

beikov Dec 23, 2021
Maintainer

chrigiansen Dec 23, 2021
Author

beikov Dec 23, 2021
Maintainer