[ColdBox 3.6.0] HQL error on join syntax

Communities ColdBox HMVC
1

I have this HQL:

var qs = “”;
qs = qs & “SELECT m.username, m.user_id, u.username FROM user AS u LEFT OUTER JOIN user AS m”;
qs = qs & " ON u.parent_id = m.user_id";
qs = qs & " WHERE m.user_id = :id";
var params = {id=tt};
rc.users = userService.executeQuery(query=qs,params=params);

It’s a join on a self reference table.

It gives error:

Application Execution ExceptionError Type: org.hibernate.hql.ast.QuerySyntaxException : 0

Error Messages: unexpected token: ON near line 1, column 83 [SELECT m.username, m.user_id, u.username FROM user AS u LEFT OUTER JOIN user AS m ON u.parent_id = m.user_id WHERE m.user_id = :id]

2

I could use join in HQL?

It’s the self reference join that causes this error?

3

Hi Tropicalista,

You sql statement does not look right as HQL…

Why not try just “var qs = query new()” I hope this should work then

4

Hi Tropicalista,

You sql statement does not look right as HQL…

Why not try just “var qs = new query()” I hope this should work then

5

Yes, it worked… thanks