Left join with leftJoin(target).on(condition) throwing "Path expected for join!" error

[thibaudsowa]

Hi,

I want to perform a left join with the leftJoin(EntityPath<P> target).on(Predicate condition) notation.

This is my entities:

@Entity(name = "CLASSROOM")
public class Classroom {
    // [...]
}

@Entity(name = "STUDENT")
public class Student {

    @Column(name = "NAME")
    private String name;

    @ManyToOne
    @JoinColumn(name = "CLASSROOM_ID")
    private Classroom classroom;

    // [...]
}

This is an exemple query wich doesn't work:

QClassroom classroom = QClassroom.classroom;
QStudent student = QStudent.student;

query.from(classroom)
    .leftJoin(student).on(classroom.eq(student.classroom))
    .where(student.name.eq("antoine"))
    .list(classroom)

Here is the error log:

org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join! [select classroom
from com.my.personnal.path.entity.Classroom classroom
  left join STUDENT student with classroom = student.classroom
where student.name = ?1]

Of course for this exemple I could do something like this:

query.from(student)
    .leftJoin(student.classroom, classroom)
    .where(student.name.eq("antoine"))
    .list(classroom)

...but my real query is bigger than this and I cannot begin the query with query.from(student).

What I'm doing wrong?

I'm using QueryDsl 3.6.6

[timowest]

It should be

QClassroom classroom = QClassroom.classroom;
QStudent student = QStudent.student;

query.from(classroom)
    .leftJoin(classroom.students, student)
    .where(student.name.eq("antoine"))
    .list(classroom)

You will need to inverse map students in classroom for this

[thibaudsowa]

Ok thanks, it could be a solution but actually I have two constraints:

• To begin my query with .from(classroom)
• To link the two entities with a ManyToOne association in Student entity (without having a bidirectional association)

With that two constraints, is there any possibilities to join my two entities?

[timowest]

This should also work

query.from(student, classroom)
    .where(
        student.classroom.eq(classroom),
        student.name.eq("antoine"))
    .list(classroom)

[timowest]

Please reopen, if still unresolved

[aTTraX]

Hi Timo,

sorry for reopening this issue, but I have a similar problem and can´t get along with the solutions you described above.

My Schema:
Campus.class

@Entity
public class Campus {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

private String name;

@OneToMany(mappedBy = "associatedCampus", cascade = CascadeType.ALL)
private Set<Building> buildings;

... Getters/Setters, Constructors

Building.class

@Entity
public class Building {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@GeneratedValue(generator = "system-uuid")
@GenericGenerator(name = "system-uuid", strategy = "uuid")
private String uuid;

@ManyToOne
@JoinColumn(name = "associatedCampus")
private Campus associatedCampus;

... Getters/Setters, Constructors

I try to join from the Campus table to the Building table, but I always get errors I don´t understand.
Error: java.lang.IllegalArgumentException: building.associatedCampus is not a root path

Its absolutely no problem to join from building to campus, but not from campus to building.
Example:

public List<Building> getBuildingsByCampusName(String campusName) {
  JPAQuery<Building> query = new JPAQuery<>(em);
  QCampus campus = QCampus.campus;
  QBuilding building = QBuilding.building;
  query.select(building)
  .from(building)
  .join(building.associatedCampus, campus)
  .where(campus.name.like("%" + campusName + "%"));
  List<Building> result = query.fetch();
  return result;

Could you please give me a hint what my problem is, or why the reverse join of building->campus isn´t working?

Greetz
Markus

[wmughal]

My Entity like as under:
@entity
@table(name = "PARTICIPANT")
public class Participant {
@OnetoOne(cascade = CascadeType.DETACH, fetch = FetchType.EAGER, targetEntity = ServiceType.class)
@jointable(name = "PARTICIPANT_SERVICES", joinColumns = @joincolumn(name = "PARTICIPANT_ID", referencedColumnName = "PARTICIPANT_ID"), inverseJoinColumns = @joincolumn(name = "SERVICE_TYPE_ID", referencedColumnName = "SERVICE_TYPE_ID"))
private List serviceTypes;

    @OneToMany(mappedBy = "participant")
@Cascade({org.hibernate.annotations.CascadeType.ALL})
private List<ParticipantRegionCountry> participantRegionCountries;

getter / setter
}

@entity
@table(name = "SERVICE_TYPE")
public class ServiceType {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "SERVICE_TYPE_ID")
private Long id;

@Column(name = "SERVICE_TYPE_NAME")
private String name;
   getter / setter

}
when I query on participant entity as under getting exception:
List participants = new ArrayList<>();

	JPQLQuery<Participant> query = new JPAQuery<>(em);
	QParticipant participant = QParticipant.participant;
	QParticipantRegionCountry participantRegionCountry = QParticipantRegionCountry.participantRegionCountry;
	BooleanBuilder predicate = new BooleanBuilder();
	predicate.and(participantRegionCountry.region.in(regions));
	
