Influencing join condition

[Pawel Veselov <pa...@gmail.com>]

Hi.

I was wondering if there was a way to influence join condition using
criteria builder API.

Suppose I have two tables: items and purchases. I want to build a left join
between the two tables, but only for the 'current user'. In SQL, I would
say:

select i.xx, p.yy from items i left join purchases p on (i.id = p.item_id
and p.owner = ? );

I can't move the 'p.owner=?' condition outside of "on", as it will change
the outcome of the query.

I understand that calling From.join(attr) creates the implicit join
conditions for the PKs, but I can't figure out if there is a way to
influence the rest of the join condition.

Another option would be to join against a sub-query

select i.xx, p.yy from items i left join (select * from purchases where
owner=?) p on i.id = p.item_id;

, but I also don't quite see how I can build that with criteria API...

Thank you!
  -- Pawel.