Grails控制器中执行HQL的executeQuery报错求助

Hey there, let's sort out that HQL syntax error you're hitting. The root of the problem is a duplicate where clause in your subquery, which is throwing off the HQL parser.

What's Causing the Error #

Looking at your error message, the problematic segment of your query is:

SELECT t.training_id from com.ashwin.User u INNER JOIN com.ashwin.TrainUser t on u.id=t.user_id where u.id=t.user_id where u.id=:uid

You've got two where keywords here—HQL only allows one where clause per query segment. Plus, the first where u.id=t.user_id is totally redundant: you already defined that relationship in the INNER JOIN ... on clause, so repeating it isn't just invalid, it's unnecessary.

Corrected Controller Code #

Here's the fixed version of your code, with the duplicate where removed and a couple of Grails-idiomatic improvements:

def currentUser = springSecurityService.currentUser.id
def trainingList = Training.executeQuery("""
    FROM Training tr 
    WHERE tr.id NOT IN (
        SELECT t.training.id 
        FROM User u 
        INNER JOIN u.trainUsers t 
        WHERE u.id = :uid
    )
""", [uid: currentUser])

A quick breakdown of the tweaks:

• If your User domain class has a proper association with TrainUser (like a hasMany relationship), you can use u.trainUsers instead of writing the join condition manually—this makes the query cleaner and aligns with Grails' object-oriented approach.
• Swapped t.training_id for t.training.id (assuming TrainUser has a training domain property mapped to the training_id column) to follow Hibernate's object-focused query style.

Why This Works #

By removing the duplicate where clause, the HQL parser can correctly parse the entire query. The INNER JOIN already enforces that u.id = t.user_id, so we don't need to restate that condition in a separate where clause.

内容的提问来源于stack exchange，提问作者Ashwin Karki