Enhanced Model Relationship System with Explicit Type Definitions

[armanist]

📋 Summary
The current joinTo and joinThrough implementation has inconsistent logic and unclear naming that doesn't align with industry standards. This proposal introduces explicit relationship type definitions in model relations and clarifies the purpose of each join method.

🐛 Current Issues

1. Inverted Relationship Logic

The current implementation has backwards logic for relationship detection:

Current joinTo behavior:

• Looks for foreign key configuration in the related model's relations
• Should look in the current model's relations (like Laravel's hasOne/hasMany)

Current joinThrough behavior:

• Looks for foreign key configuration in the current model's relations
• Works correctly by accident for belongsTo relationships
• Should be reserved exclusively for many-to-many pivot table relationships

✨ Proposed Solution

1. Add Relationship Type Constants
   Create a Relation class with relationship type constants:
   namespace Quantum\Libraries\Database\Constants;

class Relation {
    const HAS_ONE = 'hasOne';
    const HAS_MANY = 'hasMany';
    const BELONGS_TO = 'belongsTo';
    const BELONGS_TO_MANY = 'belongsToMany';
}

2. Enhanced Model Relations Definition
Models explicitly declare relationship types:

use Modules\Web\Models;

// Post Model
class Post extends QtModel {
    public function relations(): array {
        return [
            // Post belongs to User
            User::class => [
                'type' => Relation::BELONGS_TO,
                'foreign_key' => 'user_uuid',  // in posts table
                'local_key' => 'uuid',          // in users table
            ],
            
            // Post has many Comments
            Comment::class => [
                'type' => Relation::HAS_MANY,
                'foreign_key' => 'post_uuid',   // in comments table
                'local_key' => 'uuid',          // in posts table
            ],
            
            // Post belongs to many Tags (via pivot)
            Tag::class => [
                'type' => Relation::BELONGS_TO_MANY,
                'pivot_table' => 'post_tags',
                'foreign_pivot_key' => 'post_id',
                'related_pivot_key' => 'tag_id',
                'foreign_key' => 'id',
                'local_key' => 'id',
            ]
        ];
    }
}

// User Model
class User extends QtModel {
    public function relations(): array {
        return [
            // User has many Posts
            Post::class => [
                'type' => Relation::HAS_MANY,
                'foreign_key' => 'user_uuid',  // in posts table
                'local_key' => 'uuid',          // in users table
            ],
            
            // User has one Profile
            Profile::class => [
                'type' => Relation::HAS_ONE,
                'foreign_key' => 'user_uuid',  // in profiles table
                'local_key' => 'uuid',          // in users table
            ]
        ];
    }
}

3. Enhanced Join Methods
joinTo(QtModel $model, bool $switch = true)

Auto-detects relationship type from model relations configuration
Handles HAS_ONE, HAS_MANY, and BELONGS_TO relationships
Applies appropriate join logic based on detected type
Default to HAS_MANY if type not specified (backward compatibility)

joinThrough(QtModel $model, bool $switch = true)

Reserved exclusively for BELONGS_TO_MANY (many-to-many) relationships
Works with pivot tables
Clear separation of concerns

4. Usage Examples

// Get posts with their authors (Post belongsTo User)
$posts = $postModel
    ->joinTo(ModelFactory::get(User::class))  // Auto-detects BELONGS_TO
    ->select('posts.title', 'users.firstname')
    ->get();

// Get user with all their posts (User hasMany Posts)
$userPosts = $userModel
    ->criteria('uuid', '=', $userUuid)
    ->joinTo(ModelFactory::get(Post::class))  // Auto-detects HAS_MANY
    ->get();

// Get post with tags (many-to-many via pivot)
$postWithTags = $postModel
    ->criteria('uuid', '=', $postUuid)
    ->joinThrough(ModelFactory::get(Tag::class))  // Uses pivot table
    ->get();

// Chained relationships
$posts = $postModel
    ->joinTo(ModelFactory::get(User::class), true)    // Switch context Post → User
    ->joinTo(ModelFactory::get(Profile::class))       // User → Profile
    ->select('posts.title', 'users.firstname', 'profiles.bio')
    ->get();