Yii2: Table alias for joinWith needed

[gpoehl]

For tables with two (or more) foreign keys referencing the same related table you need a way to define an alias. The joinWith()function doesn't have this ability.
So there is no way to handle ambiugate column names for the related table.

[gpoehl]

Using an alias would also eleminate the need of using the table name.

Suggestion:

$orders = Order::find()->joinWith('customer as cu')->orderBy('cu.id, cu.id')->all();

instead of

$orders = Order::find()->joinWith('customer')->orderBy('tbl_customer.id, tbl_order.id')->all();

[o-rey]

Currently you can do it using ->from('customer cu')

[gpoehl]

Yeap.
But this doesn't helps when you want to re-use defined relations.

[qiangxue]

Use this:

joinWith([
    'customer' => function ($q) {
        $q->from('customer cu');
    },
])

[qiangxue]

We may consider adding the suggested shortcut.

[o-rey]

@qiangxue It would be great!

[Ragazzo]

We may consider adding the suggested shortcut.

this can confuse users, since relation name is not always table name, so example with $query is just the case.

[gpoehl]

That's an advantage as you don't even need to know the table name (or might change it without effecting these joins). You only need the relation name what should almost be the same as the model class name.

[klevron]

Any news about this one ?

In Yii1 it was quite easy to use relation name as table alias.

[cebe]

it is set for the RC milestone, so we will handle it before RC release.

[klevron]

Ok, thanks

[cebe]

Milestone set for 2.0.1, can be in 2.0 if someone is making a pull request.

[lynicidn]

https://github.com/yiisoft/yii2/issues/4143

[mdmunir]

Added property $alias to ActiveQuery is look OK for me.

MyAr::find()
    ->alias('t')
    ->joinWith([
        'customer' => function($q){
            $q->alias('c');
        }
    ]);

Or better syntax (if can) :D

joinWith(['c' => 'customer'])

    private function getQueryTableName($query)
    {
        ...

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

        return [$tableName, $alias];
    } 

[RomeroMsk]

Voting for both suggestions from @MDMunir :+1:

For now I'm using this _workaround_ for such situations:

class Order extends ActiveRecord
{
    ...
    public function getCustomer()
    {
        return $this->hasOne(Customer::className(), ['id' => 'customer_id'])
            ->from(['cu' => Customer::tableName()]);
    }
    ...
}

But this won't help in @twinni's situation.

[cebe]

related to #4972 and #5137

[alex-code]

Not sure if it's in a suitable place but I've updated getRelation in BaseActiveRecord to allow for alias names.

e.g.
Model::find()->joinWith('relation AS alias')
or
Model::find()->joinWith('relation(alias)')

The latter example with brackets is to try and keep it clean if you have multiple relations.
e.g.
Model::find()->joinWith('relation1.relation2(alias)')

https://github.com/Alex-Code/yii2/blob/relation-alias/framework/db/BaseActiveRecord.php#L1097

[callmez]

this problem is about how to use the relation better, hope it will become true soon. Thanks for work.

[cebe]

Will look into this after 2.0.1 is released. Have no time to do it this week.

[Gcaufy]

is this implemented by yii2 now?

[cebe]

no, as you see in this issue, the milestone is set for 2.0.3, I am going to work on that for this milestone and if all goes well it should be released with it.

[Gcaufy]

currently I'm doing it like this, let me know when it would be released

public static function with($r, $t = null) {
        $relations = array();
        if (is_array($r)) {
            foreach ($r as $v => $tn) {
                $i = strripos($v, '.');
                $alias = $v;
                if ($i != false)
                    $alias = substr($alias, $i + 1);
                $relations[$v] = function ($q) use ($tn, $alias) { $q->from($tn . ' ' . $alias); };
            }
        } else {
            $alias = $v;
            if ($i != false)
                $alias = substr($alias, $i + 1);
            $relations[$r] = function ($q) { $q->from($t . ' ' . $alias); };
        }
        return static::find()->from(static::tableName() . ' t')->joinWith($relations);
    }
    public static function find() {
        return parent::find()->from(static::tableName() . ' t');
    }

[callmez]

😢

[cebe]

@callmez I am sorry but my time is limited. I'd love to work on this but it turns out there is more to be done in the background than it looks like on first sight.

[callmez]

it's ok, still thanks for you work.

[rahulsinh]

