Re: Join with subquery in queryDSL without mapped relationship

[Timo Westkämper]

Hi.

Something like the following should work

from(t1)
.join(
  sub().from(t2).where(t2.date.between(...), t2.name.eq(...))
         .groupBy(t2.customerId)
         .list(t2.customerId, kwr.date.max(), kwr.receita.sum()),
  total
)
.on(t1.date.eq(total.date), t1.id.eq(total.customerId))
.orderBy(t1.id)
.limit(1000)
.list(t1.id, t1.name, total.income);

Where does kwr come from?

Br,
Timo

On Mon, Jun 25, 2012 at 11:05 PM, <querydsl+noreply-APn2wQftpsGC-hJ...@googlegroups.com> wrote:

Hi,

We are having some troubles trying to map the following Query in QueryDSL:

select top 1000 t1.id as t1Id, t1.name, total.income
from Table1 t1 with(nolock)
join (
                select t2.customerId, max(kwr.date) as date, sum(kwr.receita) as receita
                from Table2 t2 with(nolock) where t2.date between ? and ? and t2.name = ?
                group by t2.customerId
) total
on (t1.date = total.date) and (t1.id = total.ccustomerId)
order by t1.id

Here we are using dynamic filters and there aren't relationship between Table1 and Table2 mapped in database or in system classes. It is possible to do a join with subquery among tables without a relationship??


Thanks,


Danilo.




Thanks,

Danilo.

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

[Danilo Brandão]

Hi Timo,

Thanks for quick response.

I was cutting the query to make it clear and forgot to substitute kwr by t1.

We are trying to create the query according to your suggestion using JPAQuery.

We are using the following excerpt of code:

  QTable1 t1 = QTable1.table1;
  QTable2 t2 = QTable2.table2;

  JPAQuery query2 = new JPAQuery(entityManager);
  JPASubQuery sub = new JPASubQuery();

  PathBuilder<Object[]> total = new PathBuilder<Object[]>(Object[].class, "total");
  List<Tuple> rows = query2.from(t1)
    .join(sub.from(t2).where(t2.date.between(dateInicial, dateFinal), t2.keyword.eq(keyword))
      .groupBy(t2.customerId)
      .list(t2.customerId, t2.date.max(), t2.receita.sum().as("receita")),
      total
      ).on(t1.date.eq(t2.date), t1.id.eq(t2.customerId))
      .orderBy(t1.id)
      .list(t1.id, t1.campaign, total.get("receita"));

Trying this we are getting the error:  "The method on(BooleanExpression, BooleanExpression) is undefined for the type JPAQuery"

Is not possible to use alias with JPAQuery? What you used in your example?

Thanks,

Danilo.

[Timo Westkämper]

Hi.

Sorry, it's "with" for JPAQuery.
 

Is not possible to use alias with JPAQuery? What you used in your example?

What do you mean?

Timo
 

Thanks,

Danilo.

[Danilo Brandão]

Hi timo,

We changed our code according your suggestion:

  QTable1 t1 = QTable1.table1;
  QTable2 t2 = QTable2.table2;

  JPAQuery query2 = new JPAQuery(entityManager);
  JPASubQuery sub = new JPASubQuery();

  PathBuilder<Object[]> total = new PathBuilder<Object[]>(Object[].class, "total");
  List<Tuple> rows = query2.from(t1)
    .join(sub.from(t2).where(t2.date.between(dateInicial, dateFinal), t2.keyword.eq(keyword))
      .groupBy(t2.customerId)
      .list(t2.customerId, t2.date.max(), t2.receita.sum().as("receita")),
      total

      ).with(t1.date.eq(t2.date), t1.id.eq(t2.customerId))

      .orderBy(t1.id)
      .list(t1.id, t1.campaign, total.get("receita"));

But now we are having the following error:

  16:10:53,123 ERROR [org.hibernate.hql.internal.ast.ErrorCounter] (http-localhost-127.0.0.1-8080-6) line 3:56: unexpected token: max
  16:10:53,123 ERROR [org.hibernate.hql.internal.ast.ErrorCounter] (http-localhost-127.0.0.1-8080-6) line 3:56: unexpected token: max: line 3:56: unexpected token: max
 
We are using hibernate 4.1.3.Final with JBoss AS 7.1.1 and the following configuration in applicationContext.xml and persistence.xml:

  jpaVendorAdapter = org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter
  jpaDialect = org.springframework.orm.jpa.vendor.HibernateJpaDialect
  databasePlatform = org.hibernate.dialect.SQLServerDialect
  database = SQL_SERVER

The generated query is:

 select table1.id, table1.campaign, total.receita
 from Table1 table1
 join (select table2.campaignId as campaignId, max(table2.date) as date, sum(table2.receita) as receita
 from Table2 table2
 where table2.date between '2012-06-24' and '2012-06-25' and table2.keyword = 'tv'
 group by table2.campaignId) as total
 with table1.date = total.date and table1.id = total.campaignId
 order by table1.id asc

I tried to put the generated query in a sql server client and i got this error:

Erro:
   Msg 102, Level 15, State 1, Line 6
 Incorrect syntax near ','.
   Msg 319, Level 15, State 1, Line 7
 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

I believe that SQL Server isn't recognizing "with" as a valid clause to use with join, we are using SQL Server 2005.

Do you know why we are getting this error??  Is this the expected query?

Thanks in advance,

Danilo.

[Timo Westkämper]

Hi.

On Wed, Jun 27, 2012 at 11:50 PM, <querydsl+noreply-APn2wQftpsGC-hJ...@googlegroups.com> wrote:

Hi timo,

We changed our code according your suggestion:

  QTable1 t1 = QTable1.table1;
  QTable2 t2 = QTable2.table2;

  JPAQuery query2 = new JPAQuery(entityManager);
  JPASubQuery sub = new JPASubQuery();

  PathBuilder<Object[]> total = new PathBuilder<Object[]>(Object[].class, "total");
  List<Tuple> rows = query2.from(t1)
    .join(sub.from(t2).where(t2.date.between(dateInicial, dateFinal), t2.keyword.eq(keyword))
      .groupBy(t2.customerId)
      .list(t2.customerId, t2.date.max(), t2.receita.sum().as("receita")),
      total

      ).with(t1.date.eq(t2.date), t1.id.eq(t2.customerId))

You can't join to a sub query in JPQL.  You need to use SQL for this.
 

[Danilo Brandão]

Now it works \o/, We used:

SQLServerQuery sqlServerQuery = new SQLServerQuery(getConnection(), dialect);
SQLSubQuery sub = new SQLSubQuery();


Thanks,

Danilo