	participants = query.select(participant).from(participant).join(participant.participantRegionCountries, participantRegionCountry).where(predicate).distinct().fetch();

Exception:
Hibernate: select distinct participan0_.PARTICIPANT_ID as PARTICIPANT_ID1_6_, participan0_.BUSINESS_NAME as BUSINESS_NAME2_6_, participan0_.BUSINESS_TYPE_ID as BUSINESS_TYPE_ID9_6_, participan0_.CITY_ID as CITY_ID10_6_, participan0_.CONTACT_ADDRESS as CONTACT_ADDRESS3_6_, participan0_.CONTACT_EMAIL as CONTACT_EMAIL4_6_, participan0_.CONTACT_NAME as CONTACT_NAME5_6_, participan0_.CONTACT_PHONE as CONTACT_PHONE6_6_, participan0_.COUNTRY_ID as COUNTRY_ID11_6_, participan0_.CURRENCY_ID as CURRENCY_ID12_6_, participan0_.LANGUAGE_ID as LANGUAGE_ID13_6_, participan0_.PARTICIPANT_PACK_ID as PARTICIPANT_PACK_14_6_, participan0_.PARTICIPANT_TYPE_ID as PARTICIPANT_TYPE_15_6_, participan0_.PAYMENT_METHOD_TYPE_ID as PAYMENT_METHOD_TY16_6_, participan0_.PROJECTED_VOLUME as PROJECTED_VOLUME7_6_, participan0_.REGION_ID as REGION_ID17_6_, participan0_.USER_NAME as USER_NAME8_6_, participan0_1_.SERVICE_TYPE_ID as SERVICE_TYPE_ID1_12_ from PARTICIPANT participan0_ left outer join PARTICIPANT_SERVICES participan0_1_ on participan0_.PARTICIPANT_ID=participan0_1_.PARTICIPANT_ID inner join SERVICE_TYPE servicetyp1_ on participan0_1_.SERVICE_TYPE_ID=servicetyp1_.SERVICE_TYPE_ID inner join PARTICIPANT_REGION_COUNTRY participan2_ on participan0_.PARTICIPANT_ID=participan2_.PARTICIPANT_ID where servicetyp1_.SERVICE_TYPE_ID=? and (participan2_.REGION_ID in (? , ?))
Hibernate: select servicetyp0_.SERVICE_TYPE_ID as SERVICE_TYPE_ID1_18_0_, servicetyp0_.SERVICE_TYPE_NAME as SERVICE_TYPE_NAME2_18_0_ from SERVICE_TYPE servicetyp0_ where servicetyp0_.SERVICE_TYPE_ID=?
javax.persistence.PersistenceException: org.hibernate.PropertyAccessException: Could not set field value [com.mycompany.app.spring.orm.model.ServiceType@4e9e0c6] value by reflection : [class com.mycompany.app.spring.orm.model.Participant.serviceTypes] setter of com.mycompany.app.spring.orm.model.Participant.serviceTypes
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
at org.hibernate.query.Query.getResultList(Query.java:146)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:379)
at com.sun.proxy.$Proxy47.getResultList(Unknown Source)
at com.querydsl.jpa.impl.AbstractJPAQuery.getResultList(AbstractJPAQuery.java:160)
at com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:202)
at com.mycompany.app.spring.orm.service.ParticipantService.listParticipants(ParticipantService.java:53)
at com.mycompany.app.App.main(App.java:56)
Caused by: org.hibernate.PropertyAccessException: Could not set field value [com.mycompany.app.spring.orm.model.ServiceType@4e9e0c6] value by reflection : [class com.mycompany.app.spring.orm.model.Participant.serviceTypes] setter of com.mycompany.app.spring.orm.model.Participant.serviceTypes
at org.hibernate.property.access.spi.SetterFieldImpl.set(SetterFieldImpl.java:61)
at org.hibernate.tuple.entity.AbstractEntityTuplizer.setPropertyValues(AbstractEntityTuplizer.java:649)
at org.hibernate.tuple.entity.PojoEntityTuplizer.setPropertyValues(PojoEntityTuplizer.java:205)
at org.hibernate.persister.entity.AbstractEntityPersister.setPropertyValues(AbstractEntityPersister.java:4711)
at org.hibernate.engine.internal.TwoPhaseLoad.doInitializeEntity(TwoPhaseLoad.java:183)
at org.hibernate.engine.internal.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:125)
at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:1152)
at org.hibernate.loader.Loader.processResultSet(Loader.java:1011)
at org.hibernate.loader.Loader.doQuery(Loader.java:949)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
at org.hibernate.loader.Loader.doList(Loader.java:2692)
at org.hibernate.loader.Loader.doList(Loader.java:2675)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)
at org.hibernate.loader.Loader.list(Loader.java:2502)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1490)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
... 11 more
Caused by: java.lang.IllegalArgumentException: Can not set java.util.List field com.mycompany.app.spring.orm.model.Participant.serviceTypes to com.mycompany.app.spring.orm.model.ServiceType
at sun.reflect.UnsafeFieldAccessorImpl.throwSetIllegalArgumentException(UnsafeFieldAccessorImpl.java:167)
at sun.reflect.UnsafeFieldAccessorImpl.throwSetIllegalArgumentException(UnsafeFieldAccessorImpl.java:171)
at sun.reflect.UnsafeObjectFieldAccessorImpl.set(UnsafeObjectFieldAccessorImpl.java:81)
at java.lang.reflect.Field.set(Field.java:764)
at org.hibernate.property.access.spi.SetterFieldImpl.set(SetterFieldImpl.java:41)
... 30 more
Any solution?
I'm new on QueryDSL, but why is querying on service_types?

[nitindahiya]

How are self joins supposed to work then ?

There is certainly no relationship defined in this case.

[Shredder121]

JPQL is based on properties, so if there isn't a relationship (defined as a property) then you can't really arbitrarily join it.
Unless you go JpaSqlQuery, where you can use more SQL.

[giovanni-nabil]

How are self joins supposed to work then ?

There is certainly no relationship defined in this case.

if you have something like

Class Person {
   ....
   Person twin;
}

assuming you're using JPQL query expression

QPerson person = QPerson.person;
QPerson twinPerson = new QPerson("twinPerson")

.....
   .innerJoin(person.twin, twinPerson) 

but a relation has to exist on Object level