CODEWITHSUNDEEP
javapostgresqlquerydsl
Marek
Marek

Reputation: 438

Querydsl SQL one-to-many relation Projection into object with nested list not working correctly

I have simple relation in database

ACCOUNT

id name
1 Euro account
2 Dolar account

ACCOUNT_SETTING

id account_id name value
1 1 primary true
2 1 active true
3 1 temporal false
4 2 active false

I would like to get joined data from both tables projected into following DTO classes:

public class Account {
  private Long id;
  private String name;
  private List<AccountSetting> accountSettings;

  public Account(Long id, String name, List<AccountSetting> accountSettings) {
    this.id = id;
    this.name = name;
    this.accountSettings = accountSettings;
  }

  // getters setters
} 

public class AccountSetting {
  private String name;
  private boolean value;

  public AccountSetting(String name, boolean value) {
    this.name = name;
    this.value = value;
  }

  // getters setters
}

I have generated Q-classes for this database.

I've tried to set projection using grouping:

PostgreSQLQuery<Tuple> result = sqlQueryFactory
      .select(
        account.id,
        account.name,
        accountSetting.name,
        accountSetting.value)
      .from(account)
      .join(accountSetting).on(accountSetting.accountId.eq(account.id));

Map<Long, Account> resultMap = result.transform(
      GroupBy.groupBy(
        account.id
      ).as(
        Projections.constructor(
          Account.class,
          account.id,
          account.name,
          list(Projections.constructor(
              AccountSetting.class,
              accountSetting.name,
              accountSetting.value
            )
          )
        )
      )
    );

Also tied projection in select as follow:

    PostgreSQLQuery<Account> accountRows = sqlQueryFactory
      .select(Projections.constructor(
          Account.class,
          account.id,
          account.name,
          list(Projections.constructor(
              AccountSetting.class,
              accountSetting.name,
              accountSetting.value
            )
          )
        )
      )
      .from(account)
      .join(accountSetting).on(accountSetting.accountId.eq(account.id));

Each time I'm getting nested single-element-list of accountSettings (instead of 3 settings for account.id=1 and 1 setting list for account.id=2

I'm using querydsl v5.0.0.

I will appreciate any advice...

I found many answers (mostly related to QueryDSL JPA or QueryDSL Collections) related to QueryDSL resutls projections. Might be that for QueryDSL SQL I'm doing something wrong?

I've checked already: https://github.com/querydsl/querydsl/blob/cd608f04ed7534b1b62a78a28beb141071d149b6/querydsl-collections/src/test/java/com/querydsl/collections/GroupByTest.java#L219C17-L219C42

or

https://github.com/querydsl/querydsl/blob/cd608f04ed7534b1b62a78a28beb141071d149b6/querydsl-core/src/test/java/com/querydsl/core/group/GroupByListTest.java#L185

as a reference but it just doesn't work as expected for me.

Upvotes: 0

Views: 1220

Answers (1)

Kyung Song
Kyung Song

Reputation: 36

probably way too late, but check to see if you are using the correct "list" method

you should use list from

import static com.querydsl.core.group.GroupBy.list;

NOT

import static com.querydsl.core.types.Projections.list;

Upvotes: 1

Related Questions