Yii2: With() not considering where statement

[abude]

What steps will reproduce the problem?

Try to make something like this:

Listing::find()
            ->with(['package' => function($query){
            $query->andWhere('price', '0');
            }])
            ->andWhere(['customer_id' => $this->customer_id, 'status' => 'active'])->count();

the resulted query will be like this with(rawSql):
SELECT * FROM `zzz_listing` WHERE (`customer_id`=1) AND (`status`=\'active\')

If you do this:

Listing::find()
            ->innerJoinWith('package')
            ->where(['customer_id' => $this->customer_id, 'status' => 'active', '{{%listing_package}}.price' => 0])
            ->count();

The resulted query would be:

SELECT `zzz_listing`.* FROM `eas_listing` INNER JOIN `zzz_listing_package` ON `zzz_listing`.`package_id` = `zzz_listing_package`.`package_id` WHERE (`customer_id`=1) AND (`status`=\'active\') AND (`zzz_listing_package`.`price`=0)

What is the expected result?

The expected result should be the same with with() as with innerJoinWith.

What do you get instead?

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.13.1
| PHP version | 5.4.45
| Operating system | Centos 7

[Renkas]

Why should with() and innerJoinWith() give same query? They do different things for a reason.
with() is for getting eager loaded relational data for the model. Not to create a inner join'ed query - which is a different thing.

[twisted1919]

@Renkas = It shouldn't. Based on the above, it seems that when calling count() it just ignores the relation and it generates a totally different query than what it suppose to create, i.e: we're expecting to see an inner join and a condition on the inner joined table but we don't.

[Renkas]

But it should. with() and innerJoinWith() are different methods and do different things. They are not aliases. with() should not in any way interfere with a query/results. It's just to eager load relations. Everything is working exactly as it should.

I don't even get how can anyone misinterpret those methods? Should there be clearer documentation? The names themselves are quite self explanatory.

[twisted1919]

Maybe add a note in the docs, that with is only used to bring back the related records and does not alter the main query at all.

Right now if you look at http://www.yiiframework.com/doc-2.0/yii-db-activequerytrait.html#with()-detail it says nothing about this, moreover, it says Specifies the relations with which this query should be performed. and then gives an example like

Customer::find()->with([ 'orders' => function (\yii\db\ActiveQuery $query) { $query->andWhere('status = 1'); }, 'country', ])->all();

Which can be so simply misinterpreted. Nowehere does it says that the callback that modifies the query applies to the relation only but not to the main query.

[njasm]

indeed withand innerJoinWith are very confusing at first contact.

I struggled with the concepts cos the method names are using the same word.
Do we really need the With word in the innerJoin method? for 2.1.x ?!

It's confusing...

[dynasource]

@twisted1919, are you willing to create pull request with improvements of the docs that are necessary in your opinion?