Yii2: ActiveQuery “with” not working

Question

Circumstances

I have three models/db-tables related with 1:n each: An order has multiple commissions and a commission has multiple commission_positions. Therefore the commission_position has an FK-field containing a commission id. The commission itself has an FK-field containing the id of an order.

Order > Commission > CommissionPositions

Problem

What I need to do now is select all the CommissionPositions having a certain value in the related Order-Model. Obvious solution is to use the Query-Object of CommissionPosition which I extended with a named scope. The named scope looks like this:

class CommissionPositionQuery extends ActiveQuery
{
   /**
     * Named scope to filter positions of a certain alpha order id
     * @param integer $id the alpha order id
     * @return \common\models\query\CommissionPositionQuery
     */
    public function alphaOrderId($id)
    {
        //TODO: with not working
        $this->with(['commission.order']);
        $this->andWhere(['alpha_order_id'=>$id]);
        return $this;
    }
}

The relation commission is defined on the Commission-Model and working. The second relation order is defined on the commission-model and working as well. The filtered field alpha_order_id is in the Order-Table. Now I execute the query like this:

$filteredPositions = CommissionPosition::find()->alphaOrderId(17)->all();

The scope is called successfully and the where-part is used, but when I check the generated SQL I see no join-statements even though I use the with-method to tell yii to fetch the relation together. The response is 'unknown column alpha_order_id' which makes sense as there is no join to the related tables. This is the generated SQL:

SELECT * FROM `commission_position` WHERE (`alpha_order_id`=17)

What am I missing? Is this a bug of Yii2?

Answer 1

If you want a JOIN use:

$this->joinWith(['commission.order']);

Answer 2

Found the soution myself. The naming changes between Yii1 and Yii2 lead to a little confusion. To prevent others from wasting time here the details:

Yii1

In yii 1 you would join in a relation (exemplary: commission) directly like this:

$query->with = 'commission'
$query->together = true;

Yii2 / difference

When calling the with-method like showed in the question the relation was successfully added to the with-array of the ActiveQuery. However, when executing the query, the join part was missing.

Solution

Seems like the with-method is NOT the way to go. Instead I used the method called joinWith with the following signature:

public function joinWith($with, $eagerLoading = true, $joinType = 'LEFT JOIN')

Now as described in the answer I defined the relation as the first argument ('commission.order') and left the rest as is, because the default values are perfectly fine. Pay attention to the default value of the second parameter. this makes sure the relations are joined in directly!

Voilà...the resulting sql contains the needed joins. One pitfall is to be considered though: Ambigious column namings is of course to be handled by ourselves! Link to the documentation of the method:

http://www.yiiframework.com/doc-2.0/yii-db-activequery.html#joinWith()-detail