A UserAccout is in an ManyToOne relationship with Person

I have the following HQL

select u from UserAccount u
left join u.person p with p.name like 'P%'
where u.username like 'U%'

works exactly as I expect -generating sql with the restriction on person within the join clause and not on the where clause

I can create almost the same query using the Criteria API but the problem is that the restriction on the joined property is placed in the where clause instead of being a part of the left join

After extensive searching I get the impression that the criteria API doesn't support adding restrictions to a join. I've also taken a look at using filters to accomplish this but filters seem to be static (defined in the mapping or aspects) and I need dynamic filters.

Is there any way of doing the above using the criteria API?

You can create an alias on the association specifying a left outer join.

criteria.createAlias("person", "personJoin", Criteria.LEFT_JOIN);
criteria.add(Restrictions.like("personJoin.name", "P", MatchMode.START);

This is exactly what I'm using but the restriction (person like 'P%' ) is placed on the where clause of the generated SQL and not on the left join clause. (Subtle difference but very important!)

Take the HQL query
from UserAccount u
left outer join u.person p with p.name like 'p%'
where u.username like 'U%'

Transleates correctly into SQL as

select .... from UserAccout u
left outer join u.person p on u.id = p.id and p.name like 'P%'
where
u.username like 'U%'

Using the Criteria API (as you suggested using createCriteria()) the above query translates into SQL as
select .... from UserAccout u
left outer join u.person p on u.id = p.id
where
u.username like 'U%' and p.name like 'P%'

as you can see the predicate p.name like 'P%' is in the wrong place!

I guess I don't quite understand why you'd want to do that then.

If you put the condition on the outer join it's no different than not having it in the first place, unless i am missing something.

From as far as I can tell you're still going to get the UserAccounts whether or not they have a person record or the name is bob, phil or gladys. What are you actually trying to accomplish with putting the condition on the join?

I could see it if you were trying to select the data from the 2 tables but the criteria API is going to return your root UserAccounts and will load any associated person whether or not their name begins with P.