group by - how to groupBY using spring data

Question

hi i'm using spring data in My project and I'm trying group by two fields, heres the request:

@Query( "SELECT obj from Agence obj GROUP BY obj.secteur.nomSecteur,obj.nomAgence" )
  Iterable<Agence> getSecteurAgenceByPc();

but it doesnt work for me..what i want is this result:

-Safi
  -CTM
    CZC1448YZN
    2UA13817KT

-Rabat
  -CTM
    CZC1349G1B
    2UA0490SVR
  -Agdal
    G3M4NOJ

-Essaouira
  -CTM
    CZC1221B85
  -Gare Routiere Municipale
    CZC145YL3

What I get is

{
    "status": 0,
    "data":
    [
        {
            "secteur": "Safi",
            "agence": "CTM"
        },
        {
            "secteur": "Safi",
            "agence": "Dep"
        },
        {
            "secteur": "Rabat",
            "agence": "Agdal"
        },
        {
            "secteur": "Rabat",
            "agence": "CTM"
        },
        {
            "secteur": "Essaouira",
            "agence": "CTM"
        },
        {
            "secteur": "Essaouira",
            "agence": "Gare Routiere Municipale"
        }
    ]
}

Answer 1

What you want is not possible with JPQL.

What does Group By do?

It combines all rows that are identical in the columns in the group by clause in to one row. Since it combines multiple rows into one, data in other columns can only be present in some combined fashion. For example, you can include MIN/MAX or AVG values, but never the orginal values.

Also the result with always be a table, never a tree.

Also note: there is no duplicated data. Every combination of secteur and agence appears exactly once.

If you want a tree structure, you have to write some java code for that.