CODEWITHSUNDEEP
javahibernatehql
mogronalol
mogronalol

Reputation: 3015

Hibernate - HQL to fetch a collection from Unidirectional OneToMany relationship

I have an Class with a unidirectional one to many relationship as follows:

public class Order {
    @OneToMany(cascade = CascadeType.ALL)
    @JoinTable(name="order_item", joinColumns={@JoinColumn(name="order_id")}, inverseJoinColumns={@JoinColumn(name="item_id")})
    public Set<Item> getItems() {
        return items;
    }
}

Normally getting the contents of this order is straightforward:

List<Item> items = order.getItems();

But for whatever reason I might want to filter my results in some way and retrieve only part of a collection of items such as all items more than a certain price, below a certain stock etc in the fastest way possible (Not returning then all then filtering afterwards). To do this I would run a HQL query to retrieve the items for a particular order and add some more stuff into my where clause or onto my query object.

Intuitively I would want this behaviour (Which is completely wrong):

SELECT jointable.ITEM from order_item as jointable inner join jointable.order where order = :order

But of course this is wrong as HQL works in terms of mapped entities, so I cannot use the join table in the query. So what is the correct way to do this?

Edit:

I have found the answer to this question, I want the following Query:

Select o.items from Order o where o = ?

This allows me to fetch the collection of items for an order, without having to use a bidirectional relationship. I am now however confused on the second stage of this question, which is how to filter the results of this collection, the most simple example being:

Select o.items from Order o where o = ? order by o.items.somenumberfield asc

Which returns illegal attempt to dereference collection, so how would I filter my items?

Edit:

The ticket solution is actually correct, I misread the solution originally.

Upvotes: 8

Views: 10747

Answers (2)

Alex Barnes
Alex Barnes

Reputation: 7218

It sounds like you want to map the other side of this relationship i.e. make this bi-directional mapping.

Then you can use the order number in your HQL:

from Item as item where item.amount > :amount and item.order.id = :orderId

From the HQL documentation:

from Cat as cat where cat.mate.id = 69 The query is efficient and does not require a table join.

The fact that you are trying to do this query suggests that the relationship between a Line and its Order is important!

Upvotes: 3

JB Nizet
JB Nizet

Reputation: 691635

select item from Order order
inner join order.items item
where order = :order
and ...

HQL queries use entities and their associations. The fact that the association uses a join table or not is not important for HQL: you navigate through associations and Hibernate does the appropriate translation to SQL.

Upvotes: 8

Related Questions