Search code examples
javapostgresqlhibernatehql

Hibernate HQL select distinct from where order by query

I am updating a project to explicitly know about the customer, where previously the customer was assumed, because we only had one...

My problem is with adding a where clause to my HQL queries.

My starting point is this query:

  public static final String SELECT_DISTINCT_STORES =
        "select DISTINCT e.storeNum, e.city, e.state from BoEngagement e order by e.storeNum";

I want to add a where e.customer_fk = :customer_fk clause, but every time I add the where clause I get various org.hibernate.hql.internal.ast.QuerySyntaxException errors, unless I take out the distinct keyword, but then I'm not convinced that the query will give me what I am expecting.

This works:

   "select e.storeNum, e.city, e.state from BoEngagement e WHERE e.customer_fk = :customer_fk";

And, if I'm going to simplify the query that much, it should really be

"select e from BoEngagement e WHERE e.customer_fk = :customer_fk";

However, like I said I am not convinced that dropping the distinct keyword is what I want to do.

Here are some things I have tried:

   "select DISTINCT e.storeNum, e.city, e.state FROM BoEngagement e WHERE e.customer_fk = :customer_fk order by e.storeNum";

gives this error

[ERROR] 2019-10-18 15:10:03.449 [main] BsRetrieveDistinct - java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: FROM near line 1, column 94 [SELECT DISTINCT e.city, e.state from com.bh.radar.bo.BoEngagement e order by e.state, e.city FROM com.bh.radar.bo.BoEngagement e WHERE e.customer_fk = :customer_fk]
java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: FROM near line 1, column 94 [SELECT DISTINCT e.city, e.state from com.bh.radar.bo.BoEngagement e order by e.state, e.city FROM com.bh.radar.bo.BoEngagement e WHERE e.customer_fk = :customer_fk]

and this more complicated version

   "select DISTINCT e.storeNum, e.city, e.state FROM BoEngagement e in " +
        "(select g FROM BoEngagement g WHERE g.customer_fk = :customer_fk order by g.storeNum)";

gives this error

[ERROR] 2019-10-18 15:08:14.317 [main] BsRetrieveDistinct - java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: in near line 1, column 81 [select DISTINCT e.storeNum, e.city, e.state FROM com.bh.radar.bo.BoEngagement e in (select g FROM com.bh.radar.bo.BoEngagement g WHERE g.customer_fk = :customer_fk order by g.storeNum)]
java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: in near line 1, column 81 [select DISTINCT e.storeNum, e.city, e.state FROM com.bh.radar.bo.BoEngagement e in (select g FROM com.bh.radar.bo.BoEngagement g WHERE g.customer_fk = :customer_fk order by g.storeNum)]

Obviously I do not fully understand HQL, and the distinct keyword. What am I doing wrong?

Edit:

@JB Nizet commented that based on the error that I posted he doesn't think I am running the query that I posted. I absolutely am running that query. My puzzlement is why the HQL that I wrote is generating two FROM clauses in the SQL. I have pasted a screenshot below showing the relevant code and error message.

Screen shot of code and error message

Solution

  • Maybe this is not an answer to my original question, but searching the internet for days and posting my question here did not turn up anything that would generate a sane PostgreSQL query from the various JPQL strings I tried, so I stumbled on this blog post: The Many Faces of DISTINCT in PostgreSQL

    And from there started researching the EntityManager.createNativeQuery method, and come up with this PostgreSQL query that works:

      public static final String SELECT_DISTINCT_STORES =
         "SELECT DISTINCT ON(storeNum, city, state) * FROM radar2.engagement WHERE customer_fk = :customer_fk ORDER BY storeNum asc, city, state";

    and I use it thus:

        results = em.createNativeQuery(SELECT_DISTINCT_CITY_STATES, BoEngagement.class)
                .setParameter("customer_fk", customer_fk)
                .getResultList();

    Now, this solution is specific to PostgreSQL, but that is the database I am using now and for the indefinite future.