Silverstripe-framework: ORM failure on filterby join alias

[pitchandtone]

I have an ORM failure on filter by join alias

FirstClass::get()
->leftJoin(SecondClass::class, 'SecondClass.ID = FirstClass.SecondClassID', 'table1')
->filter(array(
'table1.Year' => 2017
));

table1 is not a relation on model FirstClass

[pitchandtone]

Seems similar-ish to #3839, or #4429 but I have no idea where to start with this.

[robbieaverill]

Hey @pitchandtone - the first argument for DataList::leftJoin is $tableName, which isn't necessarily the same as the FQCN. Could you try this, and if it doesn't work, post the result of ->sql() from this list?

FirstClass::get()
    ->leftJoin(DataObject::getSchema()->tableName(SecondClass::class), 'SecondClass.ID = FirstClass.SecondClassID', 'table1'))
    ...

Edit: SS4 assumed due to ::class syntax being used

[pitchandtone]

currently using DataObject::config()->get('tablename'), is that equivalent?

[robbieaverill]

It should be, but probably best to use the DataObjectSchema for consistency

[pitchandtone]

Same issue:
SilverStripe\ORM\DataQuery->applyRelation(Array)
ExactMatchFilter.php:54

[robbieaverill]

Ah OK. Are you able to get the SQL query it's running out at all?

[pitchandtone]

seems like it's failing before the query is made. ?showqueries=1 does give me an output.

[pitchandtone]

Seems like "where" works fine. By filter or filterAny do not. I'll work around in the meantime.

[robbieaverill]

OK I've done a quick investigation and this does look like a regression from SS3.

Here's a test for this behaviour:

diff --git a/tests/php/ORM/DataListTest.php b/tests/php/ORM/DataListTest.php
index 363bc43d9..bd57ac782 100755
--- a/tests/php/ORM/DataListTest.php
+++ b/tests/php/ORM/DataListTest.php
@@ -1050,6 +1050,22 @@ class DataListTest extends SapphireTest
         $this->assertEquals(array_intersect($values, array('Joe', 'Bob')), $values);
     }

+    public function testFilterOnJoinWithAlias()
+    {
+        $list = TeamComment::get()
+            ->leftJoin(
+                'DataObjectTest_Team',
+                '"MyTeamAlias"."ID" = "DataObjectTest_TeamComment"."TeamID"',
+                'MyTeamAlias'
+            )->filter([
+                'MyTeamAlias.Title' => 'Team 1',
+            ]);
+
+        $this->assertEquals(2, $list->count());
+        $values = $list->column('Name');
+        $this->assertEquals(array_intersect($values, ['Joe', 'Bob']), $values);
+    }
+
     public function testFilterOnImplicitJoin()
     {
         // Many to many

This fails in SS4:

There was 1 error:

1) SilverStripe\ORM\Tests\DataListTest::testFilterOnJoinWithAlias
InvalidArgumentException: MyTeamAlias is not a relation on model SilverStripe\ORM\Tests\DataObjectTest\TeamComment

/Users/robbieaverill/dev/ss4/vendor/silverstripe/framework/src/ORM/DataQuery.php:875
/Users/robbieaverill/dev/ss4/vendor/silverstripe/framework/src/ORM/Filters/ExactMatchFilter.php:54
/Users/robbieaverill/dev/ss4/vendor/silverstripe/framework/src/ORM/Filters/ExactMatchFilter.php:31
/Users/robbieaverill/dev/ss4/vendor/silverstripe/framework/src/ORM/Filters/SearchFilter.php:363
/Users/robbieaverill/dev/ss4/vendor/silverstripe/framework/src/ORM/DataList.php:134
/Users/robbieaverill/dev/ss4/vendor/silverstripe/framework/src/ORM/DataList.php:418
/Users/robbieaverill/dev/ss4/vendor/silverstripe/framework/src/ORM/DataList.php:403
/Users/robbieaverill/dev/ss4/vendor/silverstripe/framework/tests/php/ORM/DataListTest.php:1061
/Users/robbieaverill/dev/ss4/vendor/phpunit/phpunit/phpunit:52

But passes in SS 3.6.1. Note that MySQL requires you to use the alias in the condition for the join if you've set one.

[dhensby]

filter is for use with the ORM and not for use with custom aliases that you've created in the raw SQL query.

filter will look for a resolvable table/column that matches MyTeamAlias.Title and it won't be able to because there is no relation called MyTeamAlias.

There has been work done in 4.x that means join tables are successfully added without collisions (see: #6939).

I think the recommendation is: if you want to write custom queries, write custom queries, don't mangle DataLists.

Assuming SecondClass.ID and FirstClass.SecondClassID is a relation defined in the ORM there is no need to be manually specifying left joins and aliases.

[dhensby]

I'm closing this as "not a bug" as the error is correct (that "MyTeamAlias is not a relation on model"). Happy to re-open if there's an actual error with doing this solely through the ORM methods or when constructing a query through the dataQuery builder

[pitchandtone]

Currently it’s not clear that filter is only for use with ORM and won’t work with join aliases, could the error be updated to make this clear?

X is not a relation on y, if x is a table alias use “where” instead

Or similar?

Sent from my iPhone

On 5/12/2017, at 4:35 AM, Daniel Hensby [e-mail] wrote:

I'm closing this as "not a bug" as the error is correct (that "MyTeamAlias is not a relation on model"). Happy to re-open if there's an actual error with doing this solely through the ORM methods or when constructing a query through the dataQuery builder

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[dhensby]

Sure, I'm happy to make the error more explicit/helpful - do you want to PR?

[tractorcow]

Wouldn't the correct ORM code be either:

$list = TeamComment::get()->filter([
    'Team.Title' => 'Team 1', // Because `Team` IS a relation on `TeamComment`
]);

Or

$list = TeamComment::get()
    ->leftJoin(
        'DataObjectTest_Team',
        '"MyTeamAlias"."ID" = "DataObjectTest_TeamComment"."TeamID"',
        'MyTeamAlias'
    )->where('"MyTeamAlias"."Title" => ?' => 'Team 1');

If you want to use a custom alias.