ORM Cascading Proposal

[nashtsai]

Proposal:

• new xorm tags for has_one, belongs_to, and has_many relationships
• new Engine APIs for lazy fetch
• new xorm tags for specifying eager or lazy fetch, 'lazy', 'eager', 'eager_select'
• default fetch for has_one is eagerly fetched via join query, can override the default behavior conjunction of 'lazy' tag presence
• default fetch for has_many is lazy, can override the default behavior with conjunction of 'eager' or 'eager_select' tag, using 'eager' will result join query for fetching has many datas
• belongs_to is coupled usage with has_many, and default eager fetch

[lunny]

If the field type is struct or pointer to struct, it is has_one; If the field type is slice, map or pointer to slice or map, it is has _many. So I think the two tags could be merged to one tag.

[nashtsai]

there is still difference for has_one and belongs_to, check Active Record pattern http://guides.rubyonrails.org/association_basics.html#belongs-to-association-reference, as it's the basis for my suggestion

[nashtsai]

omitting 'has_many' seem a valid suggestion though

[ahall]

I'd like to see some support for many-to-many relationships too.

[lunny]

I want to use xorm cascade loading like below:

type Country struct {
     Id int64
     Name string
}

type Group struct {
Id int64
Name string
}

type UserGroup struct {
     UserId int64  `xorm:"index"`
     GroupId int64 `xorm:"index"`
}

type UserFamily struct {
   UserId int64 `xorm:"index"`
   FamilyId int64 `xorm:"index"`
}

type Device struct {
Id int64
UserId int64 `xorm:"index"`
Name string
}

type User struct {
     Id int64
     Name string
     Country Country `xorm:"cascade(country_id)"`   // one to one
     Father User `xorm:"cascade(father_id)"` //one to one
     Devices []Device `xorm:"cascade(user_id)"`   // one to many
     Families []User `xorm:"cascade(user_family, user_id, family_id)"` // many to many
     Groups []Group `xorm:"cascade(user_group, user_id, group_id)"` // many to many
}

[ahall]

This sounds good.

type Device struct {
Id int64
User User xorm:"index"
Name string
}

Wouldn't it make sense to reference back the User object instead of the UserId?

[lunny]

Of course, both yours and the below are ok.

type Device struct {
Id int64
User User `xorm:"index cascade(user_id)"`
Name string
}

[nashtsai]

cascade tag wording is like customer mapper for the FK field, but it's missing the owning issue, the meaning of "belongs_to" is the owning rights so it also meaning if owner got deleted the

type Order struct {
    Customer Customer `xorm:"belongs_to"` // customer_id FK association, and customer_id as default mapper
}

type Customer struct {
    Order []Order // has many required default lazy loaded, need an API for lazy loading
}

meaning that a customer got deleted all its orders will be deleted too.

[nashtsai]

has_one is also required as there are situation where FK association is placed on the owner in most one-to-one situation

type Nose struct {
    Face Face // default face_id mapper
}

type Face struct {
    Nose Nose `xorm:"has_one"`// FK lookup from inverse table (Nose)
}

and still, delete face will also delete nose

[ahall]

has_one is very useful indeed.

Shouldn't we be able to specify if the CASCADE should delete? I'm coming from python to quite used to sqlalchemy and think they do it quite well. http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#cascades.

Realise you may not be heading the same way, but worth just checking.

[nashtsai]

@ahall I suppose Active Record by its infamous RoR framework is providing better example/pattern?

[nashtsai]

@ahall just read sqlalchemy the cascade served the save/update/delete behaviour, which is exactly the same usage from using grails, and grails is following active record pattern plus cascade mapping behaviour to override default behaviour, from my experience I've only find that "delete-orphan" mostly used.

[ahall]

Yep I'm only suggesting by default it wont cascade the deletes and you can use e.g. a cascade tag to specify if you want it to delete on cascade. sqlalchemy my default wont cascade the deletes unless you tell it to as you dont always want it to delete on cascade.

[ahall]

This would be extra nice to have. Has anyone started working on it?

[lunny]

I think currently no one is working on it. I think it should be discussed more.

[nashtsai]

Basic ORM preconditions:

• All association domain models is using ‘Id int64’ as its PK

One-to-One:

type Nose struct {
     Id int64
     TheFace *Face `xorm:"belongs_to(the_face_id)”` // optional, use belongs_to if you wanna mapping different column name
}


type Face struct {
     Id int64
     TheNose *Nose `xorm:”has_one(the_face_id)”`
}


// 'belongs_to' clause usage:
// belongs_to([mapping column name] [, <mapping table name>])



// 'has_one' clause usage:
// has_one([mapping column name] [, <mapping table name>])

create table face (id bigint generated by default as identity (start with 1),                  
  primary key (id));
create table nose (id bigint generated by default as identity (start with 1),                  
  the_face_id bigint not null,                  
  primary key (id));
