left join not in JPA query when sorting by the same property is applied #2076

Open
MaciejG opened this Issue Jan 4, 2017 · 0 comments

Projects

None yet

1 participant

@MaciejG
MaciejG commented Jan 4, 2017 edited

We have following entities:

@Entity
@Table(name = "PRICERECORD")
@Data
@EqualsAndHashCode(of = {"id"})
public class PriceRecordBean implements PriceRecord, Serializable {

    @Id
    @SequenceGenerator(name = "PRICERECORD_ID_GENERATOR", sequenceName = "PRICERECORD_SEQ", allocationSize = 100)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PRICERECORD_ID_GENERATOR")
    @Column(unique = true, nullable = false, precision = 22)
    private Long id;

    @ManyToOne(targetEntity = UploadedFileBean.class)
    @JoinColumn(name = "uploaded_file_id", referencedColumnName = "id")
    private UploadedFile uploadedFile;

    @ManyToOne(targetEntity = StatusBean.class)
    @JoinColumn(name = "status_id", referencedColumnName = "id")
    private Status status;

    @ManyToOne(targetEntity = MarketBean.class)
    @JoinColumn(name = "market_id", referencedColumnName = "id")
    private Market market;

    @ManyToOne(targetEntity = ModelBean.class, fetch = FetchType.EAGER)
    @JoinColumn(name = "model_id", referencedColumnName = "id")
    private Model model;

    @ManyToOne(targetEntity = ModelOptionBean.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "model_option_id", referencedColumnName = "id")
    private ModelOption modelOption;

    @ManyToOne(targetEntity = ModelOptionBean.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "COMBI1_ID", referencedColumnName = "ID")
    private ModelOption combi1;

    @ManyToOne(targetEntity = ModelOptionBean.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "COMBI2_ID", referencedColumnName = "ID")
    private ModelOption combi2;

    @ManyToOne(targetEntity = YearBean.class)
    @JoinColumn(name = "YEAR_ID", referencedColumnName = "ID")
    private Year year;

    @Column(length = 3, name = "pricegroup", nullable = false)
    private String priceGroup;

    @Column(name = "pricetype", nullable = false)
    private Long priceType;

    @Column(name = "validfrom", nullable = false)
    private Long validFrom;

    @Column (length = 500, name = "status_message")
    private String statusMessage;

    @Column(precision = 12, scale = 2, nullable = false)
    private BigDecimal price;

    @Column(nullable = false)
    private Long version;
}

and

@Entity
@Table(name = "MODEL_OPTION")
@Data
@EqualsAndHashCode(exclude = {"id"})
public class ModelOptionBean implements ModelOption, Serializable {

    @Id
    @SequenceGenerator(name = "MODEL_OPTION_ID_GENERATOR", sequenceName = "MODEL_OPTION_SEQ", allocationSize = 100)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "MODEL_OPTION_ID_GENERATOR")
    @Column(unique = true, nullable = false, precision = 22)
    private Long id;

    @Column(nullable = false)
    private String name;

}

The problem we encounter when using QueryDsl is when we try to sort the list of price beans by modelOptionBean.name and at the same time require that modelOption should be null. The resulting JPA query should contain left join to modelOption. Id does so if only the soring is applied. If we set where.and(QPriceRecordBean.priceRecordBean.modelOption.isNull()); the resulting JPA query is left without join.

After short investigation I recognised when only sort is applied without the additional condition the "shorten" method in JPAQueryMixin is called which introduces the join for bean based sort properties. The problem is that if the additional condition for the same property (or rather its parent) is added this method is never called.

I know that it may sound strange that we sort by "priceRecordBean.modelOption.name" property and then require that "priceRecordBean.modelOption" property should be null but this is how our UI is allowed to work. User may sort by this column and at the same time filter it so that it should be null.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment