1

Hi I am trying to query my database by a specific property. Multiple entries in my database may have the same value for the property but I want it to return only the first entry that has that distinct value for the property.

For example, if my domain has a code property, then there might be an entry where "code" = "boy", and there might be another one where "code" = "girl" and there might be yet another one where again "code" = "boy". I want to query it so I get the first entry where "code" = "boy" and the entry where "code" = girl" but not the third entry where code is again equal to boy.

I was able to get the distinct code values from the database using both createCriteria() or namedQueries() but I am not able to get the whole object, just the value of code. How can I get the actual object?

Leri
  • 12,367
  • 7
  • 43
  • 60
Ameya
  • 549
  • 1
  • 10
  • 19
  • I want the one with a smaller id to populate the object data, each of the entries has an id property as well, the entry gets assigned the id depending on the order that the entry is added to the database – Ameya Aug 15 '12 at 18:58
  • hmmm you have the logSql setted to true? GORM make an extra query to get populate the object data? If so, you can split this in two query's. 1 - get distinct codes, 2 - get object data by the smaller id, using `min`. –  Aug 15 '12 at 19:06
  • @SérgioMichels what is the logSql, is that when you insert a new entry and it automatically gets an id? Could you explain more how I could get the object data using `min`. – Ameya Aug 15 '12 at 19:10
  • logSql is the config to output the sql generated by hibernate. See [this](http://stackoverflow.com/questions/2568507/how-to-log-sql-statements-in-grails) question. –  Aug 15 '12 at 19:19
  • @SérgioMichels how will logging my queries help me? – Ameya Aug 15 '12 at 19:38
  • I'm just trying to see if your actual criteria produce two querys or handle all in one. What will actually solve your problem is the 2 steps that I mentioned before. –  Aug 15 '12 at 19:42
  • @SérgioMichels So from what I understand first I get a list of distinct `code` which I am able to do. Then I use `min` to find the entry with the smallest id which has a `code` value that is in the list of distinct `code` using `WHERE` and `IN`? – Ameya Aug 15 '12 at 19:49
  • That was my first thought, but see my answer. –  Aug 15 '12 at 19:57

1 Answers1

0

Thinking about I came with an HQL query:

select d
  from Domain d
 where d.id = (select min(d1.id)
                 from Domain d1
                where d1.code = d.code)