0

I got a very basic query which gets a top 10 of searched words:

select distinct keyword, sum (amount) as keyword_amount from search group by keyword order by keyword_amount DESC

This works fine in PostGresAdmin 4, I get a top 10. But when I want to test it on Spring via Postman on a endpoint, it keeps giving me 500 errors. It says ID is not found and/or should be in the group by or used in an aggregate function. I found in other topics I need to use 'distinct' or put ID in min/max(?). Still no success, and it's weirder that it does work in PostGreSQL itself.

I tried:

@Query( value = "select distinct keyword, sum (amount) as keyword_amount from search " +
        "group by keyword order by keyword_amount DESC",
        nativeQuery = true
)
List<Search> findTop10Keywords();

which give me the missing ID errors, and I tried without nativeQuery, but then I get:

search is not mapped [select distinct keyword, sum (amount) as keyword_amount from search group by keyword order by keyword_amount DESC

I'm not sure why/where search must be mapped?

Ronald v. E.
  • 25
  • 1
  • 5

1 Answers1

0
SELECT keyword, sum(amount) AS keyword_amount
FROM   "search"  -- ?
GROUP  BY keyword
ORDER  BY keyword_amount DESC NULLS LAST
LIMIT  10;

Since you already GROUP BY keyword, adding distinct is an expensive no-op. Remove it.

sum(amount) might be NULL. If that can happen (depends on undisclosed details), be sure to use ORDER BY keyword_amount DESC NULLS LAST. See:

Seems safe to assume that a query called indTop10Keywords should only return the top 10. So LIMIT 10.

"search" is a reserved word in standard SQL (even if allowed as identifier in Postgres). Maybe that's what throws off Spring. Double-quoting may help. But I would rather avoid reserved words as identifiers to begin with.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Didn't think of the reserved keyword, i changed it to 'search_terms'. But with the code above Spring tells me the following: At least 1 parameter(s) provided but only 0 parameter(s) present in query. What is it referring to? The keyword_amount? The method it uses is: List findTop10Keywords(); but this does not uses a param either. I'm confused. – Ronald v. E. May 30 '21 at 12:07
  • Don't know much about Spring, but the error message clearly says you are providing a parameter while the query does not use one. So call it without passing aparameter. – Erwin Brandstetter May 31 '21 at 05:41
  • I found the solution here: https://www.baeldung.com/jpa-queries-custom-result-with-aggregation-functions. Need to make an Interface or replace with . But it won't accept LIMIT 10. But as far as I can find I need to add 'nativeQuery=true' – Ronald v. E. May 31 '21 at 17:32