How to filter GridView in Yii2 by related columns of the same table?

Question

Yii

There is a table organizations with the following columns:
id - id of the organization
short_name - short name of the organization
full_name - full name of the organization
parent_organization - name of the parent organization.
The parent_organization field contains the organization id from the same table.
The attribute value displays the short name of the parent organization: . The Organizations model has a getter:'value' => 'parentOrganization.short_name'

public function getParentOrganization()
{
    return $this->hasOne(Organizations::className(), ['id' => 'parent_organization']);
}

It is necessary to configure the GridView filter in such a way that when searching by the parent_organization field, a correct query is generated to the database to match the entered search query with the contents of the short_name field of the parent organization.
At the moment, when writing a compound query, the database (PostgreSQL) swears at the re-access to the same table.
The database query itself should look like this:

select * from organizations_schema.organizations where parent_organization = ( select id from organizations_schema.organizations where short_name like '%Поисковый_запрос%' );

Thanks in advance!

Answer 1

class OrganizationsSearch extends Organizations
{

    public $parent_short_name;

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['parent_short_name'], 'string'],

            // other rules
        ];
    }


    public function search($params)
    {
        $query = Organizations::find();
        $query->alias('org');

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

        $query->joinWith(['parentOrganization' => function($q) {
            /**
             * @var ActiveQuery $q
             */

            $q->alias('par_org');
        }]);

        $this->load($params);

        $dataProvider->setSort([
            'attributes' => [
                'parent_short_name' => [
                    'asc' => ['par_org.short_name' => SORT_ASC],
                    'desc' => ['par_org.short_name' => SORT_DESC],
                ],

                // other attributes
            ],
            'defaultOrder' => [
                // defaultOrders
            ],
        ]);

        if (!$this->validate()) {
            return $dataProvider;
        }

        $query->andFilterWhere(['ilike', 'org.short_name', $this->short_name]);
        $query->andFilterWhere(['ilike', 'par_org.short_name', $this->parent_short_name]);

        return $dataProvider;
    }
}

in GridView:
'attribute' => 'parent_short_name',