Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Support for "SELECT ... AS identifier1, ... AS identifier2" in JPQL constructor expressions (to be used by WHERE and/or ORDER BY clauses) #297

Open
kawoolutions opened this issue Nov 16, 2020 · 3 comments
Open

Support for "SELECT ... AS identifier1, ... AS identifier2" in JPQL constructor expressions (to be used by WHERE and/or ORDER BY clauses) #297

kawoolutions opened this issue Nov 16, 2020 · 3 comments

Comments

@kawoolutions
Copy link

kawoolutions commented Nov 16, 2020 •
edited
Loading

I'm trying to use JPQL SELECT NEW org.mydomain.WhatEverDto( SELECT my.expr1 AS identifier, my.expr2 AS identifier, ... ) syntax, that is SELECT ... AS in a constructor expression to later be used in an ORDER BY clause:

  <named-query name="GameLog.findPlayerStatDtos">
    <query>
        SELECT NEW net.bbstats.dto.PlayerStatDto(
          gl.playerId,
          gl.seasonStartYear,
          gl.lastName,
          gl.firstName,
          gl.incognito,
          COUNT(DISTINCT gl.gameId) AS games,                                            <--
          SUM(CASE WHEN gl.hasPlayed = FALSE THEN 1 ELSE 0 END) AS gamesplayed,          <--
          SUM(CASE WHEN gl.starter = TRUE THEN 1 ELSE 0 END) AS gamesstarted,            <--
          SUM(gl.threePointersMade),
          SUM(gl.freeThrowsMade),
          SUM(gl.freeThrowsAttempted),
          SUM(gl.personalFouls),
          SUM(CASE WHEN gl.personalFouls >= 5 THEN 1 ELSE 0 END),
          SUM(gl.points) AS points,                                                      <--
          MAX(gl.points),
          MIN(gl.points)
        )
        FROM GameLog gl
        WHERE gl.roundId = :roundId AND gl.groupCode = :groupCode AND gl.rosterId IN :rosterIds
        GROUP BY
          gl.playerId,
          gl.seasonStartYear,
          gl.lastName,
          gl.firstName,
          gl.incognito
        ORDER BY gamesstarted DESC,                                                      <-- referenced here
                 games DESC,                                                             <-- referenced here
                 points DESC,                                                            <-- referenced here
                 gl.lastName,
                 gl.firstName
    </query>
  </named-query>

A complete example is on SO here:

https://stackoverflow.com/questions/59677453/eclipselink-throws-jpqlexception-on-using-select-as-in-a-constructor-express

The purpose is to select a column for the constructor expression AND be able to sort the resulting elements/DTOs via the ORDER BY clause without having to repeat the (possibly complex) expression there.

Placing an "AS identifier" after the constructor item already works in Hibernate, but fails with an exception in EclipseLink:

at org.eclipse.persistence.exceptions.EntityManagerSetupException.deployFailed(EntityManagerSetupException.java:241)
	... 184 more
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.7.5.v20191016-ea124dd158): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [
            SELECT NEW net.bbstats.dto.PlayerStatDto(
              gl.playerId,
              gl.seasonStartYear,
              gl.lastName,
              gl.firstName,
              gl.incognito,
              COUNT(DISTINCT gl.gameId) AS games,
              SUM(CASE WHEN gl.hasPlayed = FALSE THEN 1 ELSE 0 END) AS gamesplayed,
              SUM(CASE WHEN gl.starter = TRUE THEN 1 ELSE 0 END) AS gamesstarted,
              SUM(gl.threePointersMade),
              SUM(gl.freeThrowsMade),
              SUM(gl.freeThrowsAttempted),
              SUM(gl.personalFouls),
              SUM(CASE WHEN gl.personalFouls >= 5 THEN 1 ELSE 0 END),
              SUM(gl.points) AS points,
              MAX(gl.points),
              MIN(gl.points)
            )
            FROM GameLog gl
            WHERE gl.roundId = :roundId AND gl.groupCode = :groupCode AND gl.rosterId IN :rosterIds
            GROUP BY
              gl.playerId,
              gl.seasonStartYear,
              gl.lastName,
              gl.firstName,
              gl.incognito
            ORDER BY gamesstarted DESC, gamesplayed DESC, games DESC, points DESC, gl.lastName, gl.firstName
        ]. 
