How to implement join query in Yii2

Question

I've read all the Yii2 Framework documentation but it's confusing when trying to implement it.

I have a view of Customer where it shows all the fields in customer table, including the address_id field in the address table.

I want to implement a join query using MySQL in the Yii2 Framework but the generated code is the following:

CustomerSearch in the models:

class CustomerSearch extends Customer{
/**
 * {@inheritdoc}
 */
public function rules()
{
    return [
        [['customer_id', 'store_id', 'address_id', 'active'], 'integer'],
        [['first_name', 'last_name', 'email', 'create_date', 'last_update'], 'safe'],
    ];
}

/**
 * {@inheritdoc}
 */
public function scenarios()
{
    // bypass scenarios() implementation in the parent class
    return Model::scenarios();
}

/**
 * Creates data provider instance with search query applied
 *
 * @param array $params
 *
 * @return ActiveDataProvider
 */
public function search($params)
{
    $query = Customer::find();

    // add conditions that should always apply here

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

    $this->load($params);

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

    // grid filtering conditions
    $query->andFilterWhere([
        'customer_id' => $this->customer_id,
        'store_id' => $this->store_id,
        'address_id' => $this->address_id,
        'active' => $this->active,
        'create_date' => $this->create_date,
        'last_update' => $this->last_update,
    ]);

    $query->andFilterWhere(['like', 'first_name', $this->first_name])
        ->andFilterWhere(['like', 'last_name', $this->last_name])
        ->andFilterWhere(['like', 'email', $this->email]);

    return $dataProvider;
}

Customer class:

class Customer extends \yii\db\ActiveRecord{
/**
 * {@inheritdoc}
 */
public static function tableName()
{
    return 'customer';
}

/**
 * {@inheritdoc}
 */
public function rules()
{
    return [
        [['store_id', 'first_name', 'last_name', 'address_id'], 'required'],
        [['store_id', 'address_id', 'active'], 'integer'],
        [['create_date', 'last_update'], 'safe'],
        [['first_name', 'last_name'], 'string', 'max' => 45],
        [['email'], 'string', 'max' => 50],
    ];
}

/**
 * {@inheritdoc}
 */
public function attributeLabels()
{
    return [
        'customer_id' => 'Customer ID',
        'store_id' => 'Store ID',
        'first_name' => 'First Name',
        'last_name' => 'Last Name',
        'email' => 'Email',
        'address_id' => 'Address ID',
        'active' => 'Active',
        'create_date' => 'Create Date',
        'last_update' => 'Last Update',
    ];
}
}

Answer 1

You need to declare some relations in your ActiveRecord models...

See here in the official docs for Working with Relational Data

If you are storing the address_id in your customer table then you will be tied to each customer having 1 single address (i.e. a one-to-one relationship), which is a rather bad design. Or you could use a junction table. You are better off storing the customer_id in each address record and defining a one-to-many relation, enabling each customer to store multiple addresses (more like in real life, i.e. for home, work address etc).

For example, in your Customer model, you would define a has-many relation for customer addresses:

use app\models\Address;

class Customer extends \yii\db\ActiveRecord
{

    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return 'customer';
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['store_id', 'first_name', 'last_name', 'primary_address_id'], 'required'],
            [['store_id', 'primary_address_id', 'active'], 'integer'],
            [['create_date', 'last_update'], 'safe'],
            [['first_name', 'last_name'], 'string', 'max' => 45],
            [['email'], 'string', 'max' => 50],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'customer_id' => 'Customer ID',
            'store_id' => 'Store ID',
            'first_name' => 'First Name',
            'last_name' => 'Last Name',
            'email' => 'Email',
            'primary_address_id' => 'Primary Address ID',
            'active' => 'Active',
            'create_date' => 'Create Date',
            'last_update' => 'Last Update',
        ];
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getAddresses()
    {
        return $this->hasMany(Address::className(), ['customer_id' => 'id']);
    }

}

And in your Address model you would have the inverse has-one relation defined:

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getCustomer()
    {
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }

You can then Access relational data from model instances via defined relation names, for example:

// SELECT * FROM `customer` WHERE `id` = 123
$customer = Customer::findOne(123);

// SELECT * FROM `address` WHERE `customer_id` = 123
// $addresses is an array of Address objects
$addresses = $customer->addresses;

Also note, if you define proper primary/foreign keys in your schema, the Gii Model/CRUD generators will automatically create the boilerplate relations code in your models and CRUD files.