Scooter Framework

Active Record Associations

Overview

There are relationships among domain models everywhere. For example, in a blog application, a post may have many comments; a user may have many posts and comments; and a post may belong to a certain category.

Relationships make CRUD operations difficult. For example, when inserting a comment record, we need to make sure that there is a parent post already in the database. When we delete a user account, we may also have to delete all the posts and comments made by this user.

The following example shows operations related to delete a user without declaring associations:

    //first delete all comments made by the user
    for (int i = 0; i < comments.size(); i++) {
        ActiveRecord comment = (ActiveRecord)comments.get(i);
        comment.delete();
    }

    //then delete all posts made by the user
    for (int i = 0; i < posts.size(); i++) {
        ActiveRecord post = (ActiveRecord)posts.get(i);
        post.delete();
    }

    //finally delete the user itself
    user.delete();

Scooter's Active Record framework comes with associations which help to manage the associated operations among related records.

This example shows operations related to deleting a user after declaring associations with a cascade delete option:

    //just delete the user and ActiveRecord will take care of deleting the
    //associated comments and posts belong to the user
    user.delete();

Declaring Relationships

Scooter's Active Record supports four basic association types.

1. belongs-to
2. has-many
3. has-one
4. has-many-through

To declare a relation, simply override the registerRelations() method in the ActiveRecord class. In a blog application, we may have the following relationship declarations:

class User extends ActiveRecord {
    public void registerRelations() {
        hasMany("posts", "cascade:delete");
        hasMany("comments", "cascade:delete");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        hasMany("comments", "cascade:delete");
        belongsTo("user");
        belongsTo("category");
    }
}

class Comments extends ActiveRecord {
    public void registerRelations() {
        belongsTo("post");
        belongsTo("user");
    }
}

class Category extends ActiveRecord {
    public void registerRelations() {
        hasMany("posts");
    }
}

Notice that Active Record by default uses plural form of a domain model name when declaring a has-many association. Also Active Record assumes that the primary key in a parent model is id, while the foreign key in its child model is {parent model name}_id.

Don't worry if your models do not follow this convention. Scooter is all about flexibility. There are many ways to declare a relation.

Example: primary key in user model is username and the foreign key in post model is user_id; also do not delete post records in posts table, but instead use null value for the user_id field when a user is deleted.

class User extends ActiveRecord {
    ...
    hasMany("posts", "mapping:username=user_id;cascade:nullify");
    ...
}

Example: specify a special condition.

class User extends ActiveRecord {
    ...
    //all admin's posts in descending order
    hasMany("admin_posts", "model:post; conditions_sql: user_id in ('admin'); order_by: posts.created_at desc");
    ...
}

Basic Associations

Belongs-To Association

belongs-to association specifies a one-to-one relationship between an owner model and its target model. The owner model of the belongs-to association holds a foreign key to the target. belongs-to associations are typically found among models with one-to-one or many-to-one cardinalities. Reverse relation of a belongs-to association can be either a has-one or has-many association.

After declaring a belongs-to association, the owner model can connect to the target model through the overloaded associated methods in ActiveRecord class.

• public AssociatedRecord associated(Class target)
• public AssociatedRecord associated(Class target, boolean refresh)
• public AssociatedRecord associated(Class target, String options)
• public AssociatedRecord associated(Class target, String options, boolean refresh)
• public AssociatedRecord associated(String associationId)
• public AssociatedRecord associated(String associationId, boolean refresh)
• public AssociatedRecord associated(String associationId, String options)
• public AssociatedRecord associated(String associationId, String options, boolean refresh)

The AssociatedRecord object brings in more operations on target model:

• attach(ActiveRecord newParent): change a parent record
• delete(): delete the associated target record
• detach(): terminate relation with parent record (value for foreigk key is set to null)
• detach(true): terminate relation with parent record and delete self if depends on parent
• getOwner(): return the owner record
• getRecord(): return the associated record
• getRecord(true): retrieve and return the associated record
• getRelation(): return the current relation
• isEmpty(): true if there is no associated record
• replace(ActiveRecord newTarget): this is equivlent to detach from old and attach to new target record

Examples:

    //find author of a post
    ActiveRecord author = post.associated("user").getRecord();

    //assign a post to an author record
    ActiveRecord david = User.findFirst("name='David'");
    post.associated("user").attach(david);

