Scopes, Relationships, and Patterns

https://slides.com/d/oRl0O30/live

Who Am I?

Utah

Ortus Solutions

Prolific Module Author

1 wife, 3 kids, 1 dog

Type 1 Diabetic

Quick Overview

  • ColdBox ORM Engine
  • Map database tables to CFCs (entities)
  • Perform CRUD operations on entities

What We'll Cover

  • DRYing up SQL code
  • Dealing with related entities
  • Executing fewer queries
  • Speeding up queries
  • Debugging Quick

Scopes

  • Give a name to a collection of SQL
  • One place to change if database structure changes
  • Always receives the builder as the first argument
  • Can pass additional arguments

Scopes

// Post.cfc
component {
  
    function scopePublished( qb ) {
        qb.where( "published", 1 );
    }
  
}

// getInstance( "Post" ).published().get()
component {
  
    function scopePublished( qb ) {
        qb.whereNotNull( "publishedDate" );
    }
  
}

Perfect for Raw Queries

component {
  
    function scopePublished( qb ) {
        qb.whereNotNull( "publishedDate" )
            .whereRaw( "publishedDate <= NOW()" );
    }
  
}
// User.cfc
component {
  
    function scopeRole( qb, role = "standard" ) {
        qb.where( "role", arguments.role );
    }
  
}

Scopes with Arguments

Scopes on Scopes

  • Chainable
  • Can call other scopes
// Task.cfc
component {
  
    function scopeOfType( qb, type ) {
        var types = {
            "Available": 1,
            "In Progress": 2,
            "Completed": 3,
            "Error": 4
        };
	
        if ( isNumeric( arguments.type ) ) {
            qb.where( "status", type );
        } else if ( types.keyExists( type ) ) {
            qb.where( "status", types[ type ] );
        }
    }

    function scopeAvailable( qb ) {
        qb.ofType( "Available" );
    }
  
}

Scopes on Scopes

Global Scopes

  • Calls any defined scopes before executing any query
  • Can ignore scopes using `withoutGlobalScope`
component {

    function applyGlobalScopes() {
        this.ofType( "admin" );
    }
  
    function scopeOfType( qb, type = "standard" ) {
        qb.where( "role", arguments.type );
    }

}

Global Scopes

var users = getInstance( "User" )
    .withoutGlobalScope( [ "ofType" ] )
    .all();

// SELECT * FROM users
var admins = getInstance( "User" ).all();

// SELECT * FROM users WHERE type = 'admin'

What's up with `this`?

  • Quick calls relationships and scopes using `onMissingMethod`
  • A component's `onMissingMethod` is not triggered unless using the `this` keyword.

Executable Scopes

  • Return values matter
  • Returning the builder instance or nothing continues chaining
  • Returning anything else returns that value
// Participant.cfc
component {

    function scopeLeaveConversation( qb, today = now() ) {
        return qb.updateAll( { "leftDate": arguments.today } )
            .result
            .recordcount;
    }

}

Executable Scopes

getInstance( "Participant" )
    .whereIn( "id", rc.ids )
    .leaveConversation();

Relationships

  • Links between entities
  • Can be configured with QueryBuilder methods

Relationships

component name="Conversation" {
  
    function participants() {
        return hasMany( "Participant", "conversationID" )
            .orderByDesc( "joinedDate" );
    }

}

Composing Relationships

  • Relationships can be defined using other relationships.

Composing Relationships

component name="Conversation" {
  
    function participants() {
        return hasMany( "Participant", "conversationID" )
            .orderByDesc( "joinedDate" );
    }

    function activeParticipants() {
        return this.participants().active();
    }
  
}
component name="Participant" {
  
    function scopeActive( qb ) {
        qb.whereNull( "leftDate" );
    }
  
}

Relationship Builder

var posts = user.getPosts();
// same as
var posts = user.posts().get();

var publishedPosts = user.posts().published().get();
  • Dynamically configure relationships at runtime

Scoping Results using Relationships

getInstance( "Post" )
    .where( "userId", user.getId() )
    .get();

// can also be written as

user.posts().get();
  • This can also be used to restrict a query instead of a scope.

Querying Relationships

  • Check for records to exist
  • Restrict using any qb methods or scopes

Querying Relationships

var conversationsWithAtLeastOneParticipant = 
    getInstance( "Conversation" )
        .has( "participants" )
        .get();
var conversationsWithAtLeastOneActiveParticipant = 
    getInstance( "Conversation" )
        .whereHas( "participants", ( qb ) => {
            qb.active();
        } )
        .get();

Querying Nested Relationships

var usersInOngoingConversations = 
    getInstance( "User" )
        .has( "conversations.messages" )
        .get();
var usersParticipatingInConversations = 
    getInstance( "User" )
        .whereHas( "conversations.messages.participant", ( qb ) => {
            qb.whereColumn( "users.id", "participants.userId" );
        } )
        .get();

Assigning Relationships

  • Quick relationships are cached
  • If they are not cached, they are retrieved from the database
  • You can manipulate the cache yourself using `assignRelationship` and `clearRelationship`.

Assigning Relationships

// Conversation.cfc
component {

    public Message function addMessage(
        required string body,
        required Participant from,
    ) {
        // create related entities through the relationship
        var message = this.messages().create( {
            "body": arguments.body,
            "participantID": arguments.from.getID(),
            "createdDate": now()
        } );

        this.update( {
            "updatedDate": now(),
            "totalMessages": this.getTotalMessages() + 1
        } );

        // set the entities in the local relationship cache
        // this avoids extra database calls later
        message.assignRelationship( "participant", arguments.from );
        message.assignRelationship( "conversation", this );
        this.assignRelationship( "latestMessage", message );

        return message;
    }
  
}

Through Relationships

  • Connect two or more relationships
  • Can even go up and down a relationship chain

Through Relationships

// User.cfc
component {

    function team() {
        return belongsTo( "Team" );
    }
  
}
// Team.cfc
component {

    function office() {
        return belongsTo( "Office" );
    }
  
    function users() {
        return hasMany( "User" );
    }
  
}
// Office.cfc
component {

    function teams() {
        return hasMany( "Team" );
    }
  
}
var teammates = user
   .getTeam()
   .getUsers();

var officemates = user
    .getTeam()
    .getOffice()
    .getTeams()
    // wishing that `flatten` and `flatMap`
    // existed on arrays in CFML
    .flatMap( ( team ) => team.getUsers() );

Before

Through Relationships

// User.cfc
component {

    function team() {
        return belongsTo( "Team" );
    }
  
    function teammates() {
        return hasManyThrough( [ "team", "users" ] );
    }
  
    function officemates() {
        return hasManyThrough( [ "team", "office", "teams", "users" ] );
    }
  
}
// Team.cfc
component {

    function office() {
        return belongsTo( "Office" );
    }
  
    function users() {
        return hasMany( "User" );
    }
  
}
// Office.cfc
component {

    function teams() {
        return hasMany( "Team" );
    }
  
}
var teammates = user.getTeammates();

var officemates = user.getOfficemates();

After

Through Relationships

// User.cfc
component {

    function team() {
        return belongsTo( "Team" );
    }
  
    function teammates() {
        return hasManyThrough( [ "team", "users" ] );
    }
  
    function officemates() {
        return hasManyThrough( [ "team", "office", "users" ] );
    }
  
}
// Team.cfc
component {

    function office() {
        return belongsTo( "Office" );
    }
  
    function users() {
        return hasMany( "User" );
    }
  
}
// Office.cfc
component {

    function teams() {
        return hasMany( "Team" );
    }
  
    function users() {
        return hasManyThrough( [ "teams", "users" ] );
    }
  
}

Subselects

  • Reduce queries when you just need one field from a relationship
  • Add computed values as attributes

Subselects

var posts = getInstance( "Post" )
    .addSubselect( "authorName", ( qb ) => {
        qb.select( "name" )
          .from( "authors" )
          .whereColumn( "posts.userId", "authors.id" );
    } )
    .get();

Subselects

var posts = getInstance( "Post" )
    .addSubselect( "authorName", "author.name" )
    .get();
// Post.cfc
component {
  
    function author() {
        return belongsTo( "User" );
    }
  
}
// User.cfc
component {
  
    property name="name";
  
}

Subselects in Scopes

var posts = getInstance( "Post" )
    .addAuthorName()
    .get();
// Post.cfc
component {
  
    function scopeAddAuthorName( qb ) {
        qb.addSubselect( "author", "author.name" );
    }
  
    function author() {
        return belongsTo( "User" );
    }
  
}

Relationship Counts

var posts = getInstance( "Post" )
    .addSubselect( "commentsCount", ( qb ) => {
        qb.reselectRaw( "COUNT(*)" )
            .from( "comments" )
            .whereColumn( "posts.id", "comments.postId" );
    } )
    .get();
var posts = getInstance( "Post" )
    .withCount( [ "comments" ] )
    .get();

Relationship Counts

var post = getInstance( "Post" )
    .withCount( [ "comments", "likes" ] )
    .first();

post.getCommentsCount();
post.getLikesCount();
  • Can fetch counts for multiple relationships as once
  • Can alias them using ` AS alias`

Virtual Attributes

  • Method to dynamically add an attribute to an entity
  • Used automatically whenever you use subselects
  • Can be used to add raw values or CASE values

Virtual Attributes

component {
  
    function scopeAddRoleName( qb ) {
        qb.selectRaw( "(CASE
            WHEN roleId = 2 THEN 'Manager'
            WHEN roleId = 3 THEN 'Admin'
            ELSE 'Standard'
        END) AS roleName" );

        qb.appendVirtualAttribute( "roleName" );
    }
  
}

Eager Loading

var posts = getInstance( "Post" ).limit( 10 ).get();

for ( var post in posts ) {
    writeDump( '"#post.getTitle()#" by #post.getAuthor().getName()#' );
}

11 queries

1 query

10 queries

Eager Loading

var posts = getInstance( "Post" )
    .with( [ "author" ] )
    .limit( 10 )
    .get();

for ( var post in posts ) {
    writeDump( '"#post.getTitle()#" by #post.getAuthor().getName()#' );
}

2 queries

0 queries

2 queries

(But also Subselects)

var posts = getInstance( "Post" )
    .addSubselect( "authorName", "author.name" )
    .limit( 10 )
    .get();

for ( var post in posts ) {
    writeDump( '"#post.getTitle()#" by #post.getAuthorName()#' );
}

1 query

0 queries

1 query

Constraining Eager Loading

var posts = getInstance( "Post" )
    .with( [
        { "author" = ( qb ) => {
            qb.active();
        } }
    ] )
    .limit( 10 )
    .get();

for ( var post in posts ) {
    var authorName = post.getAuthor()?.getName() ?: "Unknown";
    writeDump( '"#post.getTitle()#" by #authorName#' );
}

Skipping Objects

Why?

Speed

Also, reports and picklists

Skipping Objects

var seriesSitemaps = seriesService
    .addLatestPublishedVideoDate()
    .orderByLatestPublishedVideo()
    .retrieveQuery()
    .get()
    .map( ( s ) => {
        return {
            "loc": event.buildLink( to = "sitemap/#s.slug#.xml", translate = false ),
            "lastmod": variables.$FORMATTER_ISO8601
                .format( parseDateTime( s.latestPublishedVideoDate ) )
                .replace( "Z", "+00:00" )
        };
    } );

Skipping Objects

  • Doesn't do eager loading
  • No Mementifier

CBDebugger