CODEWITHSUNDEEP
hibernatejpahql
hardcore
hardcore

Reputation: 121

Sorting HQL join path errors

I am having trouble translating a mildly complex (~8 sloc) SQL query to the HQL language.

I have been messing around with it for a day, sadly, the examples on the internet are way too trivial to be any help. I have reduced my query to the smallest sample that still gives the path error. Any clues would be appreciated.

SQL (works)

SELECT container_modulelist 
FROM project.t_type 
INNER JOIN project.t_module ON project.t_type.module_id =project.t_module.id 
WHERE project.t_type.id = 111

HQL (gives join path error) 

    @Query("
select module.container_modulelist 
from Type type 
join Module module on type.module_id = module.id 
where type.id =111
")

Update : the above part should be solved by answer below

However, I still have a problem getting the sub query to execute.

I tested the section outside the brackets and it works great. However I am having trouble implementing the part in the brackets(from the answer below), I'd like to evaluate the sub query and compare it in order for my query to return the value, I have seen this can be done from the HQL documentation. In the current form, the sub query doesn't return with anything no matter the parameter.

Equivalent(?) HQL (sub query not working):

@Query("
        select value from Value value
            where value.type.module.containerModuleList = (
                select type.module.containerModuleList from Type type 
                    where type.id =111) and value.name =yyy5
")
Value findOneByNameFromModuleList(@Param("id") Long id, @Param("name") String name);

Working part: 

 @Query("
         select value from Value value
             where value.type.module.containerModuleList = 1 and value.name =yyy5
 ")
 Value findOneByNameFromModuleList(@Param("name") String name);

Upvotes: 0

Views: 102

Answers (1)

JB Nizet
JB Nizet

Reputation: 692181

HQL doesn't use table and column names, but entity and field/property names. You idn't post your entities, so it's hard to answer. But your query seems to use column names, or your Java code doesn't respect Java conventions at all.

Furthermore, you don't need any on clause on joins, because you use associations between entities for joins, and since these associations are already mapped with JoinTable/JoinColumn, Hibernate knows how the tables must be joined.

So, assuming your entities respect the Java naming conventions, and that you have a ManyToOne association between the Type and Module entities (i.e. there is a field module of type Module annotated with @ManyToOne in Type), the query should be

select cml
from Type type 
join type.module module
join module.containerModuleList cml
where type.id = 111

Or simply

select type.module.containerModuleList from Type type where type.id = 111

This is documented.

Upvotes: 1

Related Questions