Notice that the associationId in the above belongs-to example uses singular nouns. This is consistent with human reading.

Has-One Association

has-one association indicates a one-to-one relationship between an owner model and its target model. The target model of the has-one association holds a foreign key to the owner model. has-one associations are typically found among models with one-to-one cardinality. The reverse relation of a has-one association is a belongs-to association.

After declaring a has-one association, the owner model can connect to the target model through the overloaded associated methods in ActiveRecord class.

• public AssociatedRecord associated(Class target)
• public AssociatedRecord associated(Class target, boolean refresh)
• public AssociatedRecord associated(Class target, String options)
• public AssociatedRecord associated(Class target, String options, boolean refresh)
• public AssociatedRecord associated(String associationId)
• public AssociatedRecord associated(String associationId, boolean refresh)
• public AssociatedRecord associated(String associationId, String options)
• public AssociatedRecord associated(String associationId, String options, boolean refresh)

The AssociatedRecord object brings in more operations on target model:

• attach(ActiveRecord target): change a target record
• delete(): delete the associated target record
• detach(): terminate relation with target record (value for foreigk key is set to null in target)
• detach(true): terminate relation with target record and delete target if it depends on owner
• getOwner(): return the owner record
• getRecord(): return the associated record
• getRecord(true): retrieve and return the associated record
• getRelation(): return the current relation
• isEmpty(): true if there is no associated record
• replace(ActiveRecord newTarget): this is equivlent to detach from old and attach to new target record

Examples: assuming a has-one relation between user model and login model. The login model holds user login credentials.

    //find login username and password of a user
    ActiveRecord login = user.associated("login").getRecord();
    String username = (String)login.getField("username");
    String password = (String)login.getField("password");

Notice that the associationId in the above has-one example uses singular nouns. This is consistent with human reading.

Has-Many Association

has-many association sets up a one-to-many relationship between an owner model and its target model. The target model of the has-many association holds a foreign key to the owner model. has-many associations are typically found among models with one-to-many cardinalities. The reverse relation of a has-many association is a belongs-to association.

After declaring a has-many association, the owner model can connect to the target model through the overloaded allAssociated methods in ActiveRecord class.

• public AssociatedRecords allAssociated(Class target)
• public AssociatedRecords allAssociated(Class target, boolean refresh)
• public AssociatedRecords allAssociated(Class target, String options)
• public AssociatedRecords allAssociated(Class target, String options, boolean refresh)
• public AssociatedRecords allAssociated(String associationId)
• public AssociatedRecords allAssociated(String associationId, boolean refresh)
• public AssociatedRecords allAssociated(String associationId, String options)
• public AssociatedRecords allAssociated(String associationId, String options, boolean refresh)

The AssociatedRecords object brings in more operations on target model:

• add(ActiveRecord target): add a child record to the association
• add(List targets): add a list of child records to the association
• clear(): remove all associated records
• count(): count number of associated records in the database
• delete(ActiveRecord target): delete an associated target record
• delete(List targets): delete a list of associated target records
• detach(ActiveRecord target): terminate relation with a target record (value for foreigk key is set to null in target)
• detach(List targets): terminate relation with a list of target records
• getOwner(): return the owner record
• getRecord(int): return an associated record
• getRecords(): return a list of associated records
• getRecords(true): retrieve and return a list of associated records
• getRelation(): return the current relation
• isEmpty(): true if there is no associated records
• replace(List targets): this is equivlent to detach from old and attach to new target records
• size(): return number of associated records

Examples:

    //find all posts of a user
    List posts = user.allAssociated("posts").getRecords();

    //find total number of comments of a post
    int total = post.allAssociated("comments").count();

Notice that the associationId in the above has-many example uses plural nouns. This is consistent with human reading.

Has-Many-Through (HMT) Association

has-many-through (HMT) association specifies a one-to-many relationship between an owner model and its target model through a join model. The relation between the owner model and the join model is the has-many relation. The relation between the join model and the target model can be either a belongs-to or a has-many association.

has-many-through (HMT) association is typically applied to models with many-to-many cardinalities where a join model is required.

After declaring a has-many-through association, the owner model can connect to the target model through the overloaded allAssociated methods in ActiveRecord class.

