Play Framework/JPA: Find using dot notation works but same query using delete does not

Question

Let's say I have a couple of entities that look like this:

@Entity
public class Person extends Model {
    @ManyToOne
    @JoinColumn(name = "fooId", nullable = false)
    public Foo foo;
    ...
}

@Entity
public class Foo extends Model {
    @ManyToOne
    @JoinColumn(name = "barId", nullable = false)
    public Bar bar;
    ...
}

Now let's say I want to find all People that are not associated with a particular instance of Bar:

Person.find("foo.bar <> ?", someInstanceOfBar).fetch();

This works fine and returns all the People I would expect.

However, if I tweak the statement above slightly to delete all People that are not associated with Bar:

Person.delete("foo.bar <> ?", someInstanceOfBar);

I get the following:

PersistenceException occured : org.hibernate.exception.SQLGrammarException: could not execute update query
...
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "DELETE FROM PERSON CROSS[*] JOIN FOO FOO1_ WHERE BARID<>? ";

Any ideas why finding People would return the correct number of rows, yet deleting People using the same where-clause would result in an exception?

Answer 1

The problem is that you have a Join in the delete statement. Delete in JPQL only accepts "where" clauses, and a unique table as target. Your join breaks that condition. See Oracle documentation on JPQL