CODEWITHSUNDEEP
mysqljoininner-joinupgradesilverstripe
Manuel Tomasir
Manuel Tomasir

Reputation: 11

Silverstripe 2.4 - 3.1 Upgrade - innerJoin 'unknown column' issue

I am trying to update a website from SS 2.4 to SS 3.1 and have been digging around the web on this issue for a while now.

The old code looks like this...

return DataObject::get('SupportItem', "SupportItemType = '$itemType' AND ProductPageID = $productID", null, 'INNER JOIN SupportItem_Products ON SupportItem_Products.SupportItemID = SupportItem.ID');

I am trying to switch out of the deprecated INNER JOIN and DataObject::get to the now current innerJoin and DataobjectName::get. This is what I have for the new code

$productID = $this->productToView->ID;
return SupportProductListingPage::get()->innerJoin('SupportItem_Products', '"SupportItem_Products"."SupportItemID" = "SupportItem"."ID"', null)->filter(array('SupportItemType'=>'$itemType', 'ProductPageID' => '$productID'));

It should be noted that the "SupportItemID" column exists in "SupportItem_Products" and the "ID" column exists in "SupportItem". However, "SupportItemID" does not exist in the "SupportItem" table.

I am receiving the below error when loading the page...

[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "SiteTree"."ID") AS "0" FROM "SiteTree" LEFT JOIN "Page" ON "Page"."ID" = "SiteTree"."ID" INNER JOIN "SupportItem_Products" ON "SupportItem_Products"."SupportItemID" = "SupportItem"."ID" WHERE ("ProductPageID" = '$productID') AND ("SiteTree"."ClassName" IN ('SupportProductListingPage')) Unknown column 'SupportItem.ID' in 'on clause'

Can anyone help?

Upvotes: 1

Views: 394

Answers (1)

Olli Tyynelä
Olli Tyynelä

Reputation: 576

It seems that you are doing the join wrongly. The error message says that Unknown column 'SupportItem.ID' in 'on clause.

The original starts with:

DataObject::get('SupportItem', "SupportItemType = '

And yours starts with:

return SupportProductListingPage::get()->innerJoin('SupportItem_Products',

This says basically says join SupportItem_Products with SupportProductListingPage with a table that`s not part of your query at all.

This should be the innerjoin that you actually need (not tested of course):

SupportItem::get()->innerJoin('SupportItem_Products','"SupportItem_Products"."SupportItemID" = "SupportItem"."ID"');

With that the query should be right if the class relations are declared with the right has/belongs variables.

Also your filter bit wont probably work as expected:

filter(array('SupportItemType'=>'$itemType', 'ProductPageID' => '$productID'))

You are trying to use a variable inside single quotes. So either do

'SupportItemType'=>"$itemType" or 'SupportItemType'=>$itemType

Upvotes: 1

Related Questions