How to use @Where in Hibernate

Question

Searched for a few hours, but I'm stuck in a my learning curve for PlayFramework with JPA. I'm building a sample website where posts can be made. But these posts can have the states:

• PostDraft (post is a draft, do not publish)
• PostPublished (post can be published)

These states are stored in a seperate table. Obviously, the draft state posts should not be visible yet.

So I have these classes:

• Page class (getting the page information from table, 1 page can have multiple posts)
• Posts class (posts can be in draft and published)

In my page class I have:

@Column(name="POSTS_REF") @Where(clause="PostPublished") private List<Posts> userPosts; 

But this is not working! So, how can I specifify a where clause, to load only the posts that are in published state without using JPQL??

Thanks!

UPDATE: 2011-10-11

Table: Posts with columns: - id - title - state_ref (reference to the ID of States table) - content

Table: States with columns: - id - statename

So I want to say something like:

select *  from posts inner join states on posts.state_ref = states.id where states.statename = 'PostPublished' 

UPDATE 2011-10-13

This is my current modification, in my page class: but it does not work either.

/** link to the states */ @JoinColumn(name = "STATES_REF") @OneToOne @Where(clause = "states.statename = 'PostPublished'") public MyState state; 

UPDATE 2012-02-13 Emt's answer worked for me after all.

Answer 1

Try something like:

@Column(name="POSTS_REF") @Where(clause="state='PostPublished'") private List<Posts> userPosts; 

or

@Column(name="POSTS_REF") @Where(clause="PostPublished=true") private List<Posts> userPosts; 

depending on the status field type on your Post entity.

Answer 2

The where clause must be a complete condition - something like this. Assuming that state is a property on the post.

@Column(name="POSTS_REF") @Where(clause="state = 'PostPublished'") private List<Posts> userPosts; 

EDIT

Based on the data model - the following should work. I wouldn't recommend using it. Don't map the posts collection - just have a reference to Page from the POsts class, add a method to your DAO to retrieve the published posts for a page by using HQL or criteria query.

@Column(name="POSTS_REF") @Where(clause="exists (select id from states where state_ref = states.id and states.statename = 'PostPublished')") private List<Posts> userPosts;