alter table nose add constraint fk_nose_the_face_id foreign key (the_face_id) references face (id);

implementation notes:

• XORM will ALWAYS using JOIN fetch for One-to-One with 1 depth level
• cascade save:

   face := &Face{ TheNose:Nose{} }
   engine.Insert(&face) // insert both nose and face record with association made

• cascade delete, using DB feature, i.e., ‘ON DELETE CASCADE’ clause in mysql?
• cascade save and delete only works on owning side, i.e., has_one

One-to-Many:

type Order struct {
     Id int64
     TheCustomer *Customer `xorm:"belongs_to(the_customer_id)”` // optional, use belongs_to if you wanna mapping different column name
}


type Customer struct {
     Id int64
     TheOrders []*Order `xorm:”has_many(the_customer_id)"`
}



// 'has_many' clause usage:
// has_many([mapping column name] [, <mapping table name>])

create table customer (id bigint generated by default as identity (start with 1),                  
  primary key (id));
create table order (id bigint generated by default as identity (start with 1),                  
  the_customer_id bigint not null,                  
  primary key (id));
alter table order add constraint fk_order_the_customer_id foreign key (the_customer_id) reference customer (id);

Many-to-Many:

(yet supported)

Using has_many fetching strategy:

lazy:

type Customer struct {
     Id int64
     TheOrders []*Order `xorm:”has_many(the_customer_id)”` // default will be lazy
}


// 'lazy' clause usage:
// lazy // default behavior works with declaring it, and with no limit max fetch size and no table ordering
// lazy(<max fetch size:int>[, <order by fields:string>])

eager_select:

type Customer struct {
     Id int64
     TheOrders []*Order `xorm:”has_many(the_customer_id) eager_select”`
}


// 'eager_select' clause usage:
// eager_select // default with no limit max fetch size and no table ordering
// eager_select(<max fetch size:int>[,<order by fields:string>])

eager_join:

type Customer struct {
     Id int64
     TheOrders []*Order `xorm:”has_many(the_customer_id) eager_join”`
}


// 'eager_join' clause usage:
// eager_join // default with no table ordering
// eager_join(<order by fields:string>)

Adding and Removing associations:

Only work on the inverse side of domain object for removing and adding associations:

Removing association:
// retrieved var order1 Order
order1.TheCustomer = nil
engine.Update(&order1)


Adding association:


// retrieved var order1 Order
order1.TheCustomer = differentCustomer
engine.Update(&order1)


// insert new record
order := Order{ TheCustomer:customer1 }
engine.Insert(&order)

[lunny]

Perfect work!!!

And some rules could be considered:

1. Cascade tag field MUST be pointer to struct, struct is not allowed.
2. Many to Many need a default joint-table name, and user could specify one.

type Customer struct {
     Id int64
     TheOrders []*Order `xorm:”has_many(joint-table, the_customer_id, the_order_id)”` // default will be lazy
}

type Order struct {
     Id int64
     TheCustomers []*Order `xorm:”has_many(joint-table, the_order_id, the_customer_id)”` // default will be lazy
}

// 'lazy' clause usage:
// lazy // default behavior works with declaring it, and with no limit max fetch size and no table ordering
// lazy(<max fetch size:int>[, <order by fields:string>])

1. For Many to Many, lazy load need a method to manually load the data.

engine.LazyLoad(&orders) // this will a object or a slice of object

[nashtsai]

I've updated has_many clause usage above:

// 'has_many' clause usage:
// has_many([mapping column name] [, <mapping table name>])

Which is a conflicted design to above comments.

extended to:

has_many([mapping column name] [, <mapping table name> [, <many-to-many join table inverse mapping column name> ] ])

so you above example can be:

type CustomerOrder struct {
    CustomerId int64
    OrderId int64
}

type Customer struct {
     Id int64
     TheOrders []*Order `xorm:”has_many(customer_id, customer_order, order_id)”`
}

type Order struct {
     Id int64
     TheCustomers []*Order `xorm:”has_many(order_id, customer_order, customer_id)”` 
}

And with above design, it also means that for many-to-many declaration, that all 3 params are needed.

@lunny any thought on adding/removing many-to-many associations without adding new APIs?

[nashtsai]

For Many to Many, lazy load need a method to manually load the data, we have missed multiple has_many issue:

engine.LazyLoad(&orders) // this will a object or a slice of object

consider following a domain has multiple has_many:

type Flight struct {
    Id int64
    DepartureAirport Airport
    DestinationAirport Airport
}

type Airport struct {
     Id int64
     InboundFlights []*Flight `xorm:”has_many(destination_airport_id, flight)”` 
     OutboundFlights []*Flight `xorm:”has_many(departure_airport_id, flight)”` 
}

engine.LazyLoad(&airPort, "InboundFlights")

[ahall]

Sounds good to me. Shame there is no way of auto lazyload due to language restrictions.

[lunny]

How to add or remove associations simply. It's currently diffculty.