CODEWITHSUNDEEP
javahibernate
Akhilesh
Akhilesh

Reputation: 1410

HQL JOIN involving multiple associations

I have three entities with relation UserDetails hasmany FiledTask and Task hasMany FiledTask. All I want is list of FiledTask of particular User

For UserDetails:

@OneToMany(mappedBy="user",cascade=CascadeType.ALL)
Collection<FiledTask> filedTasks = new ArrayList<FiledTask>();

And for Tasks I have 

@OneToMany(mappedBy="task")
Collection<FiledTask> filedTasks = new ArrayList<FiledTask>();

And FiledTask looks like 

@ManyToOne
@JoinColumn(nullable = false, name = "taskId")
private Tasks task;
@ManyToOne
@JoinColumn(nullable = false, name = "userId")
private UserDetails user;

I have tried 

query = session.createQuery("from Tasks as tasks inner join tasks.filedTasks as files with files.user = :user");
query.setParameter("user", user); //user is UserDetails instance

But I am getting error clause can only reference columns in the driving table, means FiledTask can't userId for comparision?

with clause can only reference columns in the driving table [from com.akhi.app.cdm.Tasks as tasks inner join tasks.filedTasks as files with files.user = :user]

Upvotes: 3

Views: 3153

Answers (3)

Thomas L.
Thomas L.

Reputation: 1

I had the same problem using a MySql (Inno) DB plus my dialect class derived from Hibernate's MySQL5InnoDBDialect - class.

I had to override the supportsRowValueConstructorSyntax-method and let it return false as MySQLDialect (which is the base class of MySQL5InnoDBDialect) returns true.

@Override
public boolean supportsRowValueConstructorSyntax() {
    return false;
}

Doing this, hibernate does not use the object reference (in your case .user) in order to create a tuple for the comparison. I hope, this will help.

Upvotes: 0

Mike N
Mike N

Reputation: 125

OK, looks like there is a bug in Hibernate

https://hibernate.atlassian.net/browse/HHH-2772

I think in your case what you need to do is 

query = session.createQuery("from Tasks as tasks inner join tasks.filedTasks as files with files.user.id = :userId");
query.setParameter("userId", user.id);

Hope this helps somebody. I spend several hours on this.

Upvotes: 5

JB Nizet
JB Nizet

Reputation: 691635

If you want the FiledTasks of a given user, then the easiest thing to do is

UserDetails user = session.find(UserDetails.class, userId);
Collection<FiledTask> filesTasks = user.getFiledTasks();

Using HQL, it would be

select f from FiledTask f where f.user = :user

If what you want is in fact the tasks of a given user, then the query would simply be

select t from Tasks t
inner join t.filedTasks filedTask
where filedTask.user = :user

Note that the entity Tasks shouldbe named Task. An instance represents a single task, and not multiple ones.

Upvotes: 5

Related Questions