How to get the Join of Two One to Many associations?

Question

I have two hibernate entities User and Blog. User can be interested in multiple Tags. Blog can belong to multiple Tags. For a User, How do i find the Blogs which belong to the Tags the User is interested in?

I need something like

Select * from Blog where Blog.Tags IN User.Tags

except that SQL or HQL doesnt allow such comparisons in IN clause

A solution which im using currently is: 1. Generate a VIEW USER_BLOGS which is the cartesian product of the join tables USER_TAGS and BLOG_TAGS. 2. Define an Entity UserBlog for the View and use it to filter Blogs in the HQL query:

Select * from Blog where Blog.id IN (Select blog_id from UserBlog where user_id = "CurrentUser")

Im sure this is a common scenario. Is there a pure Hibernate or HQL solution for this?

Answer 1

Are you looking for something like this?

select b.* 
  from blog b 
  join blog_tags bt on bt.blog_id = b.id
  join user_tags ut on ut.tag = bt.tag
  where ut.user_id = (this_user)

Contrary to the comment, I believe HQL does permit explicit joins (I may be wrong, but see http://www.hibernate.org/hib_docs/reference/en/html/queryhql-joins.html). It could be done without explicit joins, in any case:

select b.* 
  from blog b, blog_tags bt, user_tags ut 
  where ut.user_id = (this_user) 
    and bt.blog_id = b.id 
    and ut.tag     = bt.tag

Answer 2

afair HQL has IN eg. somecat in elements(cat.kittens) http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html This chapter is purely about querries there is big part about handling collections and joins. gl hf

Answer 3

For MarkusQ's solution, i had to declare entities for join tables like UserTag and BlogTag to use in HQL query.

My previous solution of creating a USER_BLOGS view was too slow.

This is the HQL query that worked without any additional entities:

select b.* from Blog blog, User user, Tag tag
where tag in elements (user.tags)
and tag in elements (blog.tags)
and user.id = (current_user)

If you find any problems in this or better solutions, please post your answers