coldbox-modules
.withCount() on `hasManyThrough()` throws `arrays do not have the same lengths [4,2]` when intermediary relationship has a compound key
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
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.
https://github.com/coldbox-modules/quick/blob/4e2b1996ea902c1ec523d41b39ffd1f93c5b0c4e/models/Relationships/HasManyDeep.cfc#L233
https://github.com/coldbox-modules/quick/blob/4e2b1996ea902c1ec523d41b39ffd1f93c5b0c4e/models/Relationships/HasManyDeep.cfc#L258
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 ] ) );
}
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
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. 😉
