Join in SearchModel - too many queries to get joined table field

Yii 2.0 General Discussions
1

I have a model with its related search model class:

class BlogPostSearch extends BlogPost
    {
public function search($params)
    {
        $query = BlogPost::find();

    // add conditions that should always apply here

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

    $query->joinWith('authorK');

    $query->join('INNER JOIN', Profile::tableName()." editor", self::tableName().'.editor_id = editor.profile_id');

The author and the editor are the same model, and obviously same table.
So if i join both without table alias i get an error (obviously).
But if i use an alias i can’t use the default conditions i put in the ProfileQuery class (i need to add the table name cause there are multiple tables with the “status” field

    class ProfileQuery extends \yii\db\ActiveQuery
    {
        public function init(){
            $this->andOnCondition(['not',[Profile::tableName().'.status'=>2]]);
            parent::init();
        }

With the join for editor in place of joinWith it works.
Anyway, in the GridView i need to list the blogposts showing the author’s name and the editor’s name.
I do it like this

        [
            'attribute'=>'editor_id',
            'value'=>'profileK.name',
        ],

The problem is for EVERY record showed in GridView a different query is executed to retrieve the name. (i can see it in the debugger).

How can i prevent that?
Unfortunately, if i’m not wrong, we won’ be able to get the current alias used in an ActiveQuery in this Yii version (should be available in yii3)

2

What’s “profileK” relation for? Isn’t it a relation for “editor”?

I think you could use it like the following:

$query->joinWith(['authorK a', 'profileK e']);

where a is the alias for profile table for the author, e for the editor.

Guide > ActiveRecord > Joining with Relations
https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#joining-with-relations

3

Yes, profileK (actually editorK, there was an error in my code) is that relation.
The problem is with an alias i can’t set some default where conditions in the ProfileQuery. (see above code, ProfileQuery part) cause the where condition it adds on init is added with the profile table name. I could remove the Profile::tableName() part but i would get another error (since i’m joining multiple times the same table and the status column is ambiguous)

4

I just tried the following dirty trick, and it worked for me. I’m not sure it will work for you also.

class ProfileQuery extends \yii\db\ActiveQuery
{
    public function init()
    {
        list($tableName, $alias) = $this->getTableNameAndAlias();
        $this->andOnCondition(['not', [$alias . '.status' => 2]]);
        parent::init();
    }

    private function getTableNameAndAlias()
    {
        if (empty($this->from)) {
            $tableName = $this->getPrimaryTableName();
        } else {
            $tableName = '';
            foreach ($this->from as $alias => $tableName) {
                if (is_string($alias)) {
                    return [$tableName, $alias];
                }
                break;
            }
        }

        if (preg_match('/^(.*?)\s+({{\w+}}|\w+)$/', $tableName, $matches)) {
            $alias = $matches[2];
        } else {
            $alias = $tableName;
        }

        return [$tableName, $alias];
    }
}

The method getTableNameAndAlias is a dead copy from \yii\db\ActiveQuery. It’s a private method so I couldn’t call it from the extended class.

5

The following will be a more decent solution for your needs:

    $query->with(['authorK', 'editorK']);

    $query->innerJoin(Profile::tableName()." author", self::tableName().'.author_id = author.profile_id');
    $query->innerJoin(Profile::tableName()." editor", self::tableName().'.editor_id = editor.profile_id');

This will eagerly load authorK and editorK relations, and will solve the problem you mentioned.