1

I have the following optional, to-many relationship: PackingSlip <->> LineItem

I need to match all the PackingSlip instances that do not have any related LineItem instances with an qtyOrdered > qtyShipped.

What makes the most sense to me would be to write an expression along the lines of:

PackingSlip.LINE_ITEMS.containsMatch(LineItem.QTY_ORDERED.lt(LineItem.QTY_SHIPPED)).notExp();

Which I would expect to generate SQL along the lines of:

SELECT t0.id, ... FROM packing_slip t0
WHERE NOT ( 
  EXISTS ( 
    SELECT * FROM line_item t1 
    WHERE t1.packing_slip_id = t0.id 
    AND t1.qty_ordered < t1.qty_shipped
  )
)

Obviously, I've made up the containsMatch(Expression) method. Since such a thing does not exist (currently), what is the best way of accomplishing this in Cayenne 4.0?

David Avendasora
  • 4,538
  • 1
  • 16
  • 15

1 Answers1

1

While I am not a big fan of EJBQLQuery, it can actually help here:

String ejbql = 
    "SELECT ps FROM PackingSlip ps " +
    "WHERE NOT EXISTS " +
    "(SELECT li FROM LineItem li WHERE li " +
    "MEMBER OF ps.lineItems AND li.qtyOdered < li.qtyShipped)";

EJBQLQuery query = new EJBQLQuery(ejbql);
List<PackingSlip> objects = context.performQuery(query);

As well as SQLSelect query.

andrus_a
  • 2,528
  • 1
  • 16
  • 10
  • Thanks @andrus_a. EJBQL has always struck me as effectively the same as manually writing SQL. It carries with it many of the same downsides that I'm using an ORM like Cayenne to avoid! EJBQL (just like hard-coded SQL) can easily get out of sync with the database. For example, there's no compiler watching my back to tell me that `LineItem` was renamed to `OrderItem`. It's all on me, and as anyone can tell you, that's not a recipe for bug-free code. – David Avendasora May 11 '17 at 18:07