CODEWITHSUNDEEP
javasqlhibernatejpahql
Danilo Radenovic
Danilo Radenovic

Reputation: 1029

Hibernate HQL "Path expected for join!" @ManyToOne relationship

Suppose there are two entities - Owner

@Entity
@NamedQueries({
   @NamedQuery(name = "Owner.findOwnerForPetId", query = "select o from Owner o inner join Pet p on o.ownerId=p.owner.ownerId where p.petId= :petId")
})
public class Owner {

  @Id
  @Column(name = "ownerId")
  private Long ownerId;

  @Column
  private String name;

  // scaffolding code...
}

and Pet

@Entity
public class Pet {

  @Id
  @Column(name = "petId")
  private Long petId;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "ownerId")
  private Owner owner;

  @Column
  private String name;

  // scaffolding code...
}

where one Owner can have multiple pets (original class were renamed), but one Pet can only belong to one Owner. What I would like to do is find the Owner that owns a Pet that has some id, like:

select Owner.ownerId, Owner.name from Owner inner join Pet on Owner.ownerId=Pet.ownerId where Pet.petId=3;

This works fine when executed in pure SQL. However, I have tried these two queries in HQL and they both give the error Path expected for join!

select o from Owner o inner join Pet p on o.ownerId=p.owner.ownerId where p.petId= :petId

and

from Owner o join Pet p where p.petId= :petId

Note that there are no @OneToMany or Collection<Pet> pets in Owner. I would like to do it with only a @ManyToOne on the Pet side.

Any hints on what I have missed?

Upvotes: 2

Views: 1204

Answers (3)

lilalinux
lilalinux

Reputation: 3031

For the sake of completeness, if you need to LEFT JOIN but have the @ManyToOne attribute on the right side and thus can't specify a path, you can transform the query into a RIGHT JOIN with the same effect (i.e. not losing rows where the other table doesn't have matching rows and filtering the other table without losing null rows).

Suppose you want to get all owners that don't have pets ignoring pets named Charly:

You can't specify

SELECT o
FROM Owner o
LEFT JOIN o.pet p (<-------- ERROR) WITH p.name != 'Charly'
GROUP BY o.ownerId
HAVING count(p.petId) = 0

But you could transform this into:

SELECT o
FROM Pet p
RIGHT JOIN p.owner o WITH p.name != 'Charly'
GROUP BY o.ownerId
HAVING count(p.petId) = 0

Upvotes: 1

mibrahim.iti
mibrahim.iti

Reputation: 2060

When working with HQL you must use the relations between entities not just entities

so for INNER JOIN and LEFT JOIN for example you should use the relation direct

For example next are valid queries

SELECT o FROM Pet p inner join p.owner o WHERE p.petId= :petId (same as @rathna accepted answer)

SELECT p FROM Pet p WHERE p.owner.ownerId = :ownerId

Upvotes: 1

rathna
rathna

Reputation: 1083

Try this one

  select o from Pet p inner join p.owner o where p.petId= :petId

Upvotes: 2

Related Questions