Re: Using subquery field in FROM and GROUP BY

[Timo Westkämper]

Hi.

On Mon, Jul 16, 2012 at 9:05 PM, Querydsl on behalf of SteveG <quer...@googlegroups.com> wrote:

Hi,

I'm just getting started with QueryDSL, using it to replace some legacy queries we have.  I have a query of the form below that I'm trying to replace:

---
SELECT
  t1.field1,t1.field2,aliasField,COUNT(*)
FROM
  SomeTable t1 LEFT OUTER JOIN (
    SELECT parentPk,(
      CASE
      WHEN someSubField between 0 and 50 THEN '0-50% Low'
      WHEN someSubField between 51 and 75 THEN '51-75% Medium'
      WHEN someSubField between 76 and 100 THEN '76-100% High'
      ELSE NULL
      END)
      AS aliasField
    FROM SomeSubtable t3)
  AS subqueryResults ON (t1.pk = subqueryResults.parentPk)
WHERE
  f1.field1 = 1 AND t1.field2 = 'A'
GROUP BY
  t1.field1,t1.field2,aliasField
---

The tricky part here is the aliasField, used as a selection source, as the alias of the CASE statement in the subquery, and as one of the group-by fields.  I haven't been able to massage the right syntax out, and had a couple of questions I was hoping could get me back on track:

 - As I understand it, the above can't be expressed in JPQL right?  (Since there is a join occurring as part of the FROM?)

Correct.
 

 - How can we use aliasField in the main query's source?  I've been using a StringPath/StringOperation created from Expressions.stringPath/stringOperation() to refer to the field throughout, but can't seem to feed those through the query.from() syntax that requires an EntityPath (I am just using the Maven integration to generate the respective Q-classes, if that matters - do those need to be bugged up to add an annotated column to represent this, or something like that?).

Something like the following should work

query.from(t1)
  .leftJoin(
    sub().from(t3).list(parentPk, caseExpr.as(aliasField)),
    subQueryResults)
  .on(t1.pk.eq(subqueryResults.parentPk))
  .where(f1.field1.eq(1), t1.field2.eq("A"))
  .groupBy(t1.field1, t2.field, aliasField)
  .list(t1.field1, t1.field2, aliasField, Wildcard.count);

Br,
Timo
 

Thanks very much,
Steve

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[SteveG]

Hi Timo,

Thanks very much for the direction.  I must be misunderstanding some detail - the below code will produce the "Undeclared path 'aliasField'.  Add this path as a source..." error when I try to run it.  I also don't have the .on() join specifier method available to chain after leftJoin() (making me think I must be using the wrong query class?), and am not sure what kind of object subQueryResults is in your example.  Sorry to be so dull :) is there something obviously wrong with the below?

Thanks,
Steve

---
        JPAQuery query = new JPAQuery();
        JPASubQuery subquery = new JPASubQuery();
       
        QSomeTable t1 = new QSomeTable("t1");
        QSomeSubtable t3 = new QSomeSubtable("t3");
       
        StringPath aliasField = Expressions.stringPath("aliasField");
       
        query.from(t1)
                .leftJoin(
                    subquery.from(t3).list(
                        t3.parent.pk, new CaseBuilder()
                            .when(t3.someSubField.between(new Integer(76), new Integer(100))).then(HIGH)
                            .when(t3.someSubField.between(new Integer(51), new Integer (75))).then(MED)
                            .when(t3.someSubField.between(new Integer(0), new Integer(50))).then(LOW)
                            .otherwise("null").as(aliasField)))  // Is there a null dropin object to use in place of literal null?
                // No .on() specifier for the join available here?
                .where(t1.field1.eq(1), t1.field2.eq("A"))
                .groupBy(t1.field1, t1.field2, aliasField)
                .list(t1.field1, t1.field2, aliasField, Wildcard.count);
---

On Monday, July 16, 2012 4:19:48 PM UTC-4, Timo Westkämper wrote:

Hi.

[Timo Westkämper]

Hi.

You are trying to express an SQL query using the query classes from the JPA side.

Please use the classes mentioned in this tutorial for SQL queries : http://www.querydsl.com/static/querydsl/2.7.0/reference/html/ch02s03.html

Br,
Timo

[SteveG]

Hi Timo,

Thanks very much.  We make use of the JPA processor to produce our Q-classes from JPA-annotated entities, and we've been able to issue the correct kinds of JPQL queries up to this point for other queries.  I take it that if we were to use the SQL generation, we would need to generate a new set of Q classes for this particular query using the sql-codegen module - is it not possible to reuse the Q classes generated from the JPA-annotated entities in some way for these SQL queries (is there some set of annotations we could specify that would allow the SQL to marshal correctly, or is there some way to run sql-codegen against these annotated classes and not against a live database)?  We could definitely do this if we needed to, but it seems a shame to have to duplicate the database model representations just for this one dumb query :)

Thanks,
Steve

[Timo Westkämper]

Hi.

On Wed, Jul 18, 2012 at 10:32 PM, Querydsl on behalf of SteveG <quer...@googlegroups.com> wrote:

Hi Timo,

Thanks very much.  We make use of the JPA processor to produce our Q-classes from JPA-annotated entities, and we've been able to issue the correct kinds of JPQL queries up to this point for other queries.  I take it that if we were to use the SQL generation, we would need to generate a new set of Q classes for this particular query using the sql-codegen module -

Yes, that's the deal.
 

is it not possible to reuse the Q classes generated from the JPA-annotated entities in some way for these SQL queries (is there some set of annotations we could specify that would allow the SQL to marshal correctly, or is there some way to run sql-codegen against these annotated classes and not against a live database)?

Not really. The Q-types for the JPA types encode class and property information and the Q-types for SQL schemas encode table and column information.
 

  We could definitely do this if we needed to, but it seems a shame to have to duplicate the database model representations just for this one dumb query :)

You are right about that. Feel free to create a ticket for it.

I don't have yet a bulletproof solution in mind, but maybe the JPA 2 Metamodel could be used to map classes and properties to tables and columns.

Br,
Timo