Disambiguing column's table on ActiveQuery select/where/order etc.

[petrabarus]

I have several tables that have columns with same name. Let's say shelf, book, chapter, have the same field name.

I have a page that will show list of chapter that will also show the name of the book as well as the shelf.

Naturally this is the query I used for the ActiveDataProvider

Chapter::find()
  ->joinWith([
      'book' => function($query) {
          $query->joinWith([
              'shelf'
          ])
      }
  ])

But I only wanted to show the name in the GridView, so this is what I do to avoid the SELECT *.

Chapter::find()
  ->addSelect(['name', 'bookId'])
  ->joinWith([
          'book' => function($query) {
              $query->addSelect(['name', 'shelfId'])
                         ->joinWith([
                                'shelf' => function($query){
                                    $query->addSelect(['name']);
                                }
                         ]);
          }
  ])

On my Yii2 (c21895d4dd4c18d5bab37e0b7b359668f8aa8b67) this will output error Column 'name' in field list is ambiguous. So I have to put something like this

Chapter::find()
  ->addSelect(['Chapters.name', 'bookId'])
  ->joinWith([
          'book' => function($query) {
              $query->addSelect(['Books.name', 'shelfId'])
                         ->joinWith([
                                'shelf' => function($query){
                                    $query->addSelect(['Shelves.name']);
                                }
                         ]);
          }
  ])

Do I really have to do this for every query like this? Or is there any simpler way I don't know?

I'm thinking that if I can disambiguate the table name right from the addSelect method, it would be much easier. I extend the ActiveQuery and do something like this.

    private $_tableName;
    private function getTableName() {
        //since the `from` will be resolved in the `prepare` method.
        if (!isset($this->_tableName)) {
            $class = $this->modelClass;
            $this->_tableName = $class::tableName();
        }
        return $this->_tableName;
    }
    public function addSelect($columns) {
        if (is_array($columns)) {
            $columns = array_map(function($column) {
                return (strpos($column, ".") == false) ? $this->getTableName() . ".{$column}" : $column;
            }, $columns);
        } else {
            $columns = (strpos($column, ".") == false) ? $this->getTableName() . ".{$columns}" : $columns;
        }
        return parent::addSelect($columns);
    }

[mdmunir]

mungkin sama dengan ini #2377

[petrabarus]

Yeah, pretty much the same with #2377, I've read that before this, but I forgot to mention. The issue stated that it's good to have a way where we can put alias to disambiguate the tables.

But I'm suggesting that, either way we set an alias or not, the select() and addSelect() should also put the table name (or pre-generated alias) to the column name if there is no table/alias set. e.g. ->select(['column']) will automatically result in Table.column (or generatedAlias.column), but ->select(['alias.column']) should stay the same, just in case the programmer want to put a easily-read alias.

[cebe]

can't you do it like this:

Chapter::find()
  ->select(['Books.name AS bookName', 'Shelfs.name AS shelf_name', 'bookId', /* ... */])
  ->joinWith(['book', 'book.shelf'])
  ->asArray()->all();

as far as I see there is no need for the nesting here.

[petrabarus]

I'm thinking that it would be good if there is automatic table aliasing to support code reusing. This should not only work on select or addSelect but also on where, order etc.

For example, I have a query for Books and Shelves class.

class Query extends \yii\db\ActiveQuery {
   public function isNotRemoved() {
      return $this->andWhere(['is_removed' => 0]);
   }
}

class ShelfQuery extends Query {
}

class BookQuery extends Query {
}

class Shelf extends \yii\db\ActiveRecord {
    public static find() {
       return \Yii::createObject(Shelf::class, [get_called_class()]);
    }
}

class Book extends \yii\db\ActiveRecord {
    public static find() {
       return \Yii::createObject(Shelf::class, [get_called_class()]);
    }
}

Naturally, we want to select chapters that are not removed in books that are not removed and in shelves that are also not removed.

It would be nice if we can just reuse the isRemoved() method to simplify the query instead of manually writing the table names again and again for the same part of query.

Chapter::find()
  ->joinWith([
      'book' => function(BookQuery $query) {
          $query->joinWith([
              'shelf' => function(ShelfQuery $query) {
                  $query->isNotRemoved();
              }
          ])->isNotRemoved();
      }
  ])->isNotRemoved();

