10

Is there a way that, i can get list of distinct User objects(based on username). And still get result as a List of User Objects rather than, List of username's.

My code is

def criteria = User.createCriteria()
def users = criteria.list() {
    projections {
        distinct("username")
    }
    setResultTransformer(CriteriaSpecification.ROOT_ENTITY)
}
return users

Currently am getting List of the usernames, not User.

Improve this question
5
  • User.findAll doesn't work? Commented Jul 12, 2013 at 10:00
  • @baxxabit 1.) I need to get distinct User objects(based on username). 2.) I have to use criteria, as my actual list is based on a complex search. So findAll() wont fit in. Commented Jul 12, 2013 at 10:08
  • 1
    criteria.listDistinct()? Commented Jul 15, 2013 at 14:37
  • As per Grails document "The listDistinct() method does not work well with the pagination options maxResult and firstResult." Also user here, should be distinct based on the username, not based on equals method or id. Commented Jul 15, 2013 at 15:22
  • This question doesn't make sense from a database standpoint. Once you are doing a distinct call you would just be arbitrarily grabbing the first result that matches the username and persisting it back. You would probably need to use HQL to accomplish this, but I'm still not certain why you would want to. It seems to me that there is a design flaw in the User table to begin with if usernames can be duplicated. Commented Mar 20, 2014 at 17:43

6 Answers 6

Reset to default
16

Ya projection is like filtering and selecting by username you should change it to 

def criteria = User.createCriteria()
def users = criteria.listDistinct() {
    projections {
        groupProperty("username")
    }
}
return users

JOB DONE!

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It should be groupProperty camel case :)
4

One of these should work - I haven't tested any of them, I leave that up to you :)

  • User.list().unique()
  • User.list().unique() with the equals() method on the User domain class overridden to compare objects using the username
  • User.list().unique { it.username } (might need toArray() after list())
Improve this answer

6 Comments

The problem with this solution is that, this is in-memory filtering. First all user objects are fetched and then we remove duplicate entries. Not good for a table with millions of entries. And i should be using a "Criteria'. Because my actual search is complex.
Can you use the users list of usernames (as you have in your question) and get objects for those... def userObjects = users.collect { User.findByUsername(it) }?
That would result in two db calls. Not good if performance is of high priority. :)
Are the usernames repeated often? If the distinct list is only slightly smaller than the number of table entries the in-memory filtering shouldn't be bad...
The usernames are repeated often, usually more than 70% of the search results are duplicates.
|
2 
def criteria = User.createCriteria()
def users = criteria.list() {
    projections {
        distinct("username")
    }
    setResultTransformer(CriteriaSpecification.ROOT_ENTITY)
}

Just replace setResultTransformer(CriteriaSpecification.ROOT_ENTITY) with resultTransformer(ALIAS_TO_ENTITY_MAP). You will get a list of string as a result

otherwise just replace .list with .listDistinct and use do not need distinct("username"), just can be property("username");

Usually people get problems with pagination. not results. If you already had something like:

User.createCriteria().list([max:params.max,offset:params.offset],{
createAlias("others", "others", CriteriaSpecification.LEFT_JOIN);

ilike("others.firstName", "%${query}%");
});

It could result in row duplicates. Because .listDistinct() does not support pagination, just add 

resultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

So query will look like this:

User.createCriteria().list([max:params.max,offset:params.offset],{
    resultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
    createAlias("others", "others", CriteriaSpecification.LEFT_JOIN);

    ilike("others.firstName", "%${query}%");
    });
Improve this answer

Comments

1

Where ever you got a collection (list, array, ...) (I don't know if work with any type of collection, but work in all that i could test). Use unique{ it.property }:

Example:

def users = []
for (def room in rooms) {
    users.addAll(room.users)
}
return users.unique{ it.id }
Improve this answer

Comments

0

Using where query(Detached criteria):

def userListQuery = User.where{
// Your search criteria
}

def userList = userListQuery.list().unique{ it.username }

it should result one query for distinct results.

Improve this answer

1 Comment

This would also fetch all User from db and then do a filtering to find unique results.
0

This will get you the distinct user object based on userName

def userInstance = User.list().unique{ it.user_name}

Improve this answer

2 Comments

How is this answer an improvement over this much older answer?
This is also an example of in-memory filtering as mentioned by ashipj

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.