problem with left join subquery

[peter]

Hey =)
I have a problem with following query:

QTotalAmount amounts = QTotalAmount.totalAmount; // contains several fields... contains an itemDefinition and a quantity
QItemDefinition item = QItemDefinition.itemDefinition;

ListSubQuery<Tuple> subquery = new JPASubQuery()
                               .from(amounts)
                               .groupBy(amounts.itemDefinition)
                               .list(       
                                   amounts.itemDefinition.as(QAmount.amount.itemDefinition),
                                   amounts.quantity.sum().as(QAmount.amount.available)
                               );

JPAQuery query = new JPAQuery(entityManager)
                               .from(item)
                               .leftJoin(subquery, QAmount.amount)
                               .on(item.eq(QAmount.amount.itemDefinition));
List<Tuple> data = query().list(
                               item,
                               QAmount.amount.available
                           );

==
public class QAmount extends EntityPathBase<Tuple> {

    public static final QAmount amount = new QAmount("amount_");

    public final QItemDefinition itemDefinition = new QItemDefinition("item_");
    public final NumberPath<BigDecimal> available = createNumber("available_", BigDecimal.class);

    public QAmount(String variable) {
        super(Tuple.class, new PathMetadata<String>(null, variable, PathType.VARIABLE));
    }
}

That code is just an excerpt from the whole query. But it doesn't work, too:
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [
     select itemDefinition, amount_.available_
         from ItemDefinition itemDefinition
         left join (
             select totalAmount.itemDefinition as item_,
                    sum(totalAmount.quantity) as amount_.available_
             from TotalAmount totalAmount
             group by totalAmount.itemDefinition
         ) as amount_
         on itemDefinition = item_].
[87, 245] The join association path is not a valid expression.
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:155)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:334)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:278)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1583)
    ... 94 more

I'm not sure, what I've done wrong, the sql doesn't seem to be so bad?! ^^

[timowest]

Hi.

Subqueries are supported only in the where part for JPQL.

Timo

[peter]

hi

thanks for your answer..

mhmm.. f*** .. :-P

is there any workaround?
I need some count() queries.. something like
long count = query.where(QAmount.amount.available.gt(100)).count();

ok.. that could be the easier part, but I also need something like
List<Tuple> result = query.where(QAmount.amount.available.gt(100))
        .orderBy(QAmount.amount.available.asc())
        .offset(5000).limit(200).list(...);

of course it would be possible to load all entries in a list and order/filter them manually, but that would be an annoying performance leak, if there are more than 50k entries..

[timowest]

Hi.

Is QAmount.amount in both cases the root entity?

Br,

Timo

[peter]

no.. query comes from above...

ListSubQuery<Tuple> subquery = new JPASubQuery()
                               .from(amounts)
                               .groupBy(amounts.itemDefinition)
                               .list(       
                                   amounts.itemDefinition.as(QAmount.amount.itemDefinition),
                                   amounts.quantity.sum().as(QAmount.amount.available)
                               );

JPAQuery query = new JPAQuery(entityManager)
                               .from(item)
                               .leftJoin(subquery, QAmount.amount)
                               .on(item.eq(QAmount.amount.itemDefinition));

List<Tuple> data = query.list(
                               item,
                               QAmount.amount.available
                           );

QAmount is just a helper class for the aliases... otherwise I have to recalculate the sums everytime (in orderBy, having-clause,...)
... at least that was the plan ^^

the root entity is QItemDefinition with a left join to QTotalAmount... A QAmount should be a row built by QTotalAmount for an itemDefinition, that I could reuse in a filter or an order. Also I could use a case builder if "QAmount.amount.available" is null e.g.

[timowest]

How did you create QAmount?

Could you maybe sketch what you need in SQL? It looks like you will need to express this anyway with SQL, e.g. Querydsl SQL.

Timo