I have 2 classes AccountsOrders and Invoices, representing 2 tables, which are connected with a one to many relationship. One Invoice can contain several AccountOrders.
I have a view with a table to display all the AccountOrders so that I can select to create an invoice for the ones I want.
What I’m trying to do is filter out of the table all the AccountOrders that are already in the Invoices table.
Below are the parts from the 2 Entity Classes for the OneToMany relationship.
AccountOrders
@OneToMany(cascade = CascadeType.ALL, mappedBy = "accOrderId")
private Collection<Invoices> invoicesCollection;
Invoice
@JoinColumn(name = "acc_orders_id", referencedColumnName = "id")
@ManyToOne(optional = false)
private ReservationsAccountOrders accOrderId;
And this is the container I create to populate the table
EntityManager em = JPAContainerFactory.createEntityManagerForPersistenceUnit(HelpConstants.PERSISTENT_UNIT_RESERVATIONS);
JPAContainer<AccountOrders> container;
container = JPAContainerFactory.make(AccountOrders.class, em);
At the moment I’m taking a very brute force approach. I get a List of the AccountOrders IDs in the Invoices table, using a query, iterate the results and apply a Not Equal filter for each one.
List<Integer> invoicedRecords = em.createNativeQuery("SELECT i.account_orders_id FROM invoices i INNER JOIN account_orders o ON i.account_orders_id=o.id").getResultList();
if (invoicedRecords.size() > 0) {
for (Integer id : invoicedRecords) {
container.addContainerFilter(new Not(new Compare.Equal("id", id)));
}
}
It works, but there has to be a way to use that join column to filter the container