0

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?

membersound
  • 81,582
  • 193
  • 585
  • 1,120

1 Answers1

-1

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.

Jan-Willem Gmelig Meyling
  • 3,685
  • 2
  • 21
  • 43
  • `QBooking.booking.editor.isNull()` generates the sql: `booking.id is null` So the primary key of my main row is checked, instead of the reference id. I'm using latest frameworks. How can I know for which framework I'd have to create a bug report? For querydsl, or for hibernate? – membersound Jun 21 '21 at 08:12
  • Which Hibernate version do you use? I think on 5.5.0.Final, you should be able to work around this issue by annotating `editor` with `@OneToOne(optional = true)`. You can verify whether QueryDSL or Hibernate is to blame by looking at the generated JPQL query. If the JPQL query generated by QueryDSL looks fine, and still fails on Hibernate then its obviously Hibernate to blame. You can convert QueryDSL queries to JPQL using the JPQLSerializer and you can execute plain JPQL query strings in Hibernate through `entityManager.createQuery`. – Jan-Willem Gmelig Meyling Jun 21 '21 at 08:19
  • `optional = true` is the default already, so no changes with it. – membersound Jun 21 '21 at 08:24
  • I have to correct: hibernate sql checks `QBooking.booking.editor.id.isNull()` generates `and booking.editor.id is null`, while `QBooking.booking.editor.isNull()` generates `and booking.editor is null`. Which is both wrong, because the reference is on the `editor` table, and thus cannot be found out without a cross join. – membersound Jun 21 '21 at 08:27
  • `QBooking.booking.editor.id.isNull()` in Querydsl translates to `booking.editor.id IS NULL` in JPQL/HQL. And `booking.editor.isNull()` translates to `booking.editor IS NULL`. In the eventual SQL, both framents should render a LEFT join instead of an INNER/CROSS join for non-optional associations, but `@OneToOne` mappings have suffered from a few bugs in particularly the Hibernate 5.3.x version range. Therefore I am wondering if Hibernate is used and if it is, which version of Hibernate. – Jan-Willem Gmelig Meyling Jun 21 '21 at 11:16
  • `hibernate-core 5.4.30.Final` – membersound Jun 22 '21 at 11:47