CODEWITHSUNDEEP
grailsgrails-orm
YAT
YAT

Reputation: 466

GORM Mapping View

I have an Domain Class called Contact with multiple hasMany Relationships and another Domain Class Employee which is part of Concat.

Contact has an table contact and Employee should be mapped on a View which looks like this:

SELECT * FROM contact where employee=1

Employee should have the same columns and Relationship than Contact, how do I write the Domain Classes? Can I use inheritance?

EDIT

Now I have used inheritance like this:

class Employee extends Contact { }
class Contact{
    static mapping = {
    tablePerHierarchy(false)
    }
}

That works so far, but now I want to add some Relationships to Employee, like this:

 class Employee extends Contact {      
     static belongsTo = [CostCenter ]
     static hasMany = [costCenter: CostCenter]
     static mapping = { 
     costCenter joinTable: 'employee_cost_center', column: 'employee_id'
     }
 }
 class CostCenter {
   static hasMany = [employees:Employee]
    static mapping = { 
     employeesjoinTable: 'employee_cost_center', column: 'cost_center_id'
     }
 }

now I have the problem that the table 'employee_cost_center' makes an referen to Contact which is good, but also added 'employee_id':

contact_id
employee_id
cost_center_id

So i could add the relationships to Contact but then I have in CostCenter Contact and not Employee.

How can I add Relationships to Employee?

Upvotes: 0

Views: 2434

Answers (1)

Emmanuel Rosa
Emmanuel Rosa

Reputation: 9885

I think you're on track using inheritance. Since Employee is backed by a database view which selects a subset of Contacts, an Employee is a Contact. So you've got a good candidate for inheritance.

Table-per-subclass inheritance

You described the employee view as follows: 

SELECT * FROM contact where employee=1

When using table-per-subclass inheritance the table generated for subclasses contain the following columns:

  1. ID (primary key)
  2. Columns for properties added to the subclass (are not in the superclass), excluding properties for associations.

Since Employee does not, and cannot add, additional properties, the view should only return the primary key. 

SELECT id FROM contact where employee=1

I have an article that compares table-per-hierarchy to table-per-subclass inheritance and demonstrates what it looks like at the database level.

Join tables

In your domain class examples you described a join table to create the many-to-many relationship between Employee and CostCenter. Join tables should have two, and only two, columns:

  1. The foreign key (the me domain class)
  2. The foreign key of the other domain class.

So your employee_cost_center table should have the columns employee_id and cost_center_id. If you must specify the join table explicitly, use key instead of column.

costCenter joinTable: 'employee_cost_center', key: 'employee_id'
employees joinTable: 'employee_cost_center', key: 'cost_center_id'

belongsTo

You have Employee belong to CostCenter as so:

static belongsTo = [CostCenter ]

Maybe that's a typo, but if you're not defining a back-reference then the belongsTo should be defined as simply the class, like this:

static belongsTo = CostCenter

I've never used belongsTo this way so I don't know what it looks like in the database. But note that if you have a back-reference, defined like this:

static belongsTo = [costCenter: CostCenter]

Then, your employee view must return a cost_center column.

Upvotes: 1

Related Questions