• public AssociatedRecords allAssociated(Class target)
• public AssociatedRecords allAssociated(Class target, boolean refresh)
• public AssociatedRecords allAssociated(Class target, String options)
• public AssociatedRecords allAssociated(Class target, String options, boolean refresh)
• public AssociatedRecords allAssociated(String associationId)
• public AssociatedRecords allAssociated(String associationId, boolean refresh)
• public AssociatedRecords allAssociated(String associationId, String options)
• public AssociatedRecords allAssociated(String associationId, String options, boolean refresh)

The AssociatedRecords object brings in more operations on target model:

The following operations come with the hmt relationship declaration.

• add(ActiveRecord target): add a child record to the association
• add(List targets): add a list of child records to the association
• clear(): remove all associated records
• count(): count number of associated records in the database
• delete(ActiveRecord target): delete an associated target record
• delete(List targets): delete a list of associated target records
• detach(ActiveRecord target): terminate relation with a target record (value for foreigk key is set to null in target)
• detach(List targets): terminate relation with a list of target records
• getOwner(): return the owner record
• getJoinRecord(ActiveRecord target): return the join record
• getRecord(int): return an associated record
• getRecords(): return a list of associated records
• getRecords(true): retrieve and return a list of associated records
• getRelation(): return the current relation
• isEmpty(): true if there is no associated records
• replace(List targets): this is equivlent to detach from old and attach to new target records
• size(): return number of associated records

We can declare a hmt association from Vet model to Specialty model in the PetClinic example. The cardinality between Vet and Specialty is many-to-many.

public class Vet extends ActiveRecord {
    public void registerRelations() {
        hasMany("vet_specialties");
        hasManyThrough("specialties", "vet_specialties");
    }
}

public class VetSpecialty extends ActiveRecord {
    public void registerRelations() {
        belongsTo("vet");
        belongsTo("specialty");
    }
}

public class Specialty extends ActiveRecord {
    public void registerRelations() {
        hasMany("vet_specialties", "cascade: delete");
    }
}

Now we can perform hmt operations.

    //find Linda's specialties
    ActiveRecord linda = Vet.findFirst("first_name='Linda'");
    List allSpecialtiesOfLinda = linda.allAssociated("specialties").getRecords();

    //create a new specialty and assign it to Lina
    //ActiveRecord will insert a new record in specialties table and
    //vet_specialties table respectively
    ActiveRecord javaSpecialty = Specialty.newRecord();
    javaSpecialty.setData("name", "Java");
    linda.allAssociated("specialties").add(javaSpecialty);

Notice that the associationId in the above has-many-through example uses plural nouns. This is consistent with human reading.

Polymophic Association

Association Helpers

Options

You can use options to specify more details of an association. All options consist of multiple name and value pairs separated by semicolon. Colon is used to split the name and value string.

Belongs-To Association

belongs-to association supports the following options:

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        //id and name are columns in users table
        belongsTo("user", "columns:id,name");
    }
}

//Only id and name fields appear in the author instance
ActiveRecord author = post.associated("user").getRecord();

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        //job_title is a column in users table
        belongsTo("user", "ex_columns:job_title");
    }
}

//Author's job_title does not appear in the author instance
ActiveRecord author = post.associated("user").getRecord();

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        //posts_count column in users table records the count
        belongsTo("user", "counter_cache:true");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        belongsTo("editor", "conditions_sql: user_type = 'editor'");
    }
}

//user_type = 'editor' appear in the SQL as a where clause.

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        belongsTo("user", "finder_sql: select first_name, last_name from users where first_name like '%David%'");
    }
}

//SQL: select first_name, last_name from users where first_name like '%David%'
ActiveRecord author = post.associated("user").getRecord();

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        belongsTo("user", "include:address,contacts");
    }
}

//Author's address and contacts are retrieved in the same SQL statement.
ActiveRecord author = post.associated("user").getRecord();

//No SQL sent to database because the records are already retrieved.
List contacts = author.allAssociated("contacts").getRecords();

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        belongsTo("user", "mapping:author_id=id");
    }
}

//Assuming:
//Model Order Primary Key (id, customer_id)
//Model LineItem Primary Key (id, order_id, customer_id)
class LineItem extends ActiveRecord {
    public void registerRelations() {
        ...
        belongsTo("order", "mapping:order_id=id,customer_id=customer_id");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        belongsTo("admin_user", "model:user; conditions_sql:user_type='Admin'");
    }
}

Has-One Association

has-one association supports the following options:

//Assuming User has-one Address:
class User extends ActiveRecord {
    public void registerRelations() {
        ...
        //id and city are columns in address table
        hasOne("address", "columns:id,city");
    }
}

//Only id and city fields appear in the address instance
ActiveRecord address = author.associated("address").getRecord();

//Assuming User has-one Address:
class User extends ActiveRecord {
    public void registerRelations() {
        ...
        //street and city are columns in address table
        hasOne("address", "ex_columns:street,city");
    }
}

//street and city fields do not appear in the address instance
ActiveRecord address = author.associated("address").getRecord();

class User extends ActiveRecord {
    public void registerRelations() {
        ...
        hasOne("login", "conditions_sql: status = 'active'");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        belongsTo("user", "finder_sql: select first_name, last_name from users where first_name like '%David%'");
    }
}

//SQL: select first_name, last_name from users where first_name like '%David%'
ActiveRecord author = post.associated("user").getRecord();

class User extends ActiveRecord {
    public void registerRelations() {
        ...
        hasOne("login", "include: transactions");
    }
}

//Here we assuming Login model has-many Transaction.
//When retrieving the author's login info (such as password), the
//transactions associated with the login are retrieved in the same SQL statement.
ActiveRecord login = author.associated("login").getRecord();

//No SQL sent to database because the records are already retrieved.
List transactionsOfTheLogin = login.allAssociated("transactions").getRecords();

class User extends ActiveRecord {
    public void registerRelations() {
        ...
        hasOne("login", "mapping:user_id=login_id");
    }
}

class User extends ActiveRecord {
    public void registerRelations() {
        ...
        hasOne("login", "model:account");
    }
}

Has-Many Association

has-many association supports the following options:

class User extends ActiveRecord {
    public void registerRelations() {
        ...
        //id and title are columns in posts table
        hasMany("posts", "columns:id,title");
    }
}

//Only id and title fields appear in the post instances
List posts = author.allAssociated("posts").getRecords();

class User extends ActiveRecord {
    public void registerRelations() {
        ...
        //content is a column in posts table
        hasMany("posts", "ex_columns:content");
    }
}

//contant field does not have value in the post instances
List posts = author.allAssociated("posts").getRecords();

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        hasMany("latest_comments", "conditions_sql: created_dt > ...");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        hasMany("comments", "finder_sql: select * from comments where public = 'true'");
    }
}

//SQL: select * from comments where public = 'true'"
List publishableComments = post.allAssociated("comments").getRecords();

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        //return both post author and comment authors in one query
        hasMany("comments", "include:user, comments=>user");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        //id is the primary key of posts, author_id is the foreign-key column in comments.
        hasMany("comments", "mapping:id=author_id");
    }
}

//Assuming:
//Model Order Primary Key (id, customer_id)
//Model LineItem Primary Key (id, order_id, customer_id)
class Order extends ActiveRecord {
    public void registerRelations() {
        ...
        hasMany("lineItems", "mapping:id=order_id,customer_id=customer_id");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        hasMany("twits", "model:comments");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        //always pull comments in a descending order of created_dt timestamp.
        hasMany("comments", "order_by: created_dt DESC");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        //always pull comments in a descending order of created_dt timestamp.
        hasMany("comments", "order: Down; sort:created_dt");
    }
}

class Post extends ActiveRecord {
    public void registerRelations() {
        ...
        //only pull unique comments.
        hasMany("comments", "unique:true");
    }
}

Has-Many-Through (HMT) Association

HMT association supports the following options:

class student extends ActiveRecord {
    public void registerRelations() {
        ...
        hasManyThrough("projects", "assignments", "conditions_sql: assign_dt > ...");
    }
}

public class Vet extends ActiveRecord {
    public void registerRelations() {
        hasMany("vet_specialties");
        hasManyThrough("certified_specialties", "vet_specialties", "source:specialty; conditions_sql:certified = true");
    }
}

Advanced Retrieval

Eager Loading

Scooter uses include keyword for eager loading.

    //return a user named David and all his posts including each post's comments and category
    ActiveRecord david = User.findFirst("name='David'", "include:posts=>comments, posts=>category");

    //Now you can get a list of David's posts without hitting database anymore.
    List posts = david.allAssociated("posts").getRecords();