Retrieveing list from a many-to-many relation with JPA/HQL

Question

I have 3 classes, I am trying to get a list of all the events of an eventhost that a user is subscribed to. I am probably thinking way too complicated but I have very little experience with JPA/HQL.

User class

@ManyToMany
    @JoinTable(name = "Subscriptions", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id") , inverseJoinColumns = @JoinColumn(name = "event_host_id", referencedColumnName = "id") )
    private List<EventHost> subscriptions;

EventHost class

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "event_host_id", referencedColumnName = "id", updatable = true)
private List<Event> events;

I tried using this query, but it tells me that subscriptions is not mapped, which it is not since it's not a java class.

String hql = "SELECT o FROM Event WHERE event_host_id IN (SELECT a FROM EventHost WHERE id IN(SELECT b FROM User WHERE = + " + userid + "))";

I know injecting the userid like this is bad practice, I'm just doing it for testing purposes.

Please ask if you need something more, I would really like to understand how to write a query for this.

Answer 1

This question should really be HQL with two join tables, but I'll let you change it. Since its HQL, or JPA, it's database independent.

Anyway, any time you see a OneToMany or ManyToMany relationship you have a join table and so you should be thinking joins. It's always a good idea to look at the sql create table statements to see what's going on. In this case your user_subscriptions join table is:

create table user_subscriptions (user_id integer not null, subscriptions_id integer not null)

and your event_host_events join table is this:

create table event_host_events (event_host_id integer not null, events_id integer not null)

Nothing new there. When you're trying to get something new working that you don't intuitively understand, break it down into things you can do. For example, you can execute two queries, getting a Users subscriptions first, and then getting the Events for those subscriptions:

Query query = session.createQuery("select u.subscriptions from User u where name = :name");  
query.setParameter("name", name);
List<EventHost> subscriptions = query.list();
List<Event> events = new ArrayList<Event>();
Query query2 = session.createQuery("select s.events from EventHost s where id = :id");  
for (EventHost s: subscriptions ) {
    query2.setParameter("id", s.getId());
    events.addAll( query2.list());
}

Not elegant, but it works. Then, keeping join in mind, figure out how to make one statement out of the two of them.

Query query = session.createQuery("select s.events from User u join u.subscriptions s where u.name = :name)");  
query.setParameter("name", name);
return query.list();

The join will use an inner join by default, so you're ok there. The JPA provider will auto-magically join your three Entity tables and two Join Tables for you:

select 
    event4_.id as id1_2_
from user user0_ 
    inner join user_subscriptions subscripti1_ on user0_.id=subscripti1_.user_id 
    inner join event_host eventhost2_ on subscripti1_.subscriptions_id=eventhost2_.id 
    inner join event_host_events events3_ on eventhost2_.id=events3_.event_host_id 
    inner join event event4_ on events3_.events_id=event4_.id 
where user0_.name=?

Aren't you glad you don't have to write that query?