@cebe SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'title' in where clause is ambiguous
can you help me:
if (!$this->validate()) {
// uncomment the following line if you do not want to any records when validation fails
// $query->where('0=1');
return $dataProvider;
}

    $query ->joinWith('location');
    $query ->joinWith('programCategory');
    $query ->joinWith('duration');
    $query ->joinWith('airport');
    $query->andFilterWhere([
        'program_id' => $this->program_id,

    ]);

    $query->andFilterWhere(['like', 'title', $this->title])
      ->andFilterWhere(['like', 'location.city', $this->location_id])
        ->andFilterWhere(['like', 'program_category.program_category_name', $this->category_id])
        ->andFilterWhere(['like', 'duration.type', $this->duration_id])
        ->andFilterWhere(['like', 'airport.city', $this->airport_id]);



    return $dataProvider;
}

}

[cebe]

@rahulsinh this is not related, please use the forum to ask questions.

[MaXL-ru]

As I understand in Yii 2.0.3, I must write next code:

$query = Billing::find()
  ->joinWith([
    'state'
  ]);

$query->andFilterWhere([
  Billing::tableName()      . '.id' => $this->id,
  BillingState::tableName() . '.id' => $this->stateId
]);

if both tables Billing and BillingState have column id ?

[mklemarczyk]

:+1: @MaXL-ru It is always the problem when you extend SearchModel.
I often use a table prefix and I need to use tableName function many times.

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {
        $query = Employee::find()
            ->innerJoinWith('user', false)
            ->innerJoinWith('company', false);

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

        $this->load($params);

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

        $query->andFilterWhere([
            'id' => $this->id,
            [...]
            'company_id' => $this->company_id,
            'user_id' => $this->user_id,
        ]);
        // Here I must use tableName function
        $query->andFilterWhere(['like', User::tableName() . .firstname', $this->user_firstname]);

        return $dataProvider;
    }

But when I use

joinWith([
    'user' => function ($q) {
        $q->from('{{%user}} user');
    },
])

My order in GridView does not work (for firstname column).
You need to standardize using relation name in Query, because sometimes we can use name of relation and sometimes we need to use full table name. It is very confusing.

[lynicidn]

@mklemarczyk: you should configure sort property of dataProvider.
see ActiveDataProvider::setSort()

[mklemarczyk]

Thank you @lynicidn, I miss it. But now I also need to reorganize all methods that use joinWith in my EmployeeQuery class.

I think that, instead of modifying joinWith method we should resolve relation names in other methods?
Using aliases can join may times the same table in our Query.

But can you handle this?

$query = Experiment::find()
->joinWith(['ownerEmployee.user' => function($q){$q->from(['ownerUser' => '{{%user}}']);}])
->joinWith(['executeEmployee.user' => function($q){$q->from(['executeUser' => '{{%user}}']);}]);

Tables definition:
user table (from advanced template)
employee table (id, lab_id, superior_id, user_id)
experiment table (id, owner_employee_id, execute_employee_id, done_date)

And Yii give an exception:

Not unique table/alias: 'tex03_employee'

So to solve this problem I will need to create join manually. :disappointed:

[igorbelikov]

+

[MaXL-ru]

Not in 2.0.4 :-1:

[petrabarus]

Hope this will be supported in 2.0.5

[dynasource]

am I correct when assuming that the use of the same junction table is not supported yet?
Ie.:

$query = Article::find();
$query->joinWith('category.article');

In which:

• article joins category by article_x_article_category
• category joins article by article_x_article_category

Example SQL:

SELECT `article`.* 
FROM `article` 
LEFT JOIN `article_x_article_category` ON `article`.`id` = `article_x_article_category`.`owner_id`
LEFT JOIN `article_category` `category` ON `article_x_article_category`.`category_id` = `category`.`id`
LEFT JOIN `article_x_article_category` ON `category`.`id` = `article_x_article_category`.`owner_id`
LEFT JOIN `article` ON `article_x_article_category`.`category_id` = `article`.`id` 
WHERE `article`.`id`=10948

Gives:

• Not unique table/alias: 'article_x_article_category'

Currently, to bypass this issue, I am not using the joinWiths but instead I am using Query::innerJoin.

[Koudy]

+

[avinashz]

+1 :+1:

[a-kolomensky]

+

[disem]

+

[StagnantIce]

+

[Yiivgeny]

+1

[neotrouz]

+1

[JavierCabezas]

I would love this functionality!

[pun4drunk]

+1

[keeprock]

Me too, but, guys, instead of waiting - take a look of other frameworks implementations on this, because you can wait for a loooong time.

[SilverFire]

It is already in work: https://github.com/yiisoft/yii2/issues/10253

[cebe]

PR is merged.