Yii2 Relations to get a field using two foreign keys

I have issues to get a field from a table using two foreign keys.

Explained the relations of the use tables below.

User table hasMany Companies(company_id).
Comapny table has company_id.
Facilities hasOne Company_id(Based on the user instance).
Area hasMany Facilites(facility_id).

The logged on user will have areas related to facilities_id which in turn is related to company_id.

I now have a

Productlines hasMany Products(product_id).

The user should display products related to the areas.

Productlines hasMany Areas(area_id).
Product lines has a field called internal_code.

Product model has relation with productlines on the basis of products_id.

I want to display this internal code on the basis of product_id which belongs to the specific areas.

My code as of now which is not working :

In my product Model.

    public function getFacility()
{
    return $this->hasMany(Facility::className(),['facility_id' => 'facility_id'])->viaTable('sim_users',['company_id'=> Yii::$app->user->identity->company_id]);
}

 public function getArea()
{

    return $this->hasMany(Area::className(),['area_id'=>'area_id'])->viaTable('sim_facility',['facility_id'=> 'facility_id' ]);
}

public function getProductlines()
{

    return $this->hasMany(Productlines::className(), ['product_id' => 'product_id'])->viaTable('sim_productlines',['product_id' => 'product_id']);
}

In my view file:

  [
        'label' => 'Internal Code',
        'format' => 'raw',

        'value' => function ($data) {
            foreach ($data->productlines as $intCode)
           return $intCode->internal_code;
        }

I don't understand how to link these relations. Looking for help. Thanks

List of Tables:

Users (primaryKey - users_id, foreign_key- comapny_id)
Company(primaryKey - company_id)
Facility(primaryKey - facility_id, foreignKey- company_id)
Areas(primaryKey - area_id, foreignKey- facility_id)
Productlines(primaryKey - productlines_id, foreignKey - product_id and area_id)
Product (primaryKey - product_id).

My expected result:

In the above table you can see area_id 47 has two product_id's (1 and 3). and the internal code for them is different. I want them to return both the internal codes as of now I can get only one internal code.

Solution

So you have following hierarchy

Company hasMany Users
Company hasMany Facilities
Facility hasMany Areas
Area hasMany ProductLines
Product hasMany ProductLines

Products are linked to Areas as manyToMany via productLines relation

You can use following code for your AR classes (but You should fix the names, because I used a singular form for classes and table names):

public class User extends ActiveRecord
{
    public function getCompany()
    {  
        retrun $this->hasOne(Company::className(), ['company_id' => 'company_id']);
    }
}

public class Company extends ActiveRecord
{
    public function getUsers()
    {  
        retrun $this->hasMany(User::className(), ['company_id' => 'company_id']);
    }    

    public function getFacilities()
    {  
        retrun $this->hasMany(Facility::className(), ['company_id' => 'company_id']);
    }    
}

public class Facility extends ActiveRecord
{
    public function getCompany()
    {  
        retrun $this->hasOne(Company::className(), ['company_id' => 'company_id']);
    }

    public function getAreas()
    {  
        retrun $this->hasMany(Area::className(), ['facility_id' => 'facility_id']);
    }    
}

public class Area extends ActiveRecord
{
    public function getFacility()
    {  
        retrun $this->hasOne(Facility::className(), ['facility_id' => 'facility_id']);
    }

    public function getProductLines()
    {  
        retrun $this->hasMany(ProductLine::className(), ['area_id' => 'area_id']);
    }    

    public function getProducts()
    {  
        retrun $this->hasMany(Product::className(), ['product_id' => 'product_id'])
            ->via('productLines');
    }    
}

/**
 * @property $internal_code
 */
public class ProductLine extends ActiveRecord
{
    public function getArea()
    {  
        retrun $this->hasOne(Area::className(), ['area_id' => 'area_id']);
    }

    public function getProducts()
    {  
        retrun $this->hasMany(Product::className(), ['product_id' => 'product_id']);
    }
}

public class Product extends ActiveRecord
{
    public function getProductLines()
    {  
        retrun $this->hasMany(ProductLine::className(), ['product_id' => 'product_id']);
    }       

    public function getAreas()
    {  
        retrun $this->hasMany(Area::className(), ['area_id' => 'area_id'])
            ->via('productLines');
    }    

    public function getFacilites()
    {  
        retrun $this->hasMany(Facility::className(), ['facility_id' => 'facility_id'])
            ->via('areas');
    }
}

to get all internal codes for given product:

$productLines = $product->productLines;

$internalCodesAsArray = array_map(function(ProductLine $productLine) {
    return $productLine->internal_code;
}, $productLines);

$internalCodesAsString = join(', ', $internalCodesAsArray);

to get all internal codes for given area:

$productLines = $product->productLines;

$internalCodesAsArray = array_map(function(ProductLine $productLine) {
    return $productLine->internal_code;
}, $productLines);

$internalCodesAsString = join(', ', $internalCodesAsArray);

The same if you want to list products for given area -- use $area->products relation. If you want to get products count for given area, then call $area->getProducts()->count();

To get internal codes of product for specific area add following function into Product class:

public function getInternalCodes(Area $area = null)
{
    $query = $this->getProductLines();
    if (!empty($area)) {
        $query->where(['area_id' => $area->area_id]);
    }
    return array_map(function (ProductLine $productLine) {
        return $productLine->internal_code;
    }, $query->all());
}

And use it in your controller as $currentProduct->getInternalCodes($currentArea)

Do not add custom filters into functions that are define relations. If you want to get list of Facilities for given Product and filter them by company of current user then better implement it as additional function:

public class Product 
{
    ...

    public function getFacilitiesByCompany(Company $company)
    {
        return $product->getFacilities()
            ->where(['company_id' => $company->company_id]);
    }
}

and use it as follows

$product->getFacilitiesByCompany(Yii::$app->user->identity->company)->all();

Hope this helps.