Group by projection on multiple table join

[Peter Alexis]

Hello,

Say I've a class structure looks like below:

Class Author{
    private List<Publisher> publishersList;
    private List<Book> bookList;
}

i.e I have a one-to-many relation between Author->Publisher and Author->Book

How can get the Author bean populated with the following SQL

SELECT * FROM Author  a
JOIN Publisher p ON p.authorId = a.id
JOIN Book b ON b.authorId = a.id
WHERE a.id = 1234

I guess we should be using transform() & groupby() but not quite getting it.

Thanks

[Timo Westkämper]

Hi.

Something like this should work.

query.from(author)

  .innerJoin(publisher).on ...

  .innerJoin(book).on ...

  .where(a.id.eq(1234))

  .transform(GroupBy.groupBy(author.id) // group key
      .as(Projections.bean(Author.class,

      ... // add author properties

      set(Projections.bean(Publisher.class, ...).as("publishers"), // add publisher properties

      set(Projections.bean(Book.class, ...).as("books"))); // add book properties

You need to use sets insead of lists, since lists would contain duplicates.

Br,
Timo

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[venkates...@csgsol.com]

Hi Timo,

  Assume that there is another list inside the publisher class like List<PublisherType>,

  In this case how to write the query?

[timowest]

Hi.

Querydsl group by projection doesn't yet fully support multiple levels.

Timo

[jrevi]

Dear Timo, all,

I'm reviving this quite old thread because I have approximately the same issue.
Is there some support for multiple levels in the current version or not ?

Best,
Jerome

[timowest]

Hi,

It's not yet supported,

Timo

[Filipe Sousa]

Hello,

What's the status for multiple levels support?

[Lokare Sathyam]

Its there an update on multilevel aggregation ? Its a very common use case.
I have a use case where the aggregation can go upto 5 levels

[Anisio Silva]

Hi Timo, sorry open again this question, but in my case:

class Car{

   private Model model;

   private List<Color> color;

}

.from(tbCar)

.where(tbCar.modelo_carro.eq(modeloID))

.leftJoin(tbModel)

.on(tbCar.modelo_carro.eq(tbModel.id_modelo))

.innerJoin(tbColor)

.on(tbCar.cor_carro.eq(tbColor.id_cor))

.groupBy(tbCar.modelo_carro)

.transform(

groupBy(tbCar.modelo_carro).as(

Projections.bean(CarDTO.class, 

Projections.constructor(ModelDTO.class, tbModel.nome_modelo, tbModel.ano_modelo),

set(Projections.bean(ColorDTO.class,tbColor.nome_cor)).as("listaColor")

)));

But shows the error: Unsupported expression new ModelDTO(model.nomeModelo, model.anoModelo)