How to compare values from joined tables in Yii2 ActiveDataProvider

Question

I'm trying to make a search class to power a gridview widget. The issue is that I need to compare the values from the primary table and from a joined table.

I'm doing this in the search class:

$query = User::find();
$query->joinWith(['rank']);

and then in my filters I want something like:

$query->andFilterWhere(['>=', 'user.rank_points', 'rank.promotion_points']);

This does not work though, because the third parameter, the rank.promotion_points gets escaped as a string, and is not treated as a mysql field.

I had tried using the relation to output the value like:

 $query->andFilterWhere(['>=', 'user.rank_points', $this->rank->promotion_points]);

but that gives an error that $this does not have the rank property.

What's the proper way to accomplish this?

Edit, as requested, here's the raw query the above code produces:

SELECT `user`.*
FROM `user`
LEFT JOIN `rank`
ON `user`.`rank_id` = `rank`.`id`
WHERE (`rank_id` NOT IN (1, 2, 3, 4, 5, 6))
AND (`user`.`rank_points` >= 'rank.promotion_points')

But what I need is this:

SELECT `user`.*
FROM `user`
LEFT JOIN `rank`
ON `user`.`rank_id` = `rank`.`id`
WHERE (`rank_id` NOT IN (1, 2, 3, 4, 5, 6))
AND (`user`.`rank_points` >= `rank`.`promotion_points`)

The entire Method looks like this:

public function search($params)
{
    $query = User::find();
    $query->joinWith(['rank']);

    // add conditions that should always apply here

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    $this->load($params);

    if (!$this->validate()) {
        // uncomment the following line if you do not want to return any records when validation fails
        // $query->where('0=1');
        return $dataProvider;
    }

    // grid filtering conditions
    $query->andFilterWhere([
        'id' => $this->id,
        'rank_id' => $this->rank_id,
        'created_at' => $this->created_at,
        'updated_at' => $this->updated_at,
    ]);

    $query->andFilterWhere(['like', 'username', $this->username]);
    $query->andFilterWhere(['not in', 'rank_id', [1, 2, 3, 4, 5, 6]]);
    $query->andFilterWhere(['>=', 'user.rank_points', 'rank.promotion_points']);

    return $dataProvider;

rank table schema:

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| name             | varchar(255) | NO   | UNI | NULL    |                |
| rank_points      | int(11)      | YES  |     | NULL    |                |
| promotion_points | int(11)      | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

Answer 1

You can pass this condition as string:

$query->andWhere('user.rank_points >= rank.promotion_points');

or use Expression:

$query->andWhere(['>=', 'user.rank_points', new \yii\db\Expression('rank.promotion_points')]);