# NativeSQL in Criteria-Api "übersetzen"



## Saheeda (28. Jun 2016)

Hallo,

ich habe ein kleines Problem, an dem ich schon fast den ganzen Tag sitze.
In der Anwendung gibt es verschiedene Requests (im Sinne von Nutzeranfrage, kein HTTP-Request). Jeder Request kann eine beliebige Anzahl von Notizen haben.
Außerdem hat jede Notiz einen Ersteller und ein Erstelldatum.


Ich brauche jetzt zu jedem Request die aktuellste / neueste Notiz. Von dieser Notiz brauche ich den Nutzer und von dem wiederum die Rolle (Admin, User, etc.).
Am Ende brauch ich alle Requests, deren letzte Notiz von Nutzern mit einer bestimmten Rolle erstellt wurde.
(Z.B. alle Requests, an denen zuletzt ein Admin etwas geschrieben hat)


Mein MySQL dazu sieht so aus:

```
Select r.id, aNR.noticeCreator_User_fk, user.role_UserRole_fk
from request r
join (
        Select rn.request_fk, id, noticeCreator_User_fk
        from request_notice rn
        join (
                Select request_fk, max(noticeCreationDate) actuelDate
                from request_notice
                group by request_fk
            ) aD
        on rn.noticeCreationDate = aD.actuelDate
            and rn.request_fk=aD.request_fk
    ) aNR
    on r.id=aNR.request_fk
join user
on aNR.noticeCreator_User_fk=user.id
```


Jetzt brauche ich das Ding aber nicht in MySQL, nicht in JPQL, sondern mithilfe der CriteriaApi, genauer gesagt ne Liste von Predicates. Hintergrund ist, dass dies Teil eines Filters ist, welcher mit der Criteria Api arbeitet.


Leider bin ich mit der CA nicht sonderlich geübt (benutze sonst nur JPQL) und versuche wie gesagt schon fast den ganzen Tag, das entsprechend zu "übersetzen". 

Kann mir jemand helfen?


----------



## Saheeda (29. Jun 2016)

Ich habs hinbekommen, die sql-Query zu vereinfachen, bin beim Übersetzen in die Criteria Api allerdings kaum weiter:



```
select DISTINCT request_fk
            from request_notice
            join request on request.id = request_fk
            join user on request_notice.noticeCreator_User_fk = user.id
            join user_role on user.role_UserRole_fk = user_role.id

            and request_notice.noticeCreationDate =
              (
                SELECT noticeCreationDate 
                from request_notice 
                where request_notice.request_fk = request.id 
                ORDER BY request_notice.noticeCreationDate DESC
                    LIMIT 1
               )
```



```
Root<RequestNotice> RequestNotice = this.getQuery().from(RequestNotice.class);
            this.getQuery().distinct(true);
            Join<RequestNotice, Request> requestJoin = RequestNotice.join(RequestNotice_.request);
            Join<RequestNotice, User> creatorJoin = RequestNotice.join(RequestNotice_.noticeCreator);
            Join<User, UserRole> roleJoin = creatorJoin.join(User_.role);
                       
            if(!CollectionUtils.isEmpty(this.filter.getAllowedRoles())) {
                Set<String> roles = new HashSet<>();
                for (UserRole userRole : this.filter.getAllowedRoles()) {
                    roles.add(userRole.getTechnicalName());
                }   
                restrictions.add(roleJoin.get(UserRole_.technicalName).in(roles));
            }
```

Momentanes Problem ist, dass es vollkommen egal ist, welche Nutzerrollen ich in die Query gebe, das Ergebnis bleibt immer das gleiche.


----------

