You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Chris Merrill <ch...@webperformance.com> on 2010/08/19 14:57:54 UTC
Query syntax question about COUNT(x) and GROUP BY
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
------------------------------------------------------------------------ -
Re: Query syntax question about COUNT(x) and GROUP BY
Posted by Chris Merrill <ch...@webperformance.com>.
On 8/20/2010 9:30 AM, Pinaki Poddar wrote:
>
> Try a subquery such as
>
> String countQuery = "select count(c) from Customer c "
> + "where exists (select a FROM Address a
> "
> + "where a.customer
> = c and lower(a.city) like :city)";
Excellent! That did the trick. I don't know if I've ever done a sub-query
before (my SQL days were long, long ago!). My actual query is much more complex,
but I got it working after only ~15 tries ;)
> One suggestion: prefer query parameters over fixed strings. Lots of
> advantages.
Agreed. I'm doing that in many places, but probably not as many as I should.
Can you use them anywhere in a query?
For example, can:
SELECT c from Customer c WHERE c.name LIKE :search_param
be changed to this
SELECT c from Customer c WHERE c.:search_field LIKE :search_param
? I assumed not, but perhaps it can be?
In this example, the search-field is also specified by the user...so the field
queried will be potentially different each time.
Thanks! I would have spent many hours discovering that on my own...
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
------------------------------------------------------------------------ -
Re: Query syntax question about COUNT(x) and GROUP BY
Posted by Pinaki Poddar <pp...@apache.org>.
Try a subquery such as
String countQuery = "select count(c) from Customer c "
+ "where exists (select a FROM Address a
"
+ "where a.customer
= c and lower(a.city) like :city)";
One suggestion: prefer query parameters over fixed strings. Lots of
advantages.
-----
Pinaki
--
View this message in context: http://openjpa.208410.n2.nabble.com/Query-syntax-question-about-COUNT-x-and-GROUP-BY-tp5440084p5444496.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Query syntax question about COUNT(x) and GROUP BY
Posted by Chris Merrill <ch...@webperformance.com>.
On 8/19/2010 8:03 PM, Pinaki Poddar wrote:
> Great to note your appreciation of the progress of ORM (some one once
> said: ORM is the Viet Nam of Computer Science:)
Heh heh...I thought that was Artificial Intelligence :>
> Why a separate count(*) needed in this case?
> Is not the number of result rows returned by the first query is the same as
> the number of customers to make any paging decision?
> If that is true, the simply q1.getResultList().size() should obviate the
> COUNT(*) query?
>
> Or have I just got it all wrong?
That would work but it would be inefficient. I have to implement a data provider for
the UI component. It will ask my data provider how many total items there are (thus
the need for the count(*) query) and then it will ask me for items N thru N+20, depending
on where the user navigates to. In theory, there could be tens of thousands of rows, so
fetching them just for the sake of counting them is poor practice and very expensive.
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
------------------------------------------------------------------------ -
Re: Query syntax question about COUNT(x) and GROUP BY
Posted by Pinaki Poddar <pp...@apache.org>.
Hi,
> it is great to see how well it has progressed and how the drudgery of O/R
> mapping has been greatly
> reduced!
Great to note your appreciation of the progress of ORM (some one once
said: ORM is the Viet Nam of Computer Science:)
Why a separate count(*) needed in this case?
Is not the number of result rows returned by the first query is the same as
the number of customers to make any paging decision?
If that is true, the simply q1.getResultList().size() should obviate the
COUNT(*) query?
Or have I just got it all wrong?
-----
Pinaki
--
View this message in context: http://openjpa.208410.n2.nabble.com/Query-syntax-question-about-COUNT-x-and-GROUP-BY-tp5440084p5442654.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.