with-clause referenced two different from-clause elements in hSQL
Solution 1
You don't need the ON clauses in your query. Hibernate does automatically link the models.
Solution 2
You don't need the ON clauses and conditions in your query, since you only use the FK, which is implicit anyway.
Try this:
SELECT *
FROM A a
INNER JOIN a.b b
LEFT OUTER JOIN b.c c WITH c.addType='PermanentAddress'
LEFT OUTER JOIN b.c d WITH d.addType='ResidentialAddress'
LEFT OUTER JOIN b.c e WITH e.addType='OfficeAddress'
WHERE a.id =:id
Related videos on Youtube
vivekanand jha
Updated on September 16, 2022Comments
-
vivekanand jha over 1 year
Getting the exception:
With-clause referenced two different from-clause elements
When executing the query below with the following entities:
@Entity public class A { @Id private Long id; @Column(name = "A_ID") private Long aId; @ManyToOne(fetch=FetchType.LAZY) @JoinColumn(name = "A_ID") private B b; } @Entity public class B { @Id private Long id; @OneToMany(fetch=FetchType.LAZY, cascade=CascadeType.ALL) @JoinColumn(name="C_ID" , referencedColumnName="ID") private List<C> c; } @Entity public class C { @Id private Long id; @Column(name="C_ID") private Long cId; @Column private String addType; }
My HQL Query is:
SELECT * FROM A a INNER JOIN a.b b ON a.aId=b.id LEFT OUTER JOIN b.c c ON b.id=c.cId AND c.addType='PermanentAddress' LEFT OUTER JOIN b.c d ON b.id=d.cId AND d.addType='ResidentialAddress' LEFT OUTER JOIN b.c e ON b.id=e.cId AND e.addType='OfficeAddress' WHERE a.id =:id
The exception that I get is:
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause referenced two different from-clause elements at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:284) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:126) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:88) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328) ~[hibernate-entitymanager-4.3.6.Final.jar:4.3.6.Final] ... 187 common frames omitted
-
Poliakoff almost 8 yearsHelped at similar case, just replaced
on
towhere
. Thanks. -
Muddassir Rahman about 6 years@attofi all on with "where" ?