[140, 141] The constructor expression has two constructor items ('COUNT(DISTINCT gl.gameId)' and 'AS games') that are not separated by a comma.
[204, 205] The constructor expression has two constructor items ('SUM(CASE WHEN gl.hasPlayed = FALSE THEN 1 ELSE 0 END)' and 'AS gamesplayed') that are not separated by a comma.
[271, 272] The constructor expression has two constructor items ('SUM(CASE WHEN gl.starter = TRUE THEN 1 ELSE 0 END)' and 'AS gamesstarted') that are not separated by a comma.
[462, 463] The constructor expression has two constructor items ('SUM(gl.points)' and 'AS points') that are not separated by a comma.
[141, 149]  The expression is invalid, which means it does not follow the JPQL grammar.
[205, 219]  The expression is invalid, which means it does not follow the JPQL grammar.
[272, 287]  The expression is invalid, which means it does not follow the JPQL grammar.
[463, 472]  The expression is invalid, which means it does not follow the JPQL grammar.
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:157)
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:336)
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:280)
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:165)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
	at org.eclipse.persistence.internal.jpa.JPAQuery.processJPQLQuery(JPAQuery.java:225)
	at org.eclipse.persistence.internal.jpa.JPAQuery.prepare(JPAQuery.java:186)
	at org.eclipse.persistence.queries.DatabaseQuery.prepareInternal(DatabaseQuery.java:631)
	at org.eclipse.persistence.internal.sessions.AbstractSession.processJPAQuery(AbstractSession.java:4502)
	at org.eclipse.persistence.internal.sessions.AbstractSession.processJPAQueries(AbstractSession.java:4462)
	at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.initializeDescriptors(DatabaseSessionImpl.java:645)
	at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.postConnectDatasource(DatabaseSessionImpl.java:868)
	at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.loginAndDetectDatasource(DatabaseSessionImpl.java:811)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryProvider.login(EntityManagerFactoryProvider.java:256)
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:772)
	... 182 more

BNF from the JPA 2.2 spec (see answer on SO):

constructor_item ::=
  single_valued_path_expression |
  scalar_expression |
  aggregate_expression |
  identification_variable
aggregate_expression ::= 
  { AVG | MAX | MIN | SUM} ([DISTINCT] state_valued_path_expression) |
  COUNT ([DISTINCT] identification_variable | state_valued_path_expression
    |single_valued_object_path_expression) |
  function_invocation

The BNF clearly states, that a constructor_item cannot have an optional [AS] result_variable/identifier.

This feature might change the BNF to:

constructor_item ::=
  {single_valued_path_expression |
  scalar_expression |
  aggregate_expression |
  identification_variable} [AS] result_variable                          <-- ?
aggregate_expression ::= 
  { AVG | MAX | MIN | SUM} ([DISTINCT] state_valued_path_expression) |
  COUNT ([DISTINCT] identification_variable | state_valued_path_expression
    |single_valued_object_path_expression) |
  function_invocation

Should be extended to WHERE and other clauses if applicable.

Let me know what you think. 💡
@rhuan080
Copy link

rhuan080 commented Nov 16, 2020 via email

@kawoolutions
Copy link
Author

kawoolutions commented Nov 16, 2020 •
edited
Loading

Did you notice the ORDER BY references and the arrows I put into the query?:

        SELECT NEW net.bbstats.dto.PlayerStatDto(
          ...
          COUNT(DISTINCT gl.gameId) AS games,                                            <--
          ...
          SUM(CASE WHEN gl.starter = TRUE THEN 1 ELSE 0 END) AS gamesstarted,            <--
          ...
          SUM(gl.points) AS points,                                                      <--
          ...
        )
        FROM ...
        WHERE ...
        GROUP BY ...
        ORDER BY gamesstarted DESC,                                                      <-- referenced here
                 games DESC,                                                             <-- referenced here
                 points DESC,                                                            <-- referenced here
                 gl.lastName,
                 gl.firstName

@kawoolutions kawoolutions changed the title Support for "SELECT ... AS identifier1, ... AS identifier2" in JPQL constructor expressions Support for "SELECT ... AS identifier1, ... AS identifier2" in JPQL constructor expressions (to be used by WHERE and/or ORDER BY clauses) Jan 5, 2021
@kawoolutions
Copy link
Author

Examples:

https://stackoverflow.com/questions/7958455/order-by-case-column-in-jpa

@andyjefferson andyjefferson mentioned this issue Apr 11, 2021
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@kawoolutions @rhuan080