How to select distinct results in Spring Data

Question

I have a problem to build query in Spring Data using simple Spring Data query or @Query or QueryDSL.

How to select rows that will be distinct for three columns (Study, Country, Login) and as a result of query will be the list of objects type of User?

Table:

------------------------------------- | User | ------------------------------------- | Id | Study | Country | Site | Login | ------------------------------------- | 1 | S1 | US | 11 | user1 | | 2 | S1 | US | 22 | user1 | | 3 | S1 | US | 33 | user1 | | .. | .. | .. | .. | .. | -------------------------------------

I need a query which based only on Study will return unique Users for each Login and Country only and without taking into account the Site column.

Method signature is like below:

List<User> findByStudyIgnoreCase(String study);

and right now is returning all rows from table Users. So I have duplicated rows about user assignments in Study and Country because I have UI presentation in other table where Site is not needed.

So, I need something like:

select distinct Study, Country, Login from User

but returning object must be the User object just like the method signature says (for example first of matching result).

How can it be done?

1. Is it possible in this Way or similar way? How to make it correct?

   @Query("SELECT DISTINCT s.study, s.country, s.login FROM user s where s.study = ?1 ") List<User> findByStudyIgnoreCase(String study);

2. Is it possible using QueryDSL?

---- EDIT ----

I tried to write query via QueryDSL like TimoWestkämper suggested but I have a problem.

    public List<User> findByStudyIgnoreCase(String study) {
        QUser $ = QUser.user;
        BooleanExpression studyExists = $.study.equalsIgnoreCase(study);

        List<Users> usersList = from($)
            .where(studyExists)
            .distinct()
            .list(Projections.bean(User.class, $.study, $.country, $.id.login));

        return usersList;
    }

After call above query the exception occurs:

org.springframework.dao.InvalidDataAccessApiUsageException: The bean of type: com.domain.app.model.User has no property called: study; nested exception is java.lang.IllegalArgumentException: The bean of type: com.domain.app.model.User has no property called: study

Why it happens?

---- EDIT 2 ----

My User class:

@Entity
@Table(name="USER")
@Immutable
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Getter @EqualsAndHashCode @ToString
@FieldDefaults(level=AccessLevel.PRIVATE)
public class User {

    @EmbeddedId
    UserId id;

    @Column(name="CONTACT_UNIQUE_ID")
    String contactUniqueId;

    String country;

    @Column(name="COUNTRY_CODE")
    String countryCode;

    @Column(name="STUDY")
    String study;

    String firstname;

    String lastname;

    String email;

    String role;
}

Embeddable UserId class:

@Embeddable
@Getter @EqualsAndHashCode @ToString
@NoArgsConstructor
@AllArgsConstructor
@FieldDefaults(level=AccessLevel.PRIVATE)
public class UserId implements Serializable {

    private static final long serialVersionUID = 1L;

    String site;
    String login;

}

Generated QUser class:

@Generated("com.mysema.query.codegen.EntitySerializer")
public class QUser extends EntityPathBase<User> {

    private static final long serialVersionUID = 1646288729;

    private static final PathInits INITS = PathInits.DIRECT;

    public static final QUser user = new User("user");

    public final StringPath contactUniqueId = createString("contactUniqueId");

    public final StringPath country = createString("country");

    public final StringPath countryCode = createString("countryCode");

    public final StringPath study = createString("study");

    public final StringPath email = createString("email");

    public final StringPath firstname = createString("firstname");

    public final QUser id;

    public final StringPath lastname = createString("lastname");

    public final StringPath role = createString("role");

    public QUser(String variable) {
        this(User.class, forVariable(variable), INITS);
    }

    @SuppressWarnings("all")
    public QUser(Path<? extends User> path) {
        this((Class)path.getType(), path.getMetadata(), path.getMetadata().isRoot() ? INITS : PathInits.DEFAULT);
    }

    public QUser(PathMetadata<?> metadata) {
        this(metadata, metadata.isRoot() ? INITS : PathInits.DEFAULT);
    }

    public QUser(PathMetadata<?> metadata, PathInits inits) {
        this(User.class, metadata, inits);
    }

    public QUser(Class<? extends User> type, PathMetadata<?> metadata, PathInits inits) {
        super(type, metadata, inits);
        this.id = inits.isInitialized("id") ? new QUser(forProperty("id")) : null;
    }

}

Answer 1

I can answer the Querydsl part. It works via

List<User> users = query.from(user)
  .where(user.study.eq(arg))
  .distinct()
  .list(Projections.fields(User.class, user.study, user.country, user.login));

You will get User instances with populated study, country and login fields out. The User instances are not managed JPA entities, but populated beans.

Alternatively you can query for Tuple instances like this

List<Tuple> tuples = query.from(user)
  .where(user.study.eq(arg))
  .distinct()
  .list(user.study, user.country, user.login);

But as you are using Spring Data you might want to return Users instead.

Answer 2

I resolved the problem with call general query for all rows and then narrowing results in other method. It is not prefer solution for me but I want to show you my need. Maybe then you will find better solution with only query. Is it even possible to include this in only one query?

public List<User> findDistinctUsersByStudyNumIgnoreCase(String study) {
    QUser $ = QUser.user;
    BooleanExpression studyExists = $.study.equalsIgnoreCase(study);

    List<User> usersList = from($)
        .where(study)
        .listDistinct($);

    // narrowing results (this what I do not know how to use in QueryDsl query)
    usersList = removeDuplicates(usersList); 

    return usersList;
}

/**
 * <p>Remove duplicated user assignments for presentation User table purpose only</p>
 * <p><b>NOTE:</b> Duplicated assignment is when more than one entry in USER 
 * has the same <i>study</i>, <i>country</i>, <i>login</i> and differ with <i>site</i> only. 
 * For presentation User Assignments table purpose the <i>site</i> context is not needed, so we need
 * only info about user existence in <i>study</i> and <i>country</i> context only.</p>
 * @param usersList user assignments list from USER with study, country and site context (with duplicates)
 * @return distinct user assignments list narrowed to study and country context only and without site context (without duplicates)
 */
private List<User> removeDuplicates(List<User> usersList) {
    List<User> result = new ArrayList<User>();

    MultiKeyMap studyCountryLoginMap = new MultiKeyMap();
    for (User u : usersList) {
        if (!studyCountryLoginMap.containsKey(u.getStudy(), u.getCountry(), u.getId().getLogin())) {
            studyCountryLoginMap.put(u.getStudy(), u.getCountry(), u.getId().getLogin(), u);
        }
    }

    result.addAll(studyCountryLoginMap.values());
    return result;
}