CODEWITHSUNDEEP
sqlhibernatehql
Cichy
Cichy

Reputation: 1339

Many to Many and HQL

I got class:

@Entity
@Table(name="restaurants")
public class Restaurant {
    @Id
    @GeneratedValue
    private int id;
    private String name;
    private String street;
    (...)
    @ManyToMany
    @JoinTable(name="user_restaurant_owner",
            joinColumns={@JoinColumn(name="restaurant_id")},
            inverseJoinColumns={@JoinColumn(name="username")})
    private List<User> owner;

How to get Restaurant if I know User ("owner") username?

Query q = session.createQuery("from Restaurant as r where r.owner = :username").setString("username", username);

it doesn;t work

Upvotes: 1

Views: 5000

Answers (3)

digitaljoel
digitaljoel

Reputation: 26584

You need to join to a collection in HQL if you are selecting on properties of the element within the collection.

from Restaurant r join r.owner o where o.username = :username

Upvotes: 8

Ali
Ali

Reputation: 12684

from Restaurant as r should be from Restaurant r no as (not sure if as is optional). Also, you do r.owner = ... this should be r.owner.username = :username

Upvotes: 1

iliaden
iliaden

Reputation: 3889

r.owner references the PRIMARY KEY of the Owners table.

what you want is a subselect (done in SQL here for simplicity - I'm not too confident with HQL):

select {r.*} from Restaurant as r where r.owner = ( select id from Owners as own where own = :username ).setString("username", username)

this way, you match the foreign key with the field it references, rather than the name field.

Upvotes: 0

Related Questions