java - JPQL 返回所有关联的实体

Question

我正在尝试获取 Order[] 数组，其中包括未收到相关文档的所有订单。

我尝试了这个查询，它返回了正确的行数。

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

问题是，我的数组中的订单对象不仅包括未收到的所有文档，而且我希望该对象仅包括未收到的文档对象。

有人知道怎么解决吗？

感谢您的帮助!

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
}

文档.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

}

Answer 1

也许您可以将 @ManyToOne Order 映射到您的 Document 实体，并在用于 Order 实体

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