coldbox-modules / quick

A ColdBox ORM Engine
https://quick.ortusbooks.com
MIT License
23 stars 19 forks source link

v5 `belongsToThrough` does not emit where/join constraints #195

Closed davidAtInleague closed 10 months ago

davidAtInleague commented 2 years ago

Migrating a v4 application to v5 and addressing test failures as they arise, we found that belongsToThrough is emitting sql that does not include where/join constraints. Per the release notes / upgrade guide we haven't seen that belongsToThrough was expected to have a behavioral change.

grammar is mssql.

repro:

component table="RMME_A" extends="quick.models.BaseEntity" accessors=true {
    property name="ID_A" type="numeric" sqltype="integer";
    property name="ID_B" type="numeric" sqltype="integer";

    variables._key = "ID_A";

    function B() {
        return belongsTo(
            relationName = "RMME_B",
            foreignKey = "ID_B",
            localKey = "ID_B"
        );
    }

    function C() {
        return belongsToThrough(["B", "C"])
    }
}

component table="RMME_B" extends="quick.models.BaseEntity" accessors=true {
    property name="ID_B" type="numeric" sqltype="integer";
    property name="ID_C" type="numeric" sqltype="integer";

    variables._key = "ID_B";

    function C() {
        return belongsTo( relationName = "RMME_C", foreignKey = "ID_C", localKey = "ID_C" );
    }
}

component table="RMME_C" extends="quick.models.BaseEntity" accessors=true {
    property name="ID_C" type="numeric" sqltype="integer";

    variables._key = "ID_C";
}

//
// table defs
//
create table RMME_A (ID_A int, ID_B int);
create table RMME_B (ID_B int, ID_C int);
create table RMME_C (ID_C int);

insert into RMME_A (ID_A, ID_B) values (1, 1), (2, 2);
insert into RMME_B (ID_B, ID_C) values (1, 1), (2, 2);
insert into RMME_C (ID_C)       values (1),    (2);

//
// usage
//
v = getInstance("RMME_A").whereID_A(2).firstOrFail();

// quickV4
v.getID_A(); // 2 (ok)
/*
exec sp_executesql N'SELECT TOP (1) [RMME_C_1].[ID_C] FROM [RMME_C] AS [RMME_C_1] INNER JOIN [RMME_B] AS [RMME_B_2] ON [RMME_B_2].[ID_C] = [RMME_C_1].[ID_C] WHERE ([RMME_B_2].[ID_B] = @P0) select SCOPE_IDENTITY() AS GENERATED_KEYS',N'@P0 float',2
*/
v.getC().getID_C(); // 2 (ok)

// quickV5
v.getID_A(); // 2 (ok)
/*
SELECT TOP (1) [RMME_C_1].[ID_C] FROM [RMME_C] AS [RMME_C_1]
*/
v.getC().getID_C() // 1 (bad)