[Quick 5] Can an Entity Have Properties from Multiple Tables?

Communities Quick ORM
1

This is probably mostly meant for @elpete, but I would love to get input from other CFers as well.

I have been curious if it possible to define a Quick entity with properties spanning multiple tables. My understanding is that you can use subselects to grab data from other tables. If you want to get more than one column, you can add more subselect calls like this:

function scopeAddLastLogin( qb ) {
    qb.addSubselect( "lastLoginDate", "logins.timestamp" );
    qb.addSubselect( "lastLoginIPAddress", "logins.ipAddress" );
}

The downside of the above approach is that the underlying SQL that gets generated is not very efficient and looks something like this:

SELECT 
  [user].[id],
  // subselect 1
  (
    SELECT 
      TOP (1) [logins].[timestamp] 
    FROM 
      [logins] 
    WHERE 
      (
        [user].[id] = [logins].[userId]
      )
  ) AS [lastLoginDate] 
// subselect 2
  (
    SELECT 
      TOP (1) [logins].[ipAddress] 
    FROM 
      [logins] 
    WHERE 
      (
        [user].[id] = [logins].[userId]
      )
  ) AS [lastLoginIPAddress] 
FROM 
  [user]

The same query would be more efficient (and readable) if it were written like this:

SELECT 
  [user].[id],
  [logins].[timestamp] as [lastLoginDate],
  [logins].[ipAddress] as [lastLoginIpAddress]
FROM 
  [user] 
  LEFT JOIN [logins] ON [user].[id] = [logins].[userId]

Quick already has the ability to JOIN on different tables through the multi-table inheritence (MTI) pattern using discriminated entities.

Possible workaround (Views):
If the user has access to the database, they could probably use a view for this type of thing. However, it would be cooler if it could be done via Quick.

Idea 1:
Perhaps Quick could add some additional logic to detect multiple subselects from the same table and then dynamically create a join instead of multiple nested subselects. Alternatively, perhaps subselects should originate from a regular JOIN statement similar to my second SQL example above.

Idea 2:
Alternatively, perhaps we could define one or more join tables in the component definition and then specify which table a particular property should originate from. For example:

component 
    extends="quick.models.BaseEntity"
    accessors="true"
    table="user"
    left_joins="logins:id:userId" <-- comma separated list of tables to join + the PK and FK(s) to use

Then, when declaring properties, we could use the existing column attribute to point to the joined table:

property name="id";
property name="lastLoginDate" column="logins.timestamp";
property name="lastLoginIpAddress" column="logins:ipAddress";

Anyway, just some food for thought on how I think Quick could be improved for even greater flexibility. :slight_smile:

2

I just had another thought which might work, if you wanted to make the join a global scope for the entity. You could add the properties as normal and use the columns attribute to point to the columns you want from the joined table and use the global scopes to always include a join statement.

property name="lastLoginDate" column="logins.timestamp";
property name="lastLoginIpAddress" column="logins.ipAddress";

function scopeAddLastLogin( query ) {
    // modify the query builder and create the join
    var sub = query.newQuery()
       .select( [ "logins.timestamp", "logins.ipAddress", "logins.userId" ] )
       .from( "logins" );

    // apply the join to the entity query    
    query.leftJoinSub( "logins", sub, "logins.userId", "=", "user.id" );
}

// make sure all instances have access to the joined table. This is part of the entity now!
function applyGlobalScopes() {
    this.addLastLogin();
}
3

Let me give you some other ideas using a slightly different example.

In this case, let’s imagine a User <-> Account 1-to-1 relationship.

One option for you is to do the join yourself in your scope:

function scopeAddAccountInfo( qb ) {
    qb.join( "accounts", "users.id", "accounts.userId" );
    qb.addSelect( "accounts.phoneNumber );
    qb.addSelect( "accounts.streetAddressOne" );
    qb.appendVirtualAttribute( "phoneNumber" );
    qb.appendVirtualAttribute( "streetAddressOne" );
}

This would avoid the subselects.

It would be a nice helper to have the QuickBuilder be able to join a relationship.

function account() {
    return hasOne( "Account" );
}

function scopeAddAccountInfo( qb ) {
    qb.join( "account" ); // perhaps `joinRelationship`?
    qb.addSelect( "accounts.phoneNumber );
    qb.addSelect( "accounts.streetAddressOne" );
    qb.appendVirtualAttribute( "phoneNumber" );
    qb.appendVirtualAttribute( "streetAddressOne" );
}

Then you have the flexibility to join when needed.

Back to your example, it actually is more tricky than you mention. A leftJoin in this case would return multiple records for each User which is not the desired outcome. You can accomplish this with dynamic relationships. You can read about it here in the Quick docs or here by Jonathan Reinink.

1 Like
4

@elpete, I like your solution even better as it’s much more readable.

Regarding the leftJoin problem and multiple records, the way I’ve handled this in vanilla SQL before is by using an aggregate MAX( id ) statement in the join. Another possible solution would be to use CROSS APPLY or OUTER APPLY but I do not think that is compatible in MySQL engines.

EDIT: I had another thought… you could also do something like this:

qb.leftJoin( "logins", "logins.userId", "=", "user.id" )
qb.whereRaw( "logins.id = ( SELECT MAX( id ) FROM [logins] )" ); // only get the latest record
qb.addSelect( "logins.timestamp" );
qb.appendVirtualAttribute( "timestamp" );

EDIT 2: Another idea using joins. I actually like this method better because it handles missing records and allows for more filtering in the join. I am not sure how cross-compatible it is with non MSSQL database engines though:

Note: I changed the names of the database tables & columns here in this example

function scopeAddLastLogin( qb ) {
    // outer join with the login log table
    qb.leftJoinSub( "loginLog", function( q ) {
        q.select( "loginLog.emailAddress,loginLog.createdDate,loginLog.ipAddress" )
            .from( "loginLog" )
            // restrict to the most recent successful login attempt
            .rightJoinSub( "loginLog2", function( q ) {
                q.from( "loginLog" )
                    .selectRaw( "MAX( id ) as id" )
                    // only successful logins
                    .where( "loginLog.success", "=", 1 )
                    .groupBy( "loginLog.emailAddress" )
            }, "loginLog2.id", "=", "loginLog.id" ); // alias the restriction join
    }, "loginLog.emailAddress", "=", "user.emailAddress" );
    
    // select the additional columns from the join
    qb.selectRaw( "loginLog.createdDate as lastLoginDate" );
    qb.selectRaw( "loginLog.ipAddress as lastLoginIpAddress" );
    qb.appendVirtualAttribute( "lastLoginDate" );
    qb.appendVirtualAttribute( "lastLoginIpAddress" );
}