How do I define multiple conditions on a join JPA/Ebean

Question

Ok, lets say I have order and items tables. This is how items are described in my Order class:

@ManyToMany(cascade=CascadeType.PERSIST)
@JoinTable(name="MY_ORDER_ITEM_BRIDGE",
joinColumns=@JoinColumn(name="bridge_order_id",referencedColumnName = "order_order_id"),
inverseJoinColumns = @JoinColumn(name="bridge_item_id", referencedColumnName="item_item_id"))
private List<Item> items;

This works fine. But lets image that there is a date in the bridge table called MY_ORDER_ITEM_BRIDGE.expiration_date.

I want to change the definition to only include Items with expiration_date that has not occurred yet.

So I want the generated SQL to look something like:

SELECT *
FROM order o
join my_order_item_bridge b
on b.bridge_order_id = o.order_order_id
join item i
on i.item_item_id = b.bridge_item_id
where b.expiration_date < sysdate;

I am using Ebean, Play Framework 2.1.3. Thanks for any help.

UPDATE: Alternatively, it could also be a second condition on either of the joins:

SELECT *
FROM order o
join my_order_item_bridge b
on b.bridge_order_id = o.order_order_id
and b.expiration_date < sysdate
join item i
on i.item_item_id = b.bridge_item_id

(If possible I'd like to do this in the class definition not in raw SQL)

Coltun

You need just to compose a query or to change a definition of the schema? – Timeout

I was trying to change the definition. But perhaps I should just filter out by date when I am trying to find the records? Either way I'm trying to avoid SQL in the code if possible. – Coltun

Answer 1

The problem is that ebean-generated bridge table can have two and only two columns: two foreign keys.

If you want to have another field there you should model the whole set as three entities: Order.java, Item.java and OrderItem.java.

To enforce integrity you can use constraints in OrderItem class:

@Table(uniqueConstraints=@UniqueConstraint(columnNames={"order_order_id", "item_item_id"}))

Timeout

Answer 2

You can use SQL directly in ebean. See http://www.avaje.org/static/javadoc/pub/com/avaje/ebean/RawSql.html

Mchugh

Yeah, I know. I might resort to that today. But I'm 80% sure this is possible in the class definition as well. – Coltun