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

Up and Running with Quick

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