CODEWITHSUNDEEP
javasqlspringspring-data-jpaquerydsl
membersound
membersound

Reputation: 86875

How to use QueryDsl with @PrimaryKeyJoinColumn?

I want to create a querydsl Predicate to use it with spring-jpa CrudRepository.

Problem: I have an optional @PrimaryKeyJoinColumn that references to the child entity by @Id PK. And I want to select only the rows where the child table does not contain a reference with the main row.

In this example: I only want to select bookings in status = 2, and where no editor reference exists inside the child table.

BooleanBuilder booleanBuilder = new BooleanBuilder(predicate)
        .and(QBooking.booking.status.eq(2))
        .and(QBooking.booking.editor.id.isNull());
                
dao.findAll(booleanBuilder.getValue()); //dao is QuerydslPredicateExecutor<Booking>


class Booking {
    @Id
    long id;
    
    String status;
    
    //joins the table by their PK id
    @PrimaryKeyJoinColumn
    public Editor editor;
}

class Editor {
    @Id
    long id;
    
    ...
}

The sql generated from it is:

select * from booking
    CROSS JOIN editor
    WHERE booking.id = editor.id
    and booking.status = ?
    and editor.id is null;

But this is wrong (as of course this never returns any result)! What I'm looking for is:

select * from booking
    CROSS JOIN editor
    WHERE (booking.id = editor.id OR editor.id is null)
    and booking.status = ?;

How can I achieve the later sql statement with querydsl?

Upvotes: 0

Views: 199

Answers (1)

Jan-Willem Gmelig Meyling
Jan-Willem Gmelig Meyling

Reputation: 3913

You should be able to workaround the issue by using QBooking.booking.editor.isNull() instead, which is perfectly valid to do in JPQL. However, ID dereference should have just worked here, so you're probably looking at a bug in your ORM implementation. Probably upgrading to the latest version of Hibernate will resolve your issue as well.

Upvotes: -1

Related Questions