Java – JPQL returns all associated entities

JPQL returns all associated entities… here is a solution to the problem.

JPQL returns all associated entities

I’m trying to get an Order[] array that includes all orders that haven’t received the relevant documentation.

I tried this query and it returned the correct number of rows.

 @Query("Select o FROM Order o INNER JOIN p  INNER JOIN p.documents  d WHERE d.received = false")
    Order[] findUnreceivedOrders();

The problem is that the order object in my array not only includes all documents that were not received, but I want the object to include only document objects that are not received.

Does anyone know how to fix it?

Thanks for your help!

@Table(name = "orders")
@JsonIgnoreProperties(ignoreUnknown = true,
        value = {"progress"})
public class Order {

    @Column(name = "id", unique = true)
    private String orderid;
    @Column(name = "user_id")
    private String userid;
    @Column(name = "entrydate")
    private java.sql.Date entrydate;
    @Column(name = "info")
    private String info;
    private boolean complete;
    @Column(name= "cached")
    private boolean cached;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "order")
    private List<Property> properties = new ArrayList<>();

@OneToOne(mappedBy = "order", cascade =  CascadeType.ALL)
    private BillingAdress billingAdress;

 Getter & Setter

@Table(name = "properties")
@JsonIgnoreProperties(ignoreUnknown = true,
        value = {"progress"})
public class Property
    @Column(name = "propertyid", unique = true )
    private String id;
    @Column(name = "name")
    private String name;
    @Column(name = "street")
    private String street;
    @Column(name = "zip")
    private String zip;
    @Column(name = "town")
    private String town;
    private String house_number;

@OneToMany(cascade = CascadeType.ALL, mappedBy = "property")
    private List<Landregisterfolio> landregisterfolios = new ArrayList<>();

@Column(name = "userid" )
    private String userid;

@OneToMany(cascade = CascadeType.ALL, mappedBy = "property")
    private List<Document> documents = new ArrayList<>();

    @JoinColumn(name = "order_id")
    private Order order;

@Column(name = "order_id", insertable = false, updatable = false)
    private String orderid;

Getter & Setter

Document .java

@Table(name = "documents")
public class Document {

    private String docid;
    private String docname;
    @Column(name = "received")
    private Boolean received;
    @Column(name = "requested")
    private Boolean requested;
    @Column(name ="last_contact")
    private Date lastContact;
    @Column(name ="intern_comment")
    private String intern_comment;
    @Column(name ="extern_comment")
    private String extern_comment;
    private double fees;

    @JoinColumn(name = "property_propertyid")
    private Property property;

@Column(name = "property_propertyid", insertable = false, updatable = false)
    private String propertyid;

Getter & Setter



Perhaps you can map @ManyToOne Order to your Document entity and use it for the Order entity

@JoinColumnOrFormula(formula = @JoinFormula("(select from documents d WHERE d.order_id = id AND d.received = false"))
List<Document> unreceivedDocuments;

Related Problems and Solutions