0

I'm new to JPA and JPQL, so forgive me is this is simple an I'm simply missing something small.

Assume that I have the following (T-SQL):

CREATE TABLE tblCustomer (id int IDENTITY(1,1) NOT NULL, CustName nvarchar(50) NOT NULL, CONSTRAINT tblCustomer_PK PRIMARY KEY NONCLUSTERED (id ASC));
INSERT INTO tblCustomer (CustName) VALUES ('McDonalds');
INSERT INTO tblCustomer (CustName) VALUES ('Lowes');

CREATE TABLE tblOrder (id int IDENTITY(1,1) NOT NULL, custID int NOT NULL, status nvarchar(10) NOT NULL, CONSTRAINT tblOrder_PK PRIMARY KEY NONCLUSTERED (id ASC));

I have entities defined (tblCustomer <--> Customer, tblOrder <--> Order).

I want to do the equivalent of:

SELECT tblCustomer.id, tblCustomer.CustName, COUNT(tblOrder.id) FROM tblCustomer LEFT OUTER JOIN tblOrder ON tblCustomer.id = tblOrder.custID WHERE tblOrder.status <> 'Closed' GROUP BY tblCustomer.id, tblCustomer.CustName

but in JPA / JPQL.

I've looked at: How do I count the number of rows returned by subquery? and several other StackOverflow and other questions.

I'm either ending up with a count of all orders (i.e. tblOrder.status <> 'Closed' not being applied), or I'm ending up with a count of all closed orders without the join being applied.

Any thoughts?

Community
  • 1
  • 1
Dominic
  • 1
  • and what part of that query is hard to replicate in JPQL? There is direct read across from it to JPQL from what I see, nothing controversial in there – Neil Stockton Feb 16 '16 at 18:38
  • @NeilStockton ok; so I wrote this: `SELECT c.id, c.custName COUNT(o.id) FROM Customer c LEFT OUTER JOIN Order o WHERE o.status != 'Closed' GROUP BY c.id, c.custName` and it returns the total count of orders, not the count of open orders as is needed (i.e. o.status != 'Closed' is being ignored). – Dominic Feb 16 '16 at 19:43
  • Sorry in the above example it's actually ignoring the join, so each customer gets the total count of open orders. – Dominic Feb 16 '16 at 19:52
  • If I add the join explicitly `... Customer c LEFT OUTER JOIN Order o ON c.id = o.custID ...` then I get a QueryException with the description Exception Description: Object comparisons can only be used with OneToOneMappings. Other mapping comparisons must be done through query keys or direct attribute level comparisons. – Dominic Feb 16 '16 at 19:54
  • I think I'm getting closer. I reviewed [this question](http://stackoverflow.com/questions/20288567/jpa-join-tables-through-query) and believe it should maybe be something like `... FROM Customer c LEFT OUTER JOIN c.orders o ...` but with that setup it ignored the LEFT OUTER part of the join, i.e. it only return customers with open orders. – Dominic Feb 16 '16 at 19:59

0 Answers0