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 o.properties 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!
Order.java
@Entity
@Table(name = "orders")
@JsonIgnoreProperties(ignoreUnknown = true,
value = {"progress"})
public class Order {
@Id
@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;
@Column
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
Property.java
@Entity
@Table(name = "properties")
@JsonIgnoreProperties(ignoreUnknown = true,
value = {"progress"})
public class Property
{
@Id
@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;
@Column
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<>();
@ManyToOne
@JoinColumn(name = "order_id")
@JsonIgnore
private Order order;
@Column(name = "order_id", insertable = false, updatable = false)
private String orderid;
Getter & Setter
}
Document .java
@Entity
@Table(name = "documents")
public class Document {
@Id
@Column(name="id")
private String docid;
@Column(name="name")
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;
@Column(name="fees")
private double fees;
@ManyToOne
@JoinColumn(name = "property_propertyid")
@JsonIgnore
private Property property;
@Column(name = "property_propertyid", insertable = false, updatable = false)
private String propertyid;
Getter & Setter
}
Solution
Perhaps you can map @ManyToOne
Order to your Document
entity and use it for the Order
entity
@JoinColumnOrFormula(formula = @JoinFormula("(select d.id from documents d WHERE d.order_id = id AND d.received = false"))
List<Document> unreceivedDocuments;