ORM Cascading Proposal #41

New Issue

lunny · 2013-12-30T12:36:58Z

nashtsai commented

2013-12-30 12:36:58 +00:00

(Migrated from github.com)

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

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 commented

2013-12-31 01:57:38 +00:00

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 commented

2014-04-08 06:17:43 +00:00

(Migrated from github.com)

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 commented

2014-04-08 08:08:24 +00:00

(Migrated from github.com)

omitting 'has_many' seem a valid suggestion though

ahall commented

2014-04-10 22:48:13 +00:00

(Migrated from github.com)

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

lunny commented

2014-04-11 03:19:29 +00:00

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
}

I want to use xorm cascade loading like below: ``` Go 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 commented

2014-04-11 09:14:21 +00:00

(Migrated from github.com)

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?

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 commented

2014-04-11 09:16:56 +00:00

Of course, both yours and the below are ok.

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

Of course, both yours and the below are ok. ``` Go type Device struct { Id int64 User User `xorm:"index cascade(user_id)"` Name string } ```

nashtsai commented

2014-04-11 13:47:59 +00:00

(Migrated from github.com)

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.

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 ``` Go 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 commented

2014-04-11 14:01:46 +00:00

(Migrated from github.com)

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

has_one is also required as there are situation where FK association is placed on the owner in most one-to-one situation ``` Go 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 commented

2014-04-11 14:07:59 +00:00

(Migrated from github.com)

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.

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 commented

2014-04-11 14:13:00 +00:00

(Migrated from github.com)

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

nashtsai commented

2014-04-11 14:20:26 +00:00

(Migrated from github.com)

@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 commented

2014-04-11 15:31:39 +00:00

(Migrated from github.com)

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 commented

2014-06-11 16:33:03 +00:00

(Migrated from github.com)

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

lunny commented

2014-06-12 06:16:28 +00:00

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

nashtsai commented

2014-08-07 16:23:06 +00:00

(Migrated from github.com)

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)

Basic ORM preconditions: - All association domain models is using ‘Id int64’ as its PK One-to-One: ``` Go 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>]) ``` ``` sql 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: ``` Go 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: ``` Go 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>]) ``` ``` sql 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: ``` Go 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: ``` Go 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: ``` Go 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: ``` Go 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 commented

2014-08-08 02:07:38 +00:00

Perfect work!!!

And some rules could be considered:

Cascade tag field MUST be pointer to struct, struct is not allowed.
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>])

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

Perfect work!!! And some rules could be considered: 1. Cascade tag field MUST be pointer to struct, struct is not allowed. 1. 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 commented

2014-08-08 02:57:19 +00:00

(Migrated from github.com)

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?

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: ``` Go 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 commented

2014-08-08 05:43:32 +00:00

(Migrated from github.com)

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")

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 commented

2014-08-08 14:41:20 +00:00

(Migrated from github.com)

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