Support binding embeddables and associations using compound or foreign keys in CTE’s #571
Comments
|
I'm not sure what this is about, but I think |
|
I'll add a test case tomorrow, perhaps its fixed in the mean time. I saw the issue with a recursive CTE binding an |
|
For example: package com.blazebit.persistence.testsuite;
import com.blazebit.persistence.CTE;
import com.blazebit.persistence.testsuite.tx.TxVoidWork;
import org.junit.Before;
import org.junit.Test;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.ManyToOne;
import java.io.Serializable;
import java.util.List;
public class Issue571Test extends AbstractCoreTest {
@Override
protected Class<?>[] getEntityClasses() {
return new Class<?>[] { Cte.class, MyEntity.class };
}
@Before
public void setUp() throws Exception {
transactional(new TxVoidWork() {
@Override
public void work(EntityManager em) {
MyEntity myEntity = new MyEntity();
myEntity.setAttribute("attr");
em.persist(myEntity);
}
});
}
@Test
public void issue571Test() {
transactional(new TxVoidWork() {
@Override
public void work(EntityManager em) {
List<MyEntity> resultList = cbf.create(em, MyEntity.class)
.withRecursive(Cte.class)
.from(MyEntity.class, "a")
.bind("myEntity").select("a")
.bind("id").select("1")
.unionAll()
.from(MyEntity.class, "a")
.where("a.attribute").notIn().from(Cte.class, "b").select("b.myEntity.attribute").end()
.bind("myEntity").select("a")
.bind("id").select("1")
.end()
.from(Cte.class)
.select("myEntity")
.getResultList();
System.out.println(resultList);
}
});
}
@CTE
@Entity
@IdClass(Cte.CteIdClass.class)
public static class Cte {
@Id private Long id;
@Id @ManyToOne private MyEntity myEntity;
public MyEntity getMyEntity() {
return myEntity;
}
public void setMyEntity(MyEntity myEntity) {
this.myEntity = myEntity;
}
public static class CteIdClass implements Serializable {
Long id;
Long myEntity;
}
}
@Entity
public static class MyEntity {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String attribute;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getAttribute() {
return attribute;
}
public void setAttribute(String attribute) {
this.attribute = attribute;
}
}
}Throws: And: package com.blazebit.persistence.testsuite;
import com.blazebit.persistence.CTE;
import com.blazebit.persistence.testsuite.tx.TxVoidWork;
import org.junit.Before;
import org.junit.Test;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.ManyToOne;
import java.io.Serializable;
import java.util.List;
public class Issue571Test extends AbstractCoreTest {
@Override
protected Class<?>[] getEntityClasses() {
return new Class<?>[] { Cte.class, MyEntity.class };
}
@Before
public void setUp() throws Exception {
transactional(new TxVoidWork() {
@Override
public void work(EntityManager em) {
MyEntity myEntity = new MyEntity();
myEntity.setAttribute("attr");
em.persist(myEntity);
}
});
}
@Test
public void issue571Test() {
transactional(new TxVoidWork() {
@Override
public void work(EntityManager em) {
List<MyEntity> resultList = cbf.create(em, MyEntity.class)
.withRecursive(Cte.class)
.from(MyEntity.class, "a")
.bind("myEntity").select("a")
.bind("id").select("1")
.unionAll()
.from(MyEntity.class, "a")
.where("a.attribute").eq("bogus")
.bind("myEntity").select("a")
.bind("id").select("1")
.end()
.from(Cte.class)
.select("myEntity")
.getResultList();
System.out.println(resultList);
}
});
}
@CTE
@Entity
@IdClass(Cte.CteIdClass.class)
public static class Cte {
@Id private Long id;
@Id @ManyToOne private MyEntity myEntity;
public MyEntity getMyEntity() {
return myEntity;
}
public void setMyEntity(MyEntity myEntity) {
this.myEntity = myEntity;
}
public static class CteIdClass implements Serializable {
Long id;
Long myEntity;
}
}
@Entity
public static class MyEntity {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String attribute;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getAttribute() {
return attribute;
}
public void setAttribute(String attribute) {
this.attribute = attribute;
}
}
}Produces: with recursive Issue571Test$Cte(myEntity_id, id) AS( select
issue571te0_.id as col_0_0_,
1 as col_1_0_,
issue571te0_.id as id1_1_,
issue571te0_.attribute as attribut2_1_
from
Issue571Test$MyEntity issue571te0_
UNION
ALL select
issue571te0_.id as col_0_0_,
1 as col_1_0_,
issue571te0_.id as id1_1_,
issue571te0_.attribute as attribut2_1_
from
Issue571Test$MyEntity issue571te0_
where
issue571te0_.attribute=? ) select
issue571te1_.id as id1_1_,
issue571te1_.attribute as attribut2_1_
from
Issue571Test$Cte issue571te0_
inner join
Issue571Test$MyEntity issue571te1_
on issue571te0_.myEntity_id=issue571te1_.idWhere there are more fields projected than actually exist in the CTE. |
|
Also note the strange behaviour that occurs when you bind to the id value instead. For the latter example: public void issue571Test() {
transactional(new TxVoidWork() {
@Override
public void work(EntityManager em) {
List<MyEntity> resultList = cbf.create(em, MyEntity.class)
.withRecursive(Cte.class)
.from(MyEntity.class, "a")
.bind("myEntity").select("a.id")
.bind("id").select("1")
.unionAll()
.from(MyEntity.class, "a")
.where("a.attribute").eq("bogus")
.bind("myEntity").select("a.id")
.bind("id").select("1")
.end()
.from(Cte.class)
.select("myEntity")
.getResultList();
System.out.println(resultList);
}
});
}produces: with recursive Issue571Test$Cte(myEntity_id, id) AS( select
issue571te0_.id as col_0_0_,
1 as col_1_0_
from
Issue571Test$MyEntity issue571te0_
UNION
ALL select
issue571te0_.id as col_0_0_,
1 as col_1_0_
from
Issue571Test$MyEntity issue571te0_
where
issue571te0_.attribute=? ) select
issue571te1_.id as id1_1_,
issue571te1_.attribute as attribut2_1_
from
Issue571Test$Cte issue571te0_
inner join
Issue571Test$MyEntity issue571te1_
on issue571te0_.myEntity_id=issue571te1_.id(I believe the original issue was that in this case, the inner join wouldn't be added as well, requiring an additional select to initialize the entity, but I cant reproduce that. The other part of the issue is however obvious from the above example). |
|
The issue occurs because the I think the easiest way to solve this is by providing different JPQL for the base query. I.e. translate |
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Aug 28, 2018
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Aug 30, 2018
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Aug 30, 2018
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Sep 3, 2018
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Sep 4, 2018
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Sep 4, 2018
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Sep 4, 2018
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Sep 4, 2018
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Sep 4, 2018
jwgmeligmeyling
changed the title
jwgmeligmeyling
added a commit
to jwgmeligmeyling/blaze-persistence
that referenced
this issue
Sep 13, 2018
…pound or foreign keys in CTE's
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Description
I am facing a couple of rarities with CTE's that have a
@ManyToOnerelationship. First of all, when I do something like this:bind("ref").select("entity.someMapping"), all columns ofsomeMappingare bound in the CTE, even though they are unused. This can be worked around with by mapping to the id instead, like so:bind("ref").select("entity.someMapping.id"), which works, even without changing the type of the field, but has some other quirks: in the final select query, the reference will always be lazy loaded (i.e. only the id column). Specifically adding the fetch join to the criteria builder is ignored. The effective query remains the same.Expected behavior
The expected behaviour is that the CTE only selects the required columns, and that it is possible to optionally fetch join these fields in the final result set.
Actual behavior
Either the fields have to be "fetch joined" in the CTE part already (leading to an unnecessarily complex CTE query), or it is not possible to fetch join these queries at all (adding fetch joins to a CTE root seems to be ignored).
Steps to reproduce
Workaround
A workaround is to map the id and referenced entity separately, but to a different column. Then in the CTE you bind the column through the id field, and in the final query you can fetch join the entity field.
Environment
Version:
JPA-Provider:
DBMS:
Application Server:
The text was updated successfully, but these errors were encountered: