Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sum of Case Builder Expression in QueryDSL

Tags:

mysql

querydsl

I have a query like below

select field1,filed2,count(*),
       sum(case isNew WHEN 0 THEN 0 ELSE 1 END) as new_count1,
       sum(case source WHEN 'SomeValue' THEN 0 ELSE 1 END) as new_count2 
  from TABLE 
 where status='processed' 
   and filed1='filed1Value'
   and filed2='field2Value';

The output of this is a single row containing all values I require. I want to achieve the same using queryDSL. I am using the CaseBuilder in the QueryDSL but unable to apply sum on top of it.. Does QueryDsl support that? Any one solved this before??

    Expression<Integer> sourceCases = qTable.source.
            when(matchedValue1).then(new Integer(1)).
            otherwise(new Integer(0));

    Expression<Integer> newAccountCases = qTable.isNew.
            when(matchedValue2).then(new Integer(1)).
            otherwise(new Integer(0));

    return queryDslJdbcTemplate.queryForObject(sqlQuery,
            new Mapping(qTable.filed1,
                        qTable.filed2,
                        qTable.id.count(),
                        ####SUM(  (sourceCases) )###,
                        ####SUM(  (newAccountCases) )###);
like image 627
Thrinath Dosapati Avatar asked Jan 21 '14 13:01

Thrinath Dosapati


1 Answers

The base class Expression does not allow you to apply aggregations. If you notice, your otherwise clause actually returns NumberExpression. All you need to do is use that type instead of Expression. So your code could become:

NumberExpression<Integer> sourceCases = qTable.source.
        when(matchedValue1).then(new Integer(1)).
        otherwise(new Integer(0));

NumberExpression<Integer> newAccountCases = qTable.isNew.
        when(matchedValue2).then(new Integer(1)).
        otherwise(new Integer(0));

return queryDslJdbcTemplate.queryForObject(sqlQuery,
        new Mapping(qTable.filed1,
                    qTable.filed2,
                    qTable.id.count(),
                    sourceCases.sum(),
                    newAccountCases.sum());
like image 95
David Fleeman Avatar answered Sep 28 '22 21:09

David Fleeman