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"
        }
    ]
}

What data ist duplicated? Please show us the data that is in the table and the actual result you are getting. If we have to guess what you are seeing, we won't be of much help. — Jens Schauder, Jan 04 '17 at 06:41
take a look here:http://stackoverflow.com/questions/41465772/how-to-groupby-in-spring-data — , Jan 04 '17 at 14:09
please add relevant information to your question to the question. Don't just link to it in the comments. We like a good puzzle here at SO, but we don't want to puzzle questions together. I did it for you this time. — Jens Schauder, Jan 06 '17 at 06:43

score 1 · Answer 1 · answered Jan 06 '17 at 06:52

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.

thank for ur answer it make sens for me ...but do u have any idea how to do that with java code?? could u give an exemple please — , Jan 06 '17 at 08:45

how to groupBY using spring data

1 Answers1

Linked