Yii2 hasOne relation link to multiple table

问题:

I have 4 tables are agent, vendor, operator and ticket. A ticket is belong to only one of agent, vendor or operator. I designed ticket table have two fields: org_type and org_id In Ticket model class, I want to build 3 function getAgent, getVendor, getOperator use hasOne relation of Yii2

Solution 1:

public function getAgent()
{
    if ($this->org != Organization::ORGANIZATION_TYPE__AGENT)
        return null;
    return $this->hasOne(Agent::class, ['id' => 'org_id']);
}

will be failed because I can't use $query->joinWith('agent');

Solution 2:

public function getAgent()
{
    return $this->hasOne(Agent::class, ['id' => 'org_id'])
        ->andWhere(['org' => Organization::ORGANIZATION_TYPE__AGENT]);
}

also failed because hasOne relation will make this query: select * from agent where id = 3 and org = 'agent' but org is field of ticket, not agent.

I want to use this hasOne relation in query joinWith to I can filter and sort in GridView

Can anyone give me a solution for this?

回答1:

One solution is to have another relation to filter organization types and use that as pivot to grab records from agent table.

public function getOrganizationForAgent(){
    return $this->hasOne(static::class, ['id' => 'id'])
        ->andOnCondition(['org' => Organization::ORGANIZATION_TYPE__AGENT]);
}

public function getAgent(){
    return $this->hasOne(Agent::class, ['id' => 'org_id'])
        ->via('organizationForAgent')
}

This way you get fully a functioning relation (lazy loading, joins, etc) at the cost of bit of query overhead
The alternative is to alter your database