Open petrabarus opened 9 years ago
mungkin sama dengan ini #2377
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.
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.
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.
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;
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.
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
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.
@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()]);
}
}
@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.
@cebe it would be real nice, because then we can finally do soft-delete easily
@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]);
}
}
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.
}
}
@petrabarus that {{}} thing, I don't think it could be a good idea
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){}
}
why ActiveQuery::getQueryTableName is private? why ActiveQuery::getQueryTableName takes in query, when it is itself in query?
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
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
@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
@mikk150 sorry about that. Looks like we got regression bug after refactoring, will let you know when we fix it.
@AnatolyRugalev have you changed it? because it seems to work now magically, have no idea why
@mikk150 we test your case, and get the same result. We need a time for refactoring it.
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
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.
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();
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:
class Book extends \yii\db\ActiveRecord {
public static find() {
$query = \Yii::createObject(Shelf::class, [get_called_class()]);
return $query->alias('book'); //add alias here
}
}
Chapter::find()
->joinWith([
'book book' => function(BookQuery $query) { //add alias here
$query->joinWith([
'shelf' => function(ShelfQuery $query) {
$query->isNotRemoved();
}
])->isNotRemoved();
}
])->isNotRemoved();
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!
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.
@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.
@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.
As for UNION I haven't found a practical usage (UNION is usually between two unrelated query and there is no main scope for an @alias
), so I dropped it for now.
Let me know if you find an example where it would be useful.
@CedricYii Thanks, looks like we arrived at the same code in the end!
How is it going?
Could we just expose 2 methods like this:
class Query extends Component implements QueryInterface
{
...
public function getTableAlias()
{
list(, $alias) = $this->getTableNameAndAlias();
return $alias;
}
public function getTableName()
{
list($name,) = $this->getTableNameAndAlias();
return $name;
}
...
}
It starts getting really irritating.. and this eases the pain of figuring out the freaking alias
@mikk150 a query may involve more than one table and only in that case aliases are relevant, so adding these methods does not really help anything, it is not clear which table's alias you are asking for.
Well, I am trying to get alias inside a models Query class, there I know what tables alias I am asking for... these methods could be protected to achieve it(but then when they actually matter again, they do not work again), but right now you just have to remember to use same aliases(which BTW does not work always)
Is that related to #14049?
Kind of is...
Am I missing something, but why getFromAliases() return an array, if it is inside ActiveQuery(as activequery should only have one "from" declaration)
@mikk150 FROM
can contain multiple tables in SQL. e.g.:
SELECT name FROM user, profile WHERE user.profile_id = profile.id;
I know, but AR doesn't do that
1 The alias must previous to all assoc columns if Alias is set; 2 In the joinWith the alias must be set too (default is a key of array)
class Duration extends \yii\db\ActiveRecord {
...
public function getUnit()
{
return $this->hasOne(DurationUnit::className(), ['id' => 'unitId'])->inverseOf('durations');
}
...
}
echo Duration::find()
->joinWith([
'unit as unitAlias' => function (DurationUnitQuery $query) {
$query->andWhere(['id' => [5, 3, 2, 1]]);
}
])
->andWhere(['id' => [4, 5, 9]])
->alias('durationAlias')
->createCommand()->getRawSql();
must produce
SELECT `durationAlias`.* FROM `duration` `durationAlias` LEFT JOIN `duration_unit` `unitAlias` ON `durationAlias`.`unitId` = `unitAlias`.`id` WHERE (`durationAlias`.`id` IN (4, 5, 9)) AND (`unitAlias`.`id` IN (5, 3, 2, 1))
instead of
SELECT `durationAlias`.* FROM `duration` `durationAlias` LEFT JOIN `duration_unit` `unitAlias` ON `durationAlias`.`unitId` = `unitAlias`.`id` WHERE (`id` IN (4, 5, 9)) AND (`id` IN (5, 3, 2, 1))
Now we process the validator condition throw applyAlias
functional.
Where condition must be processed with this function:
pro: 2 years old issue
contr: ActiveQuery with multiply from (and has one $this->modelClass)
For some case can be use #14170 (alias or table_name in joinWith)
For 1 The alias must previous to all assoc columns if Alias is set;
from
is have < 2 tables
https://github.com/yiisoft/yii2/blob/master/framework/db/ActiveQuery.php#L775-L7912 In the joinWith the alias must be set too (default is a key of array)
What if we change GII, to generate aliases as well? this will ease the pain for sure
Gii generated "starter" functional. I use this method now, https://github.com/bscheshirwork/yii2-cubs/blob/5e84bd686f183052e62a840751800f9e5caf19a9/src/generators/model/Generator.php#L247 but it's not ideal. It's even harmful. So many case avoid it.
I think the attribute name must still clear in config. After #14163 I will be reverted it to base code
Is there any movement on this functionality being added to the core framework? Currently we are having to update our stand in ActiveQuery class every time the framework is updated.
Yes and no. There's a pull request by @CedricYii but we haven't reviewed it in depth. It won't get into 2.0 for sure. 2.1 - likely.
Ah, ok.. it's just it's been 3 years since this issue was raised, and as far as I can tell this is an integral to the flexibility of the framework; Yii 1.1 has this functionality.
Yes. And it was removed on purpose. That's why getting it back isn't a simple question.
@arthibald Yii1.0 did not have this feature, so we are back where we started at least :)
We definitely need this feature
https://github.com/CedricYii/yii2/commit/1e25d3e8a1fde5b850f23fc2732dbd07772989d0
in the Yii2 and next generation versions too!
I have several tables that have columns with same name. Let's say
shelf
,book
,chapter
, have the same fieldname
.I have a page that will show list of
chapter
that will also show the name of thebook
as well as theshelf
.Naturally this is the query I used for the
ActiveDataProvider
But I only wanted to show the name in the
GridView
, so this is what I do to avoid theSELECT *
.On my Yii2 (c21895d4dd4c18d5bab37e0b7b359668f8aa8b67) this will output error
Column 'name' in field list is ambiguous
. So I have to put something like thisDo 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 theActiveQuery
and do something like this.