CODEWITHSUNDEEP
javasqljpaplayframeworkjpql
Elvis Oliveira
Elvis Oliveira

Reputation: 949

JPQL - ManyToMany Self reference

I'm having a problem to make a query with JPQL. I have one table with reference himself with Many to Many:

@Entity
@Table(name = "item")
public class Item  extends BaseModel implements Serializable {
    @Column(name = "id")
    public INteger id;

    @ManyToMany(cascade = CascadeType.ALL, fetch=FetchType.EAGER)
    public Set<Item> itemsRelated;

}

And I'm trying to make this query:

Select * from Item where  itemsRelated=null

or Select * from Item where itemsRelated is null

But its throwled this Exception:

play.exceptions.JavaExecutionException: Error while executing query from Item where itemsRelated=null order by createDate desc: Unknown column 'qualified' in 'where clause' at play.mvc.ActionInvoker.invoke(ActionInvoker.java:237) at Invocation.HTTP Request(Play!)

Can anyone help me?

UPDATE:

Java call:

Item.find(query.toString()+" order by createDate desc").fetch(page, itemsPerPage);

At this point "query" has " itemsRelated is null"

Upvotes: 0

Views: 332

Answers (3)

vboerchers
vboerchers

Reputation: 897

itemsRelated is a collection and you have to check the size of the collection - don't expect it to be null. Try

select i from Item i where i.itemsRelated is empty

See http://docs.oracle.com/cd/E17904_01/apirefs.1111/e13946/ejb3_langref.html#ejb3_langref_empty_comp

Edit: completed query with alias

Upvotes: 1

Elvis Oliveira
Elvis Oliveira

Reputation: 949

Successful!

The soluction was to use INNER JOIN, lets se:

id NOT IN (SELECT DISTINCT(i.id) FROM Item i INNER JOIN i.itemsRelated ii)

the result is:

Select * from Item where id NOT IN (SELECT DISTINCT(i.id) FROM Item i INNER JOIN i.itemsRelated ii)

Thanks for everyone!

Upvotes: 0

Andreas
Andreas

Reputation: 5103

I'm not a jpql expert, but the syntax is null and is not null should work. Have you tried

Select * from Item where itemsRelated is null

http://docs.oracle.com/cd/E17904_01/apirefs.1111/e13946/ejb3_langref.html#ejb3_langref_null

Upvotes: 0

Related Questions