1 of 1

hasManyDeep

Usage

A hasManyDeep relationship is either a one-to-many or a many-to-many relationship. It is used when you want to access related entities through one or more intermediate entities. For example, you may want to retrieve all the blog posts for a Team:

// Team.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function posts() {
        return hasManyDeep(
            relationName = "Post",
            through = [ "User" ],
            foreignKeys = [
                "teamId", // the key on User that refers to Team
                "authorId" // the key on Post that refers to User
            ],
            localKeys = [
                "id", // the key on Team that identifies Team
                "id" // the key on User that identifies User
            ]
        );
    }

}

This would generate the following SQL:

SELECT *
FROM `posts`
INNER JOIN `users`
    ON `posts`.`authorId` = `users`.`id`
WHERE `users`.`teamId` = ? // team.getId()

You can generate this same relationship using a Builder syntax:

// Team.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function posts() {
        return newHasManyDeepBuilder()
            .throughEntity( "User", "teamId", "id" )
            .toRelated( "Post", "authorId", "id" );
    }

}

HasManyDeep relationships can also traverse pivot tables. For instance, a User may have multiple Permissions via a Role entity.

// User.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function permissions() {
        return hasManyDeep(
            relationName = "Role",
            through = [ "roles_users", "Role", "permissions_roles" ],
            foreignKeys = [ "userId", "id", "roleId", "id" ],
            localKeys = [ "id", "roleId", "id", "permissionId" ]
        );
    }

}

If a mapping in the through array is found as a WireBox mapping, it will be treated as a Pivot Table.

If you are traversing a polymorhpic relationship, pass an array as the key type where the first key points to the polymorphic type column and the second key points to the identifying value.

Constraining Relationships

There are two options for constraining a HasManyDeep relationship.

The first option is by using table aliases.

// Team.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function activePosts() {
        return hasManyDeep(
            relationName = "Post",
            through = [ "User AS u" ],
            foreignKeys = [
                "teamId", // the key on User that refers to Team
                "authorId" // the key on Post that refers to User
            ],
            localKeys = [
                "id", // the key on Team that identifies Team
                "id" // the key on User that identifies User
            ]
        ).where( "u.active", 1 );
    }

}

// Team.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function activePosts() {
        return newHasManyDeepBuilder()
            .throughEntity( "User AS u", "teamId", "id" )
            .toRelated( "Post", "authorId", "id" )
            .where( "u.active", 1 );
    }

}

This produces the following SQL:

SELECT *
FROM `posts`
INNER JOIN `users` AS `u`
    ON `posts`.`authorId` = `users`.`id`
WHERE `u`.`teamId` = ? // team.getId()
AND `u`.`active` = ? // 1

If you want to use scopes or avoid table aliases, you can use callback functions to constrain the relationship:

// Team.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function activePosts() {
        return hasManyDeep(
            relationName = "Post",
            through = [ () => newEntity( "User ).active() ],
            foreignKeys = [
                "teamId", // the key on User that refers to Team
                "authorId" // the key on Post that refers to User
            ],
            localKeys = [
                "id", // the key on Team that identifies Team
                "id" // the key on User that identifies User
            ]
        );
    }

}

// Team.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function activePosts() {
        return newHasManyDeepBuilder()
            .throughEntity(
                entityName = "User",
                foreignKey = "teamId",
                localKey = "id",
                callback = ( user ) => user.active()
            )
            .toRelated( "Post", "authorId", "id" )
    }

}

Signature