Handling Relationships in Yii2 Active Record

In Yii2, Active Record (AR) allows defining relationships between tables using model classes. These relationships help in fetching related data easily without writing complex SQL queries.

1. Types of Relationships in Yii2

Yii2 supports the following relationships:

1. One-to-One (hasOne())
2. One-to-Many (hasMany())
3. Many-to-Many (Using an intermediate table)

2. One-to-One Relationship

A one-to-one relationship means each record in one table corresponds to exactly one record in another table.

Example Scenario

• User table (User details)
• UserProfile table (User's additional profile details, linked by user_id)

Database Schema

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);
CREATE TABLE user_profile (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

Yii2 Model Relationship (User.php)

class User extends \yii\db\ActiveRecord
{
    public function getProfile()
    {
        return $this->hasOne(UserProfile::class, ['user_id' => 'id']);
    }
}

Yii2 Model Relationship (UserProfile.php)

class UserProfile extends \yii\db\ActiveRecord
{
    public function getUser()
    {
        return $this->hasOne(User::class, ['id' => 'user_id']);
    }
}

Fetching Data (Controller or Anywhere)

$user = User::findOne(1);
echo $user->profile->bio; // Accessing profile data

3. One-to-Many Relationship

A one-to-many relationship means a record in one table is related to multiple records in another table.

Example Scenario

• Category table (Product categories)
• Product table (Each product belongs to one category, linked by category_id)

Database Schema

CREATE TABLE category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);
CREATE TABLE product (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE CASCADE
);

Yii2 Model Relationship (Category.php)

class Category extends \yii\db\ActiveRecord
{
    public function getProducts()
    {
        return $this->hasMany(Product::class, ['category_id' => 'id']);
    }
}

Yii2 Model Relationship (Product.php)

class Product extends \yii\db\ActiveRecord
{
    public function getCategory()
    {
        return $this->hasOne(Category::class, ['id' => 'category_id']);
    }
}

Fetching Data (Controller or Anywhere)

$category = Category::findOne(1);
foreach ($category->products as $product) {
    echo $product->name . "<br>";
}

4. Many-to-Many Relationship

A many-to-many relationship requires an intermediate table to link two related tables.

Example Scenario

• Student table
• Course table
• student_course (Intermediate table storing student-course associations)

Database Schema

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);
CREATE TABLE course (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL
);
CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE CASCADE
);

Yii2 Model Relationship (Student.php)

class Student extends \yii\db\ActiveRecord
{
    public function getCourses()
    {
        return $this->hasMany(Course::class, ['id' => 'course_id'])
            ->viaTable('student_course', ['student_id' => 'id']);
    }
}

Yii2 Model Relationship (Course.php)

class Course extends \yii\db\ActiveRecord
{
    public function getStudents()
    {
        return $this->hasMany(Student::class, ['id' => 'student_id'])
            ->viaTable('student_course', ['course_id' => 'id']);
    }
}

Fetching Data (Controller or Anywhere)

$student = Student::findOne(1);
foreach ($student->courses as $course) {
    echo $course->title . "<br>";
}

5. Advanced Relationship Features

5.1. Filtering Related Data (with Conditions)

You can filter related records dynamically.

$category = Category::findOne(1);
$activeProducts = $category->getProducts()->where(['status' => 1])->all();

5.2. Eager Loading vs Lazy Loading

• Lazy Loading (Default): Related data is fetched only when accessed.

$user = User::findOne(1);
echo $user->profile->bio; // Queries executed only when accessed

• Eager Loading: Fetches related data in one query using with().

$users = User::find()->with('profile')->all();

5.3. Counting Related Records

$category = Category::findOne(1);
$productCount = $category->getProducts()->count();

5.4. Saving Related Data

$student = new Student();
$student->name = "Rahul";
$student->save();
$course = Course::findOne(2);
Yii::$app->db->createCommand()->insert('student_course', [
    'student_id' => $student->id,
    'course_id' => $course->id
])->execute();

6. More Relationship Types in Yii2

6.1. Self-Referencing (Hierarchical) Relationship

A self-referencing relationship is used when a table references itself, such as categories with subcategories or employees with managers.

Example: Category with Parent and Child Categories

Database Schema

CREATE TABLE category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES category(id) ON DELETE SET NULL
);

Yii2 Model Relationship (Category.php)

class Category extends \yii\db\ActiveRecord
{
    // Parent category (One-to-One)
    public function getParent()
    {
        return $this->hasOne(Category::class, ['id' => 'parent_id']);
    }
    // Child categories (One-to-Many)
    public function getChildren()
    {
        return $this->hasMany(Category::class, ['parent_id' => 'id']);
    }
}

Fetching Data

$category = Category::findOne(1);
echo "Parent: " . $category->parent->name;
foreach ($category->children as $child) {
    echo "Child: " . $child->name . "<br>";
}

6.2. Polymorphic Relationship

Yii2 does not have built-in polymorphic relations like Laravel, but we can implement them manually.

Example: Comments on Different Models (Post, Product, etc.)

Database Schema

CREATE TABLE comment (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entity_type VARCHAR(50) NOT NULL,  -- Stores the model name (Post, Product, etc.)
    entity_id INT NOT NULL,  -- Stores the related model's ID
    content TEXT NOT NULL
);

Yii2 Model Relationship (Comment.php)

class Comment extends \yii\db\ActiveRecord
{
    public function getEntity()
    {
        return $this->hasOne(self::getEntityClass(), ['id' => 'entity_id']);
    }
    private static function getEntityClass()
    {
        $entityType = static::findOne(['id' => $this->id])->entity_type;
        return $entityType === 'Post' ? Post::class : Product::class;
    }
}

Fetching Data

$comment = Comment::findOne(1);
echo $comment->entity->title;  // Works for both Post and Product

6.3. Composite Primary Key Relationship

If a table has a composite primary key, you can define relationships using both keys.

Example: Order Items with (order_id, product_id) as Composite Key

Database Schema

CREATE TABLE order_item (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES product(id)
);

Yii2 Model Relationship (Order.php)

class Order extends \yii\db\ActiveRecord
{
    public function getOrderItems()
    {
        return $this->hasMany(OrderItem::class, ['order_id' => 'id']);
    }
}

Yii2 Model Relationship (OrderItem.php)

class OrderItem extends \yii\db\ActiveRecord
{
    public function getProduct()
    {
        return $this->hasOne(Product::class, ['id' => 'product_id']);
    }
    public function getOrder()
    {
        return $this->hasOne(Order::class, ['id' => 'order_id']);
    }
}

Fetching Data

$order = Order::findOne(1);
foreach ($order->orderItems as $item) {
    echo $item->product->name . " - Quantity: " . $item->quantity . "<br>";
}

7. Summary of All Yii2 Relationships

Yii2 provides a flexible and powerful way to manage relationships in Active Record. The ability to define relationships using hasOne(), hasMany(), and viaTable() makes querying related data seamless.