[SOLVED] Combined join to return data from two tables

Combined join to return data from two tables

I'm learning QueryDSL and have a basic question. I'm trying to do a join of two tables and select data from both.

In my example, I have a Member table and Book table. Book has a foreign key memberId back to member.id. (Think of it as people who own books.)

In SQL, I might do:

SELECT member.name, book.title FROM member, book WHERE book.member_id = member.id;

I'm trying to do the same thing from QueryDSL. This is my code, which is clearly wrong:

List<Tuple> results = queryFactory
        .selectFrom(member)
        .innerJoin(member.books, book)
        .select(member.id, member.name, book.name)
        .fetchJoin()
        .fetch();

I get this error:

org.hibernate.query.SemanticException: query specified join fetching, but the owner of the fetched association was not present in the select list [SqmListJoin (Member(member1).books(book))]

All the examples I've found show how to do joins in order to filter the results. For instance, Books joined with member to get books based on member.name. But they only return books, not a combined join.

Solution

It looks like you're close, but you are using .select() twice, which is causing the issue. In QueryDSL, when you use fetchJoin(), it expects the owner of the fetched association to be present in the select list. Here's how you can try to fix the issue:

List<Tuple> results = queryFactory
        .select(member.id, member.name, book.title) // Include book.title in the select list
        .from(member)
        .innerJoin(member.books, book)
        .fetchJoin()
        .fetch();