# HQL collection in select clause?

**URL:** <https://discourse.hibernate.org/t/hql-collection-in-select-clause/4566>\
**Category:** Hibernate ORM\
**Created:** [September 11, 2020, 5:20pm UTC](https://discourse.hibernate.org/t/hql-collection-in-select-clause/4566 "2020-09-11T17:20:05Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mekane](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mekane/32/679_2.png) [@mekane](https://discourse.hibernate.org/u/mekane)\
**Post date:** [September 11, 2020, 5:20pm UTC](https://discourse.hibernate.org/t/hql-collection-in-select-clause/4566/1 "2020-09-11T17:20:05Z")

</div>

Is it possible to select a collection in HQL?

**Classes**

```auto
Class Author
----------------
String name
List<Books>

Class Book
---------------
String name
String category
Author author

```

**HQL**

```auto
select author.name, elements(book.name)
from Author author
inner join author.books book
where book.category = "fiction"

```

and the query.list() function would return a list of: `String, List<String>` ?

---

<div class="post-metadata">

**Author:** ![roundcubefour](https://avatars.discourse-cdn.com/v4/letter/r/848f3c/32.png) [@roundcubefour](https://discourse.hibernate.org/u/roundcubefour)\
**Post date:** [September 12, 2020, 8:14am UTC](https://discourse.hibernate.org/t/hql-collection-in-select-clause/4566/2 "2020-09-12T08:14:39Z")

</div>

`contributors` is a Collection. As such, it does not have an attribute named id.

Id is an attribute of the elements of this Collection. [shareit app](https://get-shareit.com)

You can fix the issue by joining the collection instead of dereferencing it:

```auto
SELECT p 
  FROM Project pj 
  JOIN pj.contributors p 
 WHERE pj.id = :pId
   AND p.Id = :cId

```

---

<div class="post-metadata">

**Author:** ![mekane](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mekane/32/679_2.png) [@mekane](https://discourse.hibernate.org/u/mekane)\
**Post date:** [September 14, 2020, 2:36pm UTC](https://discourse.hibernate.org/t/hql-collection-in-select-clause/4566/3 "2020-09-14T14:36:56Z")

</div>

So then I would essentially need to group the rows of contributors into a Project myself? The problem is my real query I am using (not the example I posted) is more complex and grabs from two sub collections, kind of like this:

```auto
SELECT pj.name, c.name, m.name 
  FROM Project pj 
  JOIN pj.contributors c 
  JOIN pj.maintainers m

```

This produces tons of rows since cartesian product. I could do 2 separate queries and try to join them, but this requires a lot of work outside the query, which is why I was looking for something more like what I first posted if hibernate can somehow transform them into lists for me.

---

<div class="post-metadata">

**Author:** ![mekane](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mekane/32/679_2.png) [@mekane](https://discourse.hibernate.org/u/mekane)\
**Post date:** [September 16, 2020, 6:18pm UTC](https://discourse.hibernate.org/t/hql-collection-in-select-clause/4566/4 "2020-09-16T18:18:19Z")

</div>

I’ve been doing a lot of reading this week trying to figure this out, and I think this essentially gives me an answer here:

> <https://stackoverflow.com/questions/32453989/what-is-the-solution-for-the-n1-issue-in-jpa-and-hibernate/49789933#49789933>

`If you need to fetch multiple child associations, it's better to fetch one collection in the initial query and the second one with a secondary SQL query.`

I guess that is just kind of common sense, I was hoping Hibernate could somehow do this for you behind the scenes.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [November 4, 2020, 8:06pm UTC](https://discourse.hibernate.org/t/hql-collection-in-select-clause/4566/5 "2020-11-04T20:06:49Z")

</div>

You have more or less two options in HQL. Either you fetch an entity along with associations or you list all attributes individually that you want to fetch in the select list and work with `Object[]` or `Tuple`.

You could use the following HQL:

```auto
select author
from Author author
join fetch author.books book
where exists (
  select 1
  from author.books b
  where b.category = "fiction"
)

```

This will return a `List<Author>` with all books fetched that wrote at least one book in the category “fiction”.