why does my jpaquery produce wrong result?

[Bruce Chen]

Hi,

I have three entities, post, post_tag and tag.

Post has one to many relationship to post_tag and post_tag has one to one relationship to tag.

I'm trying to do this query using querydsl

root::DATABASE=> select p.id, count (t)
root::DATABASE-> from post p
root::DATABASE-> left join post_tag pt on p.id = pt.post_id
root::DATABASE-> left join tag t on pt.tag_id = t.id
root::DATABASE-> where t.id in (2,3)
root::DATABASE-> group by p.id;

this would give me the match count of the posts. However, I can't get it to work in querydsl.

List<Tuple> result = new JPAQuery<Tuple>(em)
        .select(count(QTag.tag), post.id)
        .from(post)
        .leftJoin(post.tags, QPostTag.postTag)
        .leftJoin(QPostTag.postTag.tag, QTag.tag)
        .where(post.tags.any().tag.id.in(2))
        .groupBy(post.id)
        .fetch();

I've checked, rechecked, triple checked but still can't find anything wrong. The sql query printed in console seems to be exactly the same, but the results aren't. 

2017-10-04T05:11:20.928649+00:00 app[web.1]: Hibernate:

2017-10-04T05:11:20.928663+00:00 app[web.1]:     /* select

2017-10-04T05:11:20.928665+00:00 app[web.1]:         count(tag),

2017-10-04T05:11:20.928665+00:00 app[web.1]:         postSearchResult.id

2017-10-04T05:11:20.928666+00:00 app[web.1]:     from

2017-10-04T05:11:20.928667+00:00 app[web.1]:         PostSearchResult postSearchResult

2017-10-04T05:11:20.928668+00:00 app[web.1]:     left join

2017-10-04T05:11:20.928668+00:00 app[web.1]:         postSearchResult.tags as postTag

2017-10-04T05:11:20.928669+00:00 app[web.1]:     left join

2017-10-04T05:11:20.928669+00:00 app[web.1]:         postTag.tag as tag

2017-10-04T05:11:20.928670+00:00 app[web.1]:     where

2017-10-04T05:11:20.928670+00:00 app[web.1]:         exists (

2017-10-04T05:11:20.928671+00:00 app[web.1]:             select

2017-10-04T05:11:20.928671+00:00 app[web.1]:                 1

2017-10-04T05:11:20.928672+00:00 app[web.1]:             from

2017-10-04T05:11:20.928673+00:00 app[web.1]:                 postSearchResult.tags as postSearchResult_tags_0

2017-10-04T05:11:20.928674+00:00 app[web.1]:             where

2017-10-04T05:11:20.928677+00:00 app[web.1]:                 postSearchResult_tags_0.tag.id = ?1

2017-10-04T05:11:20.928678+00:00 app[web.1]:         )

2017-10-04T05:11:20.928678+00:00 app[web.1]:     group by

2017-10-04T05:11:20.928679+00:00 app[web.1]:         postSearchResult.id */ select

2017-10-04T05:11:20.928679+00:00 app[web.1]:             count(tag2_.id) as col_0_0_,

2017-10-04T05:11:20.928680+00:00 app[web.1]:             postsearch0_.id as col_1_0_

2017-10-04T05:11:20.928681+00:00 app[web.1]:         from

2017-10-04T05:11:20.928681+00:00 app[web.1]:             post postsearch0_

2017-10-04T05:11:20.928682+00:00 app[web.1]:         left outer join

2017-10-04T05:11:20.928683+00:00 app[web.1]:             post_tag tags1_

2017-10-04T05:11:20.928683+00:00 app[web.1]:                 on postsearch0_.id=tags1_.post_id

2017-10-04T05:11:20.928684+00:00 app[web.1]:         left outer join

2017-10-04T05:11:20.928684+00:00 app[web.1]:             tag tag2_

2017-10-04T05:11:20.928685+00:00 app[web.1]:                 on tags1_.tag_id=tag2_.id

2017-10-04T05:11:20.928685+00:00 app[web.1]:         where

2017-10-04T05:11:20.928686+00:00 app[web.1]:             exists (

2017-10-04T05:11:20.928686+00:00 app[web.1]:                 select

2017-10-04T05:11:20.928687+00:00 app[web.1]:                     1

2017-10-04T05:11:20.928687+00:00 app[web.1]:                 from

2017-10-04T05:11:20.928688+00:00 app[web.1]:                     post_tag tags3_

2017-10-04T05:11:20.928688+00:00 app[web.1]:                 where

2017-10-04T05:11:20.928705+00:00 app[web.1]:                     postsearch0_.id=tags3_.post_id

2017-10-04T05:11:20.928706+00:00 app[web.1]:                     and tags3_.tag_id=?

2017-10-04T05:11:20.928707+00:00 app[web.1]:             )

2017-10-04T05:11:20.928707+00:00 app[web.1]:         group by

2017-10-04T05:11:20.928810+00:00 app[web.1]:             postsearch0_.id

can someone please help me?

[Bruce Chen]

I've figured this out, but I have another issue on how to correctly using alias. 

NumberExpression<Double> formula = ((power(23, 2)).add((power(17, 2)))).sqrt();

List<Tuple> result = new JPAQuery<Tuple>(em)
                .select(post, formula.as("distance"))
                .from(post)
                .where(formula.lt(radius))
                .fetch();

 this will generate the formula twice in select and where. How can I reuse the distance alias in select clause?