JPQL Constructor ignoring Rows whose have a NULL value

Viewed 237

I have following statement:

@Query("SELECT "
        + "new com.app.model.RestaurantOrderPartial( "
            + "o.orderId, o.orderedAt, o.orderType, o.orderState, o.orderValue, o.deliveryPrice, o.deliveredBy, o.driver) "
        + "FROM Order o "
        + "WHERE o.restaurant.restaurantId = ?1 "
        + "AND o.orderedAt BETWEEN ?2 and ?3 "
        + "ORDER BY o.orderedAt DESC ")
List<RestaurantOrderPartial> getRestaurantOrdersCompressed(long restaurantId, LocalDateTime dateBeforePeriod, LocalDateTime now); //12 Month

The rows in table where o.driver is NULL, the constructor doesn´t contruct the entry to an object and doesn´t include it in the result list.

Why does it have this behavior? and how let the constructor include the entries where Driver is null, in the custom object RestaurantOrderPartial the Driver object would be null according to value in Column.

Here are my POJOs:

public class RestaurantOrderPartial {
    
    private long orderId;
    private LocalDateTime orderedAt;
    private OrderType orderType;
    private OrderState orderState;
    private Long orderValue;
    
    
    private int deliveryPrice;
    private DeliveredBy deliveredBy;
    private Driver driver;

}

public class Driver {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long driverId;

@Column
private String email;

@Column
private String password;

@Column
private String fullName;

@Column
private String phoneNumber;

@Column
private String secondaryPhoneNumber;

@Column
private String fullAddress;

@Column
private boolean isActive;

@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "cityId")    
private City city;

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "locationId")
private Location location;

@JsonIgnore
@OneToMany(mappedBy="driver")
private List<Order> orders;

Sample Data: sample data

Thanks for your help.

1 Answers

If you want o.driver to be fetched if it is null, you must specify a left outer join; o.driver is always an inner join and so filters out null values from the results.

Try something more like

"SELECT "
        + "new com.app.model.RestaurantOrderPartial( "
            + "o.orderId, o.orderedAt, o.orderType, o.orderState, o.orderValue, o.deliveryPrice, o.deliveredBy, driver) "
        + "FROM Order o LEFT OUTER JOIN o.driver driver"
        + "WHERE o.restaurant.restaurantId = ?1 "
        + "AND o.orderedAt BETWEEN ?2 and ?3 "
        + "ORDER BY o.orderedAt DESC ")
Related