Hibernate : could not interpret path expression when doing JOIN on unrelated tables

Hibernate ORM
1

I have the tables: Post and UserSettings. userId is coming from external DB that’s why both entites are unrelated.

I am using hibernate 6. The left join with comments is already working.

@Entity
data class Post(
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long,
    val userId: Long,  
    val content: String,
    @OneToMany(mappedBy = "post", fetch = FetchType.LAZY)
    val comments: List<Comment> = emptyList()
)

@Entity
data class UserSettings(
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long,
    val userId: Long,
    @Enumerated(EnumType.STRING) // Ensure correct enum mapping
    val settings: Setting
)
enum class Setting{
    ALL,
    NONE,
    CUSTOM
}

I want to have a LEFT join between Post and UserSettings to get back Post object and settings so I did the following inside the repository

@Query("""
    select new com.example.dto.PostWithSettingsDTO(p, us.settings) 
    from Post p
    LEFT JOIN FETCH p.comments c
    LEFT JOIN UserSettings us
    ON p.userId = us.userId
    where us.settings= 'ALL'
""")
fun findPostsWithSettings(
    
): List<PostWithSettingsDTO>

data class PostWithSettingsDTO(
    val post: Post,
    val settings: String
)

When I did that, I got the
error

 org.hibernate.query.SemanticException: Could not interpret path expression 'us.settings'

It did work with the WHERE instead of LEFT join but I need the LEFT join.

2

Please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a new ticket in our issue tracker and attach that reproducer.

3

UserSetting object is defined the alias as us meanwhile on clause (where) you are using a up alias so this throw the semantic exception

4

It’s a typo when I typed the code. it’s actually “us” in both and I am getting the error (i corrected my question).
It’s like the UserSettings table columns are not accessible. Even when I removed it from the where clause and tried to get a column it still fails.

5

It seems that you are using Spring Data JPA. Did you tried to define your query using JPA/Hibernate @NamedQuery annotation, and then in Spring Data repository us @Query(name="yourQuery") ? I have some recollections that Spring Data is using its own query parser, and that it might be not so good as one in Hibernate.

I’ve created test case with your classes and query, and it is working without problems. [Only change is that settings in PostWithSettingsDTO should be of type Setting, not String.] Query created seems OK

    select
        p1_0.id,
        c1_0.post_id,
        c1_0.id,
        c1_0.review,
        p1_0.content,
        p1_0.userId,
        us1_0.settings 
    from
        Post p1_0 
    left join
        Comment c1_0 
            on p1_0.id=c1_0.post_id 
    left join
        UserSettings us1_0 
            on p1_0.userId=us1_0.userId 
    where
        us1_0.settings='ALL'