grails - retrieve domain instances by date from seperate tables

Question

I would like to return the 5 latest domain instances from 2 different tables. I have:

class Domain1 {
...
    Date dateCreated

    static mapping = {
        sort "dateCreated"
    }
...
}

&

class Domain2 {
...
    Date dateCreated

    static mapping = {
        sort "dateCreated"
    }
...
}

I need the 5 most recently created by dateCreated for any one user regardless of the table they come from, i.e if all 5 of the most recently created instances from a user are of type Domain1, then the query returns 5 Domain1 instances. I have looked at the various querying options available and haven't yet identified what could meet these needs (i haven't looked much at HQL). The user domain has a one to many relationship with the domains:

class User {
...
    static hasMany = [domain1s:Domain1,
                      domain2s:Domain2,]
...
}

score 1 · Answer 1 · answered Mar 25 '16 at 10:11

I'd create a new table which has userId, domainName, domainId, dateCreated. Put the work into creating/deleting this record whenever a user saves/destroys a Domain1 | Domain2 | DomainN domain instance.

Then your "I need the newest 5 instances from an arbitrary list of domains" turns pretty trivial.

score 1 · Accepted Answer · edited May 23 '17 at 11:45

Well in general there could be several ways of doing it. Listing down below few of those:

Create third table which contains the common field with respective id field from both the tables.
Use native sql and there use nested queries or join or union queries.

use hql union queries. For example,

select * from (
select
dp.field1 as field1,dp.field2 as field2
from DOMAIN1 dd
where dd.userId = 2

union

select
dp.field1 as field1,dp.field2 as field2
from DOMAIN2 dd
where dd.userId = 2
) as final_table order by dateCreated desc limit0,5

See this post for same.

grails - retrieve domain instances by date from seperate tables

2 Answers2