CODEWITHSUNDEEP
javahibernatejpa
Przemek Wiśniewski
Przemek Wiśniewski

Reputation: 21

JPA subquery returning list into constructor

Let's say we have a many-to-many relationship:

entity ChannelDO {
    Long id;
    String name;
    Set<LanguageDO> languages;
}
entity LanguageDO {
    Long id;
    String name;
}

Then there is also a simple class used in REST communication between client and server (for some reasons I never let my entity objects go outside EJB methods):

public class ChannelListItem {
    public Long id;
    public String name;
    public List<String> languages;
    public ChannelListItem();
    public ChannelListItem(Long id, String name, List<String> languages);
}

Objects of that class are rendered into table-like view on client side.

I could simply select a list of entity objects and build final list of ChannelListItems by myself but maybe there is a convenient way to make JPA (Hibernate) do this.

I am trying with query like this but with no success:

select new pkg.ChannelListItem( c.id, c.name,
    (select cl.name from c.languages cl order by cl.name asc) )
from ChannelDO c
order by c.name asc

It seems that my sub-query is only able to return single value. JPA tells me that it expects constructor with last parameter of type String instead of List.

Upvotes: 2

Views: 1891

Answers (1)

asm0dey
asm0dey

Reputation: 2931

What about 

select new pkg.ChannelListItem( c.id, c.name, c.languages )
from ChannelDO c
order by c.name asc

with dto like

public class ChannelListItem {
    public Long id;
    public String name;
    public List<String> languages;
    public ChannelListItem();
    public ChannelListItem(Long id, String name, List<ChannelDO> channels){
        languages = new ArrayList<String>();
        for(ChannelDO channel: channels)
            languages.add(channel.getName())
        //omitted
    }
}

And answering your question: JPA doesn't support subqueries in select.

Upvotes: 1

Related Questions