Field Not quoted for fetching relation data with joinWith

[avinashz]

I am having three tables.

• User
• Regions
• Country

Relations are built like below:

• User hasMany Regions
• Regions hasOne Country

I have relation build for fetching regions of user in User Model and one more relation is there in Region Model to get related country.

If I do eager loading like this the my Primary key of region is not quoted. Let me show you waht works and what not.

Works:

$userModel->getRegions()->with('country')->all();

This works may be becuase we will have separate query. Correct?

Field Not Quoted:

$userModel->getRegions()->joinWith('country')->all();

In MySQL it will work but in case of PG, that is required to have the field quoted specially when you have camel casing. Have a look at generated query.

SELECT "region".* FROM "region" LEFT JOIN "country" ON "region"."CountryID" = "country"."CountryID" WHERE "region".UserID=8

You can see that last field which is primary key of my User model is not quoted.

PHP: 5.5.12
Yii2: 2.0.7

Am i missing something here or its bug over there?

[SilverFire]

Please, show your getCounty method in Regions model

[avinashz]

Here it goes, this is correct because it is fetching correct data when I use with instead of joinWith.

public function getCountry() {
    return $this->hasOne(Country::className(), ['CountryID' => 'CountryID']);
}

[CedricYii]

It may better come from the method getRegions in User model as suggested by region.userid. May we have a look?

[SilverFire]

WHERE "region".UserID=8

From where does this condition come?

[avinashz]

@CedricYii Here goes my getRegions relation method

public function getRegions() {
        return $this->hasMany(Region::className(), ['UserID' => 'UserID']);
}

@SilverFire $userModel is an ActiveRecord object of User model which is having UserID = 8

[SilverFire]

Could you create a failing test, please?

[avinashz]

Could you please elaborate on what I need to do?

[SilverFire]

I'm asking you to create a test case that shows wrong quoting

[avinashz]

Well, I am not good at test cases, but scenario I have mentioned already as above. Confusing part is while using with it works but not while using joinWith, which says relation setup is correct is code.

[CedricYii]

Hard to tell, maybe in the primarykey() method. Can you post the models and methods involved and also the controller part to see how is assigned the userid?

[avinashz]

Sure I will post details soon. fyi, I have not modified primarykey() method.

[avinashz]

Here goes the details:

My Table Schema:

CREATE TABLE `order` (
  `orderid` int(11) NOT NULL AUTO_INCREMENT,
  `amount` varchar(45) NOT NULL,
  PRIMARY KEY (`orderid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

CREATE TABLE `order_item` (
  `order_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `orderid` int(11) NOT NULL,
  `itemid` int(11) NOT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

CREATE TABLE `item` (
  `itemid` int(11) NOT NULL AUTO_INCREMENT,
  `itemname` varchar(45) NOT NULL,
  PRIMARY KEY (`itemid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

Controller Code:

$order = \app\models\Order::findOne(1);
$items = $order->getOrderitems()->joinWith('item')->all();

Query Generated:

SELECT `order_item`.* FROM `order_item` LEFT JOIN `item` ON `order_item`.`itemid` = `item`.`itemid` WHERE `order_item`.orderid=1

Please note the quotes in WHERE condition.

Model Code:

Attached Model Files in txt format

Item.txt
Order.txt
OrderItem.txt

Let me know if I have missed anything.

All I can say is, when you already have AR object and trying to fetch related data by calling relation method and along with fetching nested data and using joinWith, in this case it is skipping the quotes on primary key of your AR Object.