Grails multiple databases

Question

Hi,

how can we write queries on 2 databases .

I installed datasources plugin and domain classes are :

class Organization {
long id
long company_id
String name

static mapping = {
    version false
    table 'organization_'
    id column : 'organizationId'
    company_id column : 'companyId'
    name column : 'name'
}

}

---

class Assoc {
Integer id
Integer association_id
Integer organization_id

static mapping = {
    version false
    table 'assoc'
    id column : 'ASSOC_ID'
    association_id column : 'ASSOCIATION_ID'
    organization_id column : 'ORGANIZATION_ID'
}

}

this is working :
def org = Organization.list()
def assoc = Assoc.list()

and this is not working :

def query = Organization.executeQuery("SELECT o.name as name, o.id FROM Organization o WHERE o.id IN (SELECT a.organization_id FROM Assoc a )")

error :

org.hibernate.hql.ast.QuerySyntaxException: Assoc is not mapped [SELECT o.name as name, o.id FROM org.com.domain.Organization o WHERE o.id IN (SELECT a.organization_id FROM AssocOrg a )]

How can we connect with 2 databases using single query ?

thanks in advance .

Answer 1

You'll have to split it into two queries, get the ids first and use those in the primary query. The plugin partitions your domain classes each into their own separate SessionFactory, so you can't join in Hibernate.