What is the HQL equivalent of this SQL

Question

Trying to do a bit more complex query, and thought that HQL would be better for the job. Using nHibernate.

SELECT * FROM [Group] g 
  INNER JOIN [User2Group] ug on g.Id = ug.GroupId
  INNER JOIN [User] u ON u.Id = ug.UserId
  INNER JOIN Activity a on g.ActivityId = a.Id
WHERE u.Id = ? AND a.Lineage LIKE '?%'

I guess I could also just use the SQL as well (?), but not sure really how to load up my objects that way.

Answer 1

It would depend on what your entities are and what the primary object you care about is. You seem to be pulling them all instead of just one entity. I am going to assume Group is the entity from this example

from MyApp.Entities.Group as g
join fetch g.Users as u
join fetch g.Activity as a
where u.Id = :userId and a.Lineage like '?%'

That should get you started. But with out knowing your structure, I am taking a shot in the dark.

Answer 2

If you have many-to-many connection (User2Group table) you should start from it and make all the joins on this table. If you have correct mappings, the query below will work like a charm.

from User2Group as ug
join ug.User as u
join ug.Group as g
where u.Id = :userId and g.Activity.Lineage like '?%'