openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Merrill <ch...@webperformance.com>
Subject Query syntax question about COUNT(x) and GROUP BY
Date Thu, 19 Aug 2010 12:57:54 GMT
Hi all!  I've been using OpenJPA and Derby for a small app and so far everything is
going very well.  It's been quite a while since I've been in the Java/database world
and it is great to see how well it has progressed and how the drudgery of O/R mapping
has been greatly reduced!

I've got a query that I need execute COUNT(x) version of first, so that I can page
the results correctly.  In the example below, we have Customers in our database
as well as Addresses. A customer may have more than one Address. The relationship
is maintained only on the Address side (i.e. Addresses have a reference to
the Customer, but Customers do not directly refer to their Addresses).  This
query is looking for all Customers that have "Main" in the street of one of
their addresses.  The GROUP BY is there to remove duplicates (we don't want
a customer to show up twice, if they have 2 addresses with "Main" in the
street). The query works perfect (AFAICT):

SELECT x._customer FROM Address x
WHERE LOWER(x._street) LIKE '%Main%'
GROUP BY x._customer
ORDER BY x._customer._last_activity DESC

But my COUNT(x) version of the query:

SELECT COUNT(x._customer) FROM Address x
WHERE LOWER(x._street) LIKE '%Main%'
GROUP BY x._customer
(note that the ORDER BY is removed)

generates this error:
org.apache.openjpa.persistence.NonUniqueResultException: The query on candidate type "class
com.webperformanceinc.Address" with filter "<query from above>" was configured to have
a
unique result, but more than one instance matched the query.

I execute the query with this line:
    return ((Long) query.getSingleResult()).intValue();

I think I understand the error - If I look at the result list that comes
back from query.getResultList() instead, it looks like the query is returning
a list containing the count of the Addresses matched for each Customer.

I'm at a loss for a way to write the query to get what I need.  I should note that my
actual query is a little bit more complex - the WHERE clause has several parts as we are
searching several fields in both the Customer and the Address all at once.

I've looked through a lot of JPA docs and articles from various sites (which is how I got
this far!), but everything I have found on the COUNT() keyword shows trivial examples.

Does anyone have an idea on how to write the query or perhaps a more appropriate forum
for asking this question?

TIA!
Chris


-- 
------------------------------------------------------------------------ -
Chris Merrill                           |  Web Performance, Inc.
chris@webperformance.com                |  http://webperformance.com
919-433-1762                            |  919-845-7601

Web Performance: Website Load Testing Software & Services
------------------------------------------------------------------------ -

Mime
View raw message