LEFT JOIN Query with JPA not working (select new entity with users that have n documents) -

may me task...

persistence provider eclipselink 2.6.

i want retrieve list of users may have 0 or n documents. because both tables have few columns want use select new entity (userid, amountdocuments), need user-id , amount of documents task. if user hasn't documents yet, "0" should shown, e.g.:

userid:            1 2 3 4
amountdocs:  0 1 0 3

mapping documents in entity user follows:

@onetomany(fetch=fetchtype.lazy, cascade=cascadetype.all,mappedby = "user", targetentity = userdocument.class) @orderby("sortorder asc") @joinfetch(joinfetchtype.outer)  protected list<userdocument>documents;  

mapping user in entity userdocument follows:

@manytoone(cascade=cascadetype.all) protected user user; 

and here jpa-query:

  select distinct  new user.entity.user(u.id,count(doc.user)) user u  left join u.documents doc on doc.user = u  , doc.active = 't'  group u.id 

problem is, only retrieve 2 users have documents match doc.active='t'.

i tried size(u.documents) returns 2 users , additionally wrong document-count values.

what wrong here?

thanks in advance!

finally after spending hours simple stuff, right solution came with:

select distinct     new user.entity.user(u.id,count(doc)) user u      left join u.documents doc on doc.user = u , doc.active = 't'         group u.id 

i have count left joined documents not users.