PostgreSQLException ERROR: subquery uses ungrouped column "grandchildren4_.id" from outer query

58 views

Skip to first unread message

Alan C.

unread,

Oct 15, 2022, 11:16:09 AM10/15/22

to Querydsl

Hello dear group, good day.

I hope your day is going great. After contacting Timo W. from QueryDSL Team, he suggested posting my question/issue here. Before, was https://github.com/querydsl/querydsl/issues/3408

Basically, my problem is querying and sort to a counter, but this counter should be obtained from deep-hierarchies [almost 4 levels: ConfigElement -> Reviews -> Criteria -> Issues -> count(issue.eq(CONSTANT))]

## Observed vs. expected behavior
With a `Parent.class`, we would like to query (for sorting, filtering) till a `targetProperty` found in the last `@OneToMany` relationship. Where we are able to know the size/count the elements, the "great grand children", that have this `targetProperty` equals to a constant value.

```
1. (ConfigElement) Parent.class:
@OneToMany List<Child> children;

2. (Review) Child.class:
@OneToMany List<GrandChild> grandChildren;

3. (Criterion) GrandChild.class:
@OneToMany List<GreatGrandChild> greatGrandChildren;

4. (Issue) GreatGrandChild.class:
String targetProperty;
```

While trying to `leftjoin`/`joins` to the required target class, in order, to filter-sort by the counter on the `targetProperty`:

```
JPAQueryFactory queryFactory = new JPAQueryFactory(entityManager);
JPAQuery<Parent> parentJPAQuery =
queryFactory
.selectFrom(QParent)
.leftJoin(QParent.children, child)
.leftJoin(child.grandChildren, grandChild)
.where(parentFilter) // parent.id = 1 and parent.name = name
.groupBy(QParent.id)
.orderBy(grandChild.greatGrandChildren.size()).fetchAll();
// Error on fetch:
final List<?> result = querydsl.applyPagination(pageable, configElementJPAQuery).fetch();
```

The exception is thrown: `org.postgresql.util.PSQLException: ERROR: subquery uses ungrouped column "grandchildren4_.id" from outer query Position: 1134`.

* Another source: https://stackoverflow.com/questions/59621477/querydsl-fetch-joins-requests-group-by-without-groupby

Expected behavior: _The data is filtered and sorted on property_.

## Steps to reproduce

1. Have a class hierarchy till great grand children's relationship with data on DB
2. Implement a query on a property from the great grand children using `leftjoins`
3. PostgreSQL Exception is thrown

## Environment
* Querydsl version: `com.querydsl:querydsl-apt:5.0.0:jpa`
* Querydsl module: `com.querydsl.jpa.JPAQueryBase`
* Database: `PostgreSQL 14.1-bullseye`
* JDK: `openjdk version "17" 2021-09-14 LTS`

## Additional details
* If we try to use the approach, to sort on it like: `QParent.children.any().grandChildren.any().greatGrandChildren.any().targetProperty.eq("VALUE")`. The QueryDSL tells that it requires, or it's expecting a Path in the Join.

### 1. Other approaches done

Update: If I comment the .groupBy(QParent.id), it displays duplicated rows. But this is not what we are looking for. Thanks in advance, team.

### 2. Other approaches done

Another try was to call the query like this (ConfigElement is the parent class) so no errors thrown, but:

JPAQueryFactory queryFactory = new JPAQueryFactory(entityManager);

JPAQuery<ConfigElement> configElementJPAQuery = queryFactory .select(configElement) .from(configElement) .where(configElementsFilter) .orderBy(orderSpecifiers);

final List<?> result = querydsl.applyPagination(pageable, configElementJPAQuery) .fetchAll().fetch();

Take an eye on the fetchAll after pagination was added... then I have a lot of duplicates, sad :c

### 3. Other approaches done

Update: I tried with the next approaches (1, 2, 3), but still, the issues are there:

// 1. Throws PostgreSQL exception for criteria_id from outer join JPAQuery<ConfigElement> configElementJPAQuery = queryFactory .select(configElement) .from(configElement) .where(configElementsFilter) .groupBy(configElement.id, configElement.node.id, configElement.node.project.id) .orderBy(orderSpecifiers);

// 2. Duplicates on various reviews in CE, repeated values on CE JPAQuery<ConfigElement> configElementJPAQuery = queryFactory .select(configElement) .from(configElement) .where(configElementsFilter) .orderBy(orderSpecifiers);

// 3. Does not find ids in subquerys, only allowed at where // clauses (QueryDSL limitation)

JPAQuery<ConfigElement> configElementJPAQuery = queryFactory .select(configElement).from(configElement).where( configElement.eq( queryFactory .select(review.configElement) .from(review).where(review.configElement.id.eq(configElement.id)))).where( review.eq( queryFactory.select(criterion.review) .from(criterion).where(criterion.review.id.eq(review.id)))) .where(criterion.eq(queryFactory.select(issue.criterion) .from(issue).where(issue.criterion.id.eq(criterion.id)))) .where(configElementsFilter).groupBy(configElement.id,review.id, criterion.id,issue.id).orderBy(orderSpecifiers).fetchAll();