Is it possible in Grails to join mongo and mysql domains?

Question

I have one domain using MySQL db, other domain uses MongoDB. Can I join them?

For example:

Appeal (mongo domain)

class Appeal {

    static mapWith = "mongo"

    Organization organization <=== MySQL domain
    ...
}

Organization (MySQL domain)

class Organization {
    ... 
    static hasMany = [ appeals : Appeal ]; <==join to mongo domain
}

Exception is :

Error initializing the application: Error creating bean with name 'transactionManagerPostProcessor': Initialization of bean failed;
nested exception is
org.springframework.beans.factory.BeanCreationException: Error
creating bean with name 'transactionManager': Cannot resolve reference
to bean '$primaryTransactionManager' while setting constructor
argument with key [0]; nested exception is
org.springframework.beans.factory.BeanCreationException: Error
creating bean with name '$primaryTransactionManager': Cannot resolve
reference to bean 'sessionFactory' while setting bean property
'sessionFactory'; nested exception is
org.springframework.beans.factory.BeanCreationException: Error
creating bean with name 'sessionFactory': Invocation of init method
failed; nested exception is org.hibernate.MappingException:
Association references unmapped class: lc.itgroup.education.dao.Appeal
    Message: Error creating bean with name 'transactionManagerPostProcessor': Initialization of bean failed;
nested exception is
org.springframework.beans.factory.BeanCreationException: Error
creating bean with name 'transactionManager': Cannot resolve reference
to bean '$primaryTransactionManager' while setting constructor
argument with key [0]; nested exception is
org.springframework.beans.factory.BeanCreationException: Error
creating bean with name '$primaryTransactionManager': Cannot resolve
reference to bean 'sessionFactory' while setting bean property
'sessionFactory'; nested exception is
org.springframework.beans.factory.BeanCreationException: Error
creating bean with name 'sessionFactory': Invocation of init method
failed; nested exception is org.hibernate.MappingException:
Association references unmapped class: lc.itgroup.education.dao.Appeal

Answer 1

No, but you can approximate it. You would have the same problem joining two tables in two different relational databases - each gets its own SessionFactory and there's no support in Hibernate or GORM to join across databases or datastores.

To approximate it, store the primary key of the other table/document and use transient methods to retrieve the instance for you. This is basically what Hibernate does for you - it stores the foreign key value and auto-loads the instance on demand.

class Appeal {

    static mapWith = "mongo"

    void setOrganization(Organization organization) {
        organizationId = organization.id
    }
    Organization getOrganization() {
        organizationId ? Organization.get(organizationId) : null
    }
    static transients = ['organization']

    Long organizationId
    ...
}

Using this approach, your code will be very similar to what it would look like if both tables were in the same database. When you access the Organization it will be retrieved from the database using the id that was previously persisted.

It's important that the organization property be transient since having a matched get/set pair like this would be considered a persistent property and as you've seen, this will fail. Only the id should be persisted.