In the current Yii version, the query above will throw error Ambiguous column is_removed. But if we can use aliasing similar with Yii1, the first isNotRemoved will use t.is_removed, the second one will use t2.is_removed (or use the relation name book.is_removed like Yii1), and the third one will use t3.is_removed (or shelf.is_removed).

This way, we can reuse methods in Query classes and get better "Don't Repeat Yourself"s.

[petrabarus]

Still reading the whole Query classes to start working on the pull request. But as the framework users so far, the ideas are below.

First, we should add alias attribute to the \yii\db\Query class.

namespace yii\db;

class Query extends Component implements QueryInterface {
   public $alias = 't';
}

and when we use select, where, order, etc, the attribute will automatically appended by the alias, unless it's already appended.

$query = (new Query())->select(['attr1', 'attr2'])->from('table')->where(['attr1' => 5])->order(['attr2' => SORT_ASC]);

for example above the query generated will be

SELECT t.attr1, t.attr2 FROM table t WHERE t.attr1 = 5 ORDER BY t.attr2 ASC

but of course this will break if users use alias in the from method and not using the alias in the select or other methods.

$query = (new Query())->select(['attr1', 'attr2'])->from('table u')->where(['attr1' => 5])->order(['attr2' => SORT_ASC]);

in the current yii version, the query above works if attr1 exists in the table table. But if we use the $alias attribute, this will break.

But the query below is safe.

$query = (new Query())->select(['u.attr1', 'u.attr2'])->from('table u')->where(['u.attr1' => 5])->order(['u.attr2' => SORT_ASC]);

Next in the ActiveQuery, when with method is used, the alias for the query for the relation will be replaced with the name of the relation.

Chapter::find()
  ->joinWith([
      'book' => function(BookQuery $query) {
          $query->joinWith([
              'shelf' => function(ShelfQuery $query) {
                  $query->isNotRemoved();
              }
          ])->isNotRemoved();
      }
  ])->isNotRemoved();

The query above will result on

SELECT t.*, book.*, shelf.* 
     FROM Chapters t 
        JOIN Books book ON t.book_id = book.id
        JOIN Shelves shelf ON book.shelf_id = shelf.id
     WHERE
        t.is_removed = 0 AND
        book.is_removed = 0 AND
        shelf.is_removed = 0;

[cebe]

Read this issue through and found similar to #9326. Automatic aliasing of all columns is hard to do right but we could add methods to retrieve tables aliases currently used in a query.

[mikk150]

consider this

class User extends yii\db\ActiveRecord
{
    public function attributes()
    {
        return ['id'];
    }

    public function tableName()
    {
        return 'user';
    }

    public function getUserBlogPosts()
    {
        return $this->hasMany(BlogPosts::className(), ['id' => 'user_id']);
    }
}

class BlogPosts extends yii\db\ActiveRecord
{
    public function attributes()
    {
        return ['id', 'tag_id', 'user_id'];
    }

    public function tableName()
    {
        return 'blog_posts';
    }
}

class TestController extends Controller
{
    public function actionTest()
    {
        User::find()->joinWith('userBlogPosts', false)->where(['userBlogPosts.id' => [46, 88, 13]]);
    }
}

wouldn't it be nice, if you do not have to remember, that userBlogPosts relation table name is blog_posts, instead wrap it with some characters(or it would auto-detect that hey I do not have any alias for userBlogPosts, maybe you meant relation userBlogPosts that is really blog_posts

[cebe]

The following is possible with 2.0.7 release, which will be out tomorrow:

class TestController extends Controller
{
    public function actionTest()
    {
        User::find()->joinWith('userBlogPosts upb', false)->where(['upb.id' => [46, 88, 13]]);
    }
}

You could even make it a habit to always use the relation name as the alias:

    public function getUserBlogPosts()
    {
        return $this->hasMany(BlogPosts::className(), ['id' => 'user_id'])->alias('userBlogPosts');
    }

so with the above relation definition the action would work as you wrote it.

[petrabarus]

@cebe

can you give example how should I write the refactored isRemoved method in the above example I gave.

class Query extends \yii\db\ActiveQuery {
   public function isNotRemoved() {
      return $this->andWhere(['is_removed' => 0]);
   }
}

class ShelfQuery extends Query {
}

class BookQuery extends Query {
}

class Shelf extends \yii\db\ActiveRecord {
    public static find() {
       return \Yii::createObject(Shelf::class, [get_called_class()]);
    }
}

class Book extends \yii\db\ActiveRecord {
    public static find() {
       return \Yii::createObject(Shelf::class, [get_called_class()]);
    }
}

[cebe]

@petrabarus I have no good solution for this case yet, that's why this issue is still open. I'd like to see even more use cases to build a solution for them.

[mikk150]

@cebe it would be real nice, because then we can finally do soft-delete easily

[petrabarus]

@cebe just skimmed the #10813 and #10253, can we do something like this?

class Query extends \yii\db\ActiveQuery {
   public function isNotRemoved() {
      $alias = $this->getAlias();
      return $this->andWhere(["`{$alias}`.is_removed" => 0]);
   }
}

[petrabarus]

or maybe we can have a shorthand placeholder for current alias.. like {{}}.

class Query extends \yii\db\ActiveQuery {
   public function isNotRemoved() {
      return $this->andWhere(['{{}}.is_removed' => 0]);
      //string {{}} will be automatically replaced with the current alias.
   }
}

[mikk150]

@petrabarus that {{}} thing, I don't think it could be a good idea

[sasha-x]

Now I use:

class ActiveRecord {
    /**
     * We can specify "default" table alias here in addition to tableName() for using it in SQLs.
     * By default alias is equal to table name.
     * @return string
     */
    public static function tableAlias()
    {
        return static::tableName();
    }
} 

class ActiveQuery {
    public function getAlias()
    {
        list(, $alias) = $this->getQueryTableName($this);
        return $alias;
    }
}

Abstraction of my issue #10909 may looks like:

$query = ... ; //Some ActiveQuery, may contain join and joinWith sections

$query->forTable(<unique id>, //<unique id> may be table name, or alias, or position pointer, like 'primary'
                function($query){
                    //all column names will be auto-prepended with <unique id> table name/alias
                    $query->select(['id', 'name', 'created'])
                            ->andWhere('id > 10')
                            ->addOrderBy('id');
});

and, may be,

class ActiveQuery {
    /**
     * @param string $tableUniqueId same as in prev example
     */
    public function getAlias($tableUniqueId){}
}

[mikk150]

why ActiveQuery::getQueryTableName is private? why ActiveQuery::getQueryTableName takes in query, when it is itself in query?

[mikk150]

Also I cannot find place to attach myself via events(or anything else) right before sql statement is being constructed for this query so I could alter the statement

[AnatolyRugalev]

DOES NOT WORK. SEE NEXT COMMENTS I have a magic solution for this:

<?php

namespace common\query;

use yii\db\Query;
use \yii\helpers\Json;
use \yii\helpers\ArrayHelper;

class ActiveQuery extends \yii\db\ActiveQuery
{
    public function prepare($builder)
    {
        $query = parent::prepare($builder);
        static::replaceAlias($query);
        return $query;
    }

    public static function replaceAlias(Query $query)
    {
        $alias = ArrayHelper::isAssociative($query->from) ? array_keys($query->from)[0] : $query->from[0];

        $replaceAliasRecursively = function ($value) use ($alias, &$replaceAliasRecursively) {
            if ($value instanceof \yii\db\Expression) {
                $value->expression = $replaceAliasRecursively($value->expression);
            } elseif (is_scalar($value)) {
                $value = str_replace('%%alias%%', $alias, $value);
            } elseif (is_array($value)) {
                $newValue = [];
                foreach ($value as $k => $v) {
                    $newKey = $replaceAliasRecursively($k);
                    $newValue[$newKey] = $replaceAliasRecursively($v);
                }
                $value = $newValue;
                unset($newValue);
            }

            return $value;
        };

        $attributes = ['where', 'orderBy'];
        foreach ($attributes as $attribute) {
            if (!empty($query->$attribute)) {
                $query->$attribute = $replaceAliasRecursively($query->$attribute);
            }
        }
    }

    public function aliasMiddleware($callback)
    {
        return function (ActiveQuery $query) use ($callback) {
            $callback($query);
            static::replaceAlias($query);
        };
    }

    public function joinWith($with, $eagerLoading = true, $joinType = 'LEFT JOIN')
    {
        $result = parent::joinWith($with, $eagerLoading, $joinType);

        foreach ($this->joinWith as $i => $config) {
            foreach ($config[0] as $j => $relation) {
                if (is_callable($relation)) {
                    $this->joinWith[$i][0][$j] = $this->aliasMiddleware($relation);
                }
            }
        }

        return $result;
    }
}

Inside models:

    public static function find()
    {
        $query = Yii::createObject(\common\query\ActiveQuery::className(), [get_called_class()]);
        return $query>andWhere(['%%alias%%.isDeleted' => false]);
    }

This hack wraps ActiveQuery relational callbacks with middleware and replaces all %%alias%% strings inside scopes.

Found it together with @sizeg

[mikk150]

@AnatolyRugalev it is not magic -> it doesn't work consider this

Book::find()->joinWith('author')->all()

produces

SELECT `book`.* FROM `book` LEFT JOIN `author` ON `book`.`author_id` = `author`.`id` WHERE (`book`.`deleted_at` IS NULL) AND (`book`.`deleted_at` IS NULL) LIMIT 20

what I actually wanted was

SELECT `book`.* FROM `book` LEFT JOIN `author` ON `book`.`author_id` = `author`.`id` WHERE (`book`.`deleted_at` IS NULL) AND (`author`.`deleted_at` IS NULL) LIMIT 20

[AnatolyRugalev]

@mikk150 sorry about that. Looks like we got regression bug after refactoring, will let you know when we fix it.

[mikk150]

@AnatolyRugalev have you changed it? because it seems to work now magically, have no idea why

[sizeg]

@mikk150 we test your case, and get the same result. We need a time for refactoring it.

[CedricYii]

Examples provided are putting emphasis on columns' aliases. My main concern is about using the same table in different relations with different aliases (or even on some occasions, reuse the same relation with another alias). Consider this example:

class Order {
...
  //first relation with table COMPANY
  public function getCustomer()
  {
      return $this->hasOne( Company::className(), ['id' => 'customerid'] ) //table COMPANY
                  ->andOnCondition( [ '==relationAlias==.type' => 'customer' ] );
  }

  //second relation with table COMPANY
  public function getSupplier()
  {
      return $this->hasOne( Company::className(), ['id' => 'supplierid'] ) //table COMPANY
                  ->andOnCondition( [ '==relationAlias==.type' => 'supplier' ] );
  }
...
}

Then we could write

$orders = Order::find()
                   ->alias( 'o' )
                   ->innerJoinWith('customer c')
                   ->innerJoinWith('supplier s');

To build query

select o.* from order o
join company c on c.id = o.customerid and c.type = 'customer'
join company s on s.id = o.supplierid and s.type = 'supplier'

This is supposed to work with pr #11326

[CedricYii]

Proposed syntax for dynamic alias addressing is simply @alias. With pr #11646, you can now insert placeholder @alias in an active query to address the current table name without knowing it in advance. If the table as no alias, it will use the table name.

Consider this complete example: ActiveRecord Order with two relations on the same table company

class Order {
...
  //first relation with table COMPANY
  public function getCustomer()
  {
      return $this->hasOne( Company::className(), ['id' => 'customerid'] ) //table COMPANY
                  ->andOnCondition( [ '@alias.type' => 'customer' ] );
  }

  //second relation with table COMPANY
  public function getSupplier()
  {
      return $this->hasOne( Company::className(), ['id' => 'supplierid'] ) //table COMPANY
                  ->andOnCondition( [ '@alias.type' => 'supplier' ] );
  }

  public static find()
  {
      return parent::find()->orderBy( '@alias.id' );
  }
...
}

ActiveRecord Company related to address (to illustrate reuse of the same relation with another alias).

class Company {
...
  public function getAddress()
  {
      return $this->hasOne( Address::className(), ['companyid' => 'id'] ) //table ADDRESS
                  //condition with one alias notation dynamicaly replaced by two different aliases
                  ->andOnCondition( [ '@alias.type' => 'head' ] ); 
  }
...
}

Then we could write

$orders = Order::find()
                   ->alias( 'o' )
                   ->innerJoinWith(['customer c' => function($q){
                      $q->joinWith(['address adc']);
                   }])
                   ->innerJoinWith(['supplier s' => function($q){
                      $q->joinWith(['address ads']);
                   }]);

To build query

select o.* from order o
join company c on c.id = o.customerid and c.type = 'customer'
join address adc on adc.companyid = c.id and adc.type = 'head'
join company s on s.id = o.supplierid and s.type = 'supplier'
join address ads on ads.companyid = c.id and ads.type = 'head'
order by o.id

It helps building complex queries without worrying about predefined alias. This should solve issues #7263 and #10883.

[petrabarus]

Haven't read update on ActiveQuery for a while. I assume @alias will be automatically generated right?

so adding @alias in return $this->andWhere(['@alias.is_removed' => 0]); should work right?

class Query extends \yii\db\ActiveQuery {
   public function isNotRemoved() {
      return $this->andWhere(['@alias.is_removed' => 0]);
   }
}

class ShelfQuery extends Query {
}

class BookQuery extends Query {
}

class Shelf extends \yii\db\ActiveRecord {
    public static find() {
       return \Yii::createObject(Shelf::class, [get_called_class()]);
    }
}

class Book extends \yii\db\ActiveRecord {
    public static find() {
       return \Yii::createObject(Book::class, [get_called_class()]);
    }
}

and this case will not result in error for ambiguous column name?

Chapter::find()
  ->joinWith([
      'book' => function(BookQuery $query) {
          $query->joinWith([
              'shelf' => function(ShelfQuery $query) {
                  $query->isNotRemoved();
              }
          ])->isNotRemoved();
      }
  ])->isNotRemoved();

[CedricYii]

That's right, it should work properly. Could you give it a try?

However I notice that in your example Shelf and Book are built on Shelf::class, so I assume they are using the same table name 'shelf' (or it probably is just a typo). Whatever, in this case, you would need to give an alias to shelf and/or to book in order to distinguish them. For example, it can be inserted:

• in the Book::find() method

class Book extends \yii\db\ActiveRecord {
    public static find() {
       $query = \Yii::createObject(Shelf::class, [get_called_class()]);
       return $query->alias('book'); //add alias here
    }
}

• or when you build the query

Chapter::find()
  ->joinWith([
      'book book' => function(BookQuery $query) { //add alias here
          $query->joinWith([
              'shelf' => function(ShelfQuery $query) {
                  $query->isNotRemoved();
              }
          ])->isNotRemoved();
      }
  ])->isNotRemoved();

[petrabarus]

However I notice that in your example Shelf and Book are built on Shelf::class, so I assume they are using the same table name 'shelf' (or it probably is just a typo).

Yes, it's typo. I just fixed it. Assuming the alias works, I think my case should be working.

Self joined class should also work with the code you put above.

Thanks!

[arthibald]

Just trying out your revisions @CedricYii, and they seem to work well for our project. Any news on when this will become part of the core?

Also, disambiguing should really be disambiguating, or the title works as just disambiguate.

[arthibald]

@CedricYii, having now used your code more intensively, I had to add both:

$this->select   = self::replaceAliasPlaceholder( $this->select, $alias );

and the commented

     if (!empty($this->join)) {
         foreach ($this->join as &$join) {
             $join = self::replaceAliasPlaceholder( $join, $alias );
         }
     }

to the private function normalizeAliasInQuery() to cover some edge cases.

I haven't needed a UNION yet, but I suspect this will be necessary too at some point.

Thanks again.

[CedricYii]

@arthibald Thank you for your feedback. I have no news on when it will be merged, however it is planned in 2.0.10, so wait and see... Other commits have been made already, you can find then here https://github.com/yiisoft/yii2/pull/11646/commits (sorry I didn't reference the issue in all of them). You might want to check the comments also in the pull request #11646 I may need to start a new issue dedicated to this new dynamic alias feature to make it clearer for everybody.