.withCount() on `hasManyThrough()` throws `arrays do not have the same lengths [4,2]` when intermediary relationship has a compound key

[DiscountDarcy]

We have an entity, qCompetitionSeasonDivision that translates to 'settings for this sports program (competition) for this division for this season' and a relationship where we want to see how many people have registered for that program in that division in that season.

Using a scope with .withCount() causes arrayZipEach() to throw The arrays do not have the same length [[4,2]].

Stripped down entity and relationships:

component table="competition_seasonal_divisional" extends="model.q.inLeagueBaseEntity" accessors=true {
	property name="compSeasonDivUID" column="compSeasonDivUID" type="string" sqltype="idstamp";
	property name="competitionUID"   column="competitionUID"   type="string" sqltype="idstamp";
	property name="seasonUID"        column="seasonUID"        type="string" sqltype="idstamp";
	property name="divID"            column="divID"            type="string" sqltype="idstamp";

    
function v_compSeasonDiv_forAllSourceCompsIncludingSelfSource() {
		return hasMany(
			relationName = "q_v_compSeasonDiv_forAllSourceCompsIncludingSelfSource",
			foreignKey = "compSeasonDivUID",
			localKey = "compSeasonDivUID"
		);
	}

	function competitionRegistrationsIncludingSourcedCompetitions() {
		return hasManyThrough(
			relationships = [ "v_compSeasonDiv_forAllSourceCompsIncludingSelfSource", "competitionRegistrations" ]
		)
	}

The intermediary entity:

component extends="model.q.inLeagueBaseEntity" table="v_competition_seasonal_divisional_forAllSourceCompsIncludingSelfSource" accessors=true {

	property name="compSeasonDivUID"     type="guid"    sqltype="idstamp" readonly=true update=false;
	property name="competitionUID"       type="guid"    sqltype="idstamp" readonly=true update=false;
    property name="seasonUID"            type="guid"    sqltype="idstamp" readonly=true update=false;
	property name="divID"                type="guid"    sqltype="idstamp" readonly=true update=false;
	property name="sourceCompetitionUID" type="guid"    sqltype="idstamp" readonly=true update=false;

	// This definition of the key is not true, but is required to play nice with hasOneOrManyThrough in left side of this "join" table (i.e. in compSeasonDiv).
	// Actual candidate keys are:
	//  - (sourceCompetitionUID, compSeasonDivUID, sourceCompetitionUID)
	//  - (sourceCompetitionUID, competitionUID, seasonUID, divID, sourceCompetitionUID)
	variables._key = 'compSeasonDivUID'

    function competitionRegistrations() {
        return hasMany(
            relationName = "qCompetitionRegistration",
            foreignKey = ["competitionUID", "seasonUID", "divID"],
            localKey = ["sourceCompetitionUID", "seasonUID", "divID"]
        )
    }

	function compSeasonDiv() {
		return belongsTo(
			relationName = "qCompetitionSeasonDivision",
			foreignKey = ["competitionUID", "seasonUID", "divID"],
            localKey = ["sourceCompetitionUID", "seasonUID", "divID"]
		)
	}
}

The following scope throws the error:

function scopeWithCurrentActiveRegistrationsCountIncludingSourcedCompetitions( qb ) {
		qb.withCount( {
			"competitionRegistrationsIncludingSourcedCompetitions as currentActiveRegistrationsCountIncludingSourcedCompetitions" : function(
				qCompetitionRegistration
			) {
				qCompetitionRegistration
					.where( ( qb ) => {
						qb.orWherePaid( 1 )
							.orWhereNotNull( "awaitingAsyncPaymentCompletion" )
					} )
					.whereCanceled( 0 )
			}
		} )
	}

Stack trace:

modules/quick/models/Relationships/BaseRelationship.cfc:609
modules/quick/models/Relationships/HasManyDeep.cfc:217
/root/modules/quick/models/QuickQB.cfc:990
modules/qb/models/Query/QueryBuilder.cfc:1661
modules/quick/models/QuickBuilder.cfc:775
modules/quick/models/Relationships/HasManyDeep.cfc:219
modules/quick/models/QuickBuilder.cfc:265
modules/quick/models/BaseEntity.cfc:1291
modules/quick/models/QuickBuilder.cfc:270
modules/quick/models/BaseEntity.cfc:3079
modules/quick/models/QuickBuilder.cfc:271
model/q/Competition/qCompetitionSeasonDivision.cfc:155

[davidAtInleague]

getQualifiedLocalKeys seems to have slightly different logic than getQualifiedForeignKeyNames, with the localKey variant seeming to handle arrays a little more robustly than the foreignKey one.

quick/models/Relationships/HasManyDeep.cfc

Line 233 in 4e2b199

foreignKeys.append( variables.related.qualifyColumn( variables.foreignKeys[ i ][ 2 ] ) );

quick/models/Relationships/HasManyDeep.cfc

Line 258 in 4e2b199

arrayWrap( variables.localKeys[ i ] ).each( function( localKey ) {

Here's a quick and dirty patch that gets a larger portion of our tests passing, @elpete does this look roughly in the ballpark:

--- a/modules/quick/models/Relationships/HasManyDeep.cfc
+++ b/modules/quick/models/Relationships/HasManyDeep.cfc
@@ -230,7 +230,12 @@ component
                for ( var i = 1; i <= variables.foreignKeys.len(); i++ ) {
                        if ( i > variables.throughParents.len() ) {
                                if ( isArray( variables.foreignKeys[ i ] ) ) {
-                                       foreignKeys.append( variables.related.qualifyColumn( variables.foreignKeys[ i ][ 2 ] ) );
+                                       foreignKeys.append(
+                                               variables.foreignKeys[ i ].map(
+                                                       (v) => variables.related.qualifyColumn(v)
+                                               ),
+                                               true
+                                       );
                                } else {
                                        foreignKeys.append( variables.related.qualifyColumn( variables.foreignKeys[ i ] ) );
                                }

[davidAtInleague]

I added a failing test for our use case, and then a fix for it, but it breaks a few other tests in tests/specs/integration/BaseEntity/Relationships/QueryingRelationshipsSpec.cfc.

It seems like polymorphic relationships generate a composite key to include the discriminator column, and getQualifiedForeignKeyNames assumed it could pluck out the non-discriminator keys; but in a non-polymorphic scenario the entirety of a composite key needs to be retained. So fixing it to support our non-polymorphic case means breaking the polymorphic case.

https://github.com/davidAtInleague/quick/tree/hasManyDeep-key-cardinality

[elpete]

This should be fixed in the next major version of Quick.

I'll give you folks at inLeague this, you sure stretch Quick to its limits. 😉