Complex SQL query using grails,HQL

Question

i am trying to write a 3 table join query in HQL grails my 3 classes are:

class Domains {
    String name
    Date ets
    Account account

    static hasMany = [facebooks: Facebook, twitters: Twitter]
}

...

class Facebook {
    Boolean activated
    String name
    String token
    String pageId
    String expiryTime
    String scope
    Date ets
    String username
    Domains domains

    static belongsTo = [Domains]
}

...

class FbPosts {
    String postId
    Long commentsCount
    Long likesCount
    Long sharesCount
    Date date
    Date ets
    String message
    String type
    Integer postImpression
    Facebook facebook

    static belongsTo = [Facebook]
}

i am trying to get fbPosts related to a domain (domain eid). my current HQL query looks like this:

def fbPosts = FbPosts.findAll("from FbPosts as fb join Facebook as f on f.eid=fb.facebook_eid join Domains as d on d.eid=f.domain_eid where d.eid=?"[domain_eid])
def map = [fbPosts:fbPosts]

and does not seem to work. please help.

Answer 1

How about this then? The downside is that it will cause two separate database queries (unless the domains object already exist in the persistance cache).

def domainsInstance=Domains.get(domain_eid)
def fbPosts = FbPosts.withCriteria {
    facebook {
        eq('domains', domainsInstance)
    }
}