การใช้งาน Eloquent Relationships - เพื่อดึงข้อมูลใน Laravel อย่างมีประสิทธิภาพ

php artisan make:migration create_countries_table
php artisan make:migration create_users_table
php artisan make:migration create_profiles_table
php artisan make:migration create_roles_table
php artisan make:migration create_role_user_table
php artisan make:migration create_posts_table
php artisan make:migration create_comments_table
php artisan make:migration create_videos_table
php artisan make:migration create_photos_table
php artisan make:migration create_tags_table
php artisan make:migration create_taggables_table

public function up(): void {
    Schema::create('countries', function (Blueprint $table) {
        $table->id();
        $table->string('name')->unique();
        $table->string('iso2', 2)->unique();
        $table->timestamps();
    });
}

public function up(): void {
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->foreignId('country_id')->nullable()->constrained()->nullOnDelete();
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();

        $table->index('country_id');
    });
}

public function up(): void {
    Schema::create('profiles', function (Blueprint $table) {
        $table->id();
        $table->foreignId('user_id')->unique()->constrained()->cascadeOnDelete();
        $table->text('bio')->nullable();
        $table->string('phone')->nullable();
        $table->timestamps();
    });
}

public function up(): void {
    Schema::create('roles', function (Blueprint $table) {
        $table->id();
        $table->string('name')->unique();
        $table->string('label')->nullable();
        $table->timestamps();
    });
}

public function up(): void {
    Schema::create('role_user', function (Blueprint $table) {
        $table->foreignId('role_id')->constrained()->cascadeOnDelete();
        $table->foreignId('user_id')->constrained()->cascadeOnDelete();
        $table->foreignId('assigned_by')->nullable()->constrained('users')->nullOnDelete();
        $table->timestamp('expires_at')->nullable();
        $table->timestamps();

        $table->primary(['role_id', 'user_id']);
    });
}

public function up(): void {
    Schema::create('posts', function (Blueprint $table) {
        $table->id();
        $table->foreignId('user_id')->constrained()->cascadeOnDelete();
        $table->string('title');
        $table->text('body')->nullable();
        $table->enum('status', ['draft','published'])->default('draft');
        $table->timestamps();

        $table->index('user_id');
        $table->index('status');
    });
}

public function up(): void {
    Schema::create('comments', function (Blueprint $table) {
        $table->id();
        $table->foreignId('post_id')->constrained()->cascadeOnDelete();
        $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
        $table->text('content');
        $table->timestamps();

        $table->index(['post_id', 'user_id']);
    });
}

public function up(): void {
    Schema::create('videos', function (Blueprint $table) {
        $table->id();
        $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
        $table->string('title');
        $table->string('url');
        $table->timestamps();

        $table->index('user_id');
    });
}

public function up(): void {
    Schema::create('photos', function (Blueprint $table) {
        $table->id();
        $table->morphs('imageable'); // imageable_type, imageable_id (indexed)
        $table->string('path');
        $table->string('alt')->nullable();
        $table->timestamps();
    });
}

public function up(): void {
    Schema::create('tags', function (Blueprint $table) {
        $table->id();
        $table->string('name')->unique();
        $table->timestamps();
    });
}

public function up(): void {
    Schema::create('taggables', function (Blueprint $table) {
        $table->foreignId('tag_id')->constrained()->cascadeOnDelete();
        $table->morphs('taggable'); // taggable_type, taggable_id
        $table->foreignId('added_by')->nullable()->constrained('users')->nullOnDelete();
        $table->timestamps();

        $table->index(['tag_id', 'taggable_type', 'taggable_id'], 'taggables_full_index');
    });
}

php artisan migrate

php artisan make:model Country
php artisan make:model User
php artisan make:model Profile
php artisan make:model Role
php artisan make:model Post
php artisan make:model Comment
php artisan make:model Video
php artisan make:model Photo
php artisan make:model Tag

class Country extends Model
{
    protected $fillable = ['name', 'iso2'];

    public function users() {
        return $this->hasMany(User::class);
    }

    public function posts() {
        return $this->hasManyThrough(Post::class, User::class);
    }

    public function latestPost() {
        return $this->hasManyThrough(Post::class, User::class)->one()->latestOfMany();
    }
}

class User extends Authenticatable
{
    use HasFactory, Notifiable;

    public function country() { return $this->belongsTo(Country::class); }

    public function profile() { return $this->hasOne(Profile::class); }

    public function posts() { return $this->hasMany(Post::class); }

    public function videos() { return $this->hasMany(Video::class); }

    public function roles() { return $this->belongsToMany(Role::class)->withTimestamps()->withPivot(['assigned_by', 'expires_at']); }

    public function photo() { return $this->morphOne(Photo::class, 'imageable'); }
}

class Profile extends Model
{
    protected $fillable = ['user_id', 'bio', 'phone'];

    public function user() {
        return $this->belongsTo(User::class);
    }
}

class Role extends Model
{
    protected $fillable = ['name','label'];

    public function users() {
        return $this->belongsToMany(User::class)->withTimestamps();
    }
}

class Post extends Model
{
    protected $fillable = ['user_id','title','body','status'];

    public function user() { return $this->belongsTo(User::class); }

    public function comments() { return $this->hasMany(Comment::class); }

    public function photos() { return $this->morphMany(Photo::class, 'imageable'); }

    public function tags() { return $this->morphToMany(Tag::class, 'taggable')->withPivot(['added_by']); }
}

class Comment extends Model
{
    protected $fillable = ['post_id','user_id','content'];

    public function post() { return $this->belongsTo(Post::class); }
    public function user() { return $this->belongsTo(User::class); }
}

class Video extends Model
{
    protected $fillable = ['user_id','title','url'];

    public function user() { return $this->belongsTo(User::class); }

    public function tags() { return $this->morphToMany(Tag::class, 'taggable')->withPivot(['added_by']); }
}

class Photo extends Model
{
    protected $fillable = ['path','alt'];

    public function imageable() {
        return $this->morphTo();
    }
}

class Tag extends Model
{
    protected $fillable = ['name'];

    public function posts() { return $this->morphedByMany(Post::class, 'taggable'); }

    public function videos() { return $this->morphedByMany(Video::class, 'taggable'); }
}

// Read
$profile = $user->profile;
$owner   = $profile->user;

// Create
$user->profile()->create([
  'bio' => 'I love Laravel',
  'phone' => '0812345678',
]);

// Update
$user->profile->update(['phone' => '0888888888']);

// Read
$posts = $user->posts;
$owner = $post->user;

// Create
$user->posts()->createMany([
  ['title' => 'A', 'status' => 'draft'],
  ['title' => 'B', 'status' => 'published'],
]);

// Update
$user->posts()->where('status', 'draft')->update(['status' => 'published']);

// Read
$roles = $user->roles;

// Create
$user->roles()->attach($roleId, [
  'assigned_by' => auth()->id(),
  'expires_at'  => now()->addMonth(),
]);

// Update
$user->roles()->updateExistingPivot($roleId, [
  'expires_at' => now()->addMonths(3),
]);

// Read
$posts = $country->posts;
$latest = $country->latestPost;

// Create
$user = $country->users()->firstOrFail();
$user->posts()->create(['title' => 'From Country', 'status' => 'published']);

// Update
$country->posts()->where('status', 'draft')->update(['status' => 'published']);

// Read
$photo = $user->photo;
$photos = $post->photos;

// Create
$user->photo()->create([
  'path' => 'avatars/u1.jpg',
  'alt'  => 'User avatar',
]);

$post->photos()->createMany([
  ['path' => 'posts/1/cover.jpg', 'alt' => 'Cover'],
  ['path' => 'posts/1/detail.jpg', 'alt' => 'Detail'],
]);

// Update
$photo->update(['alt' => 'Main Cover']);

$post->photos()->whereNull('alt')->update(['alt' => 'N/A']);

// Read
$postTags = $post->tags;
$videoTags = $video->tags;

$postsOfTag  = $tag->posts;
$videosOfTag = $tag->videos;

// Create
$post->tags()->attach($tagId);
$post->tags()->attach([1 => ['added_by' => auth()->id()], 3 => []]);

// Update
$post->tags()->updateExistingPivot($tagId, ['added_by' => auth()->id()]);

// ดึงเฉพาะ user ที่มี post อย่างน้อย 1 รายการ
$users = User::has('posts')->get();

// ดึงเฉพาะ user ที่มี post ตั้งแต่ 3 รายการขึ้นไป
$users = User::has('posts', '>=', 3)->get();

use Illuminate\Database\Eloquent\Builder;

// ดึงเฉพาะ user ที่มี post ซึ่งเผยแพร่แล้ว (status = published)
$users = User::whereHas('posts', function (Builder $query) {
    $query->where('status', 'published');
})->get();

// ใส่เงื่อนไขจำนวนได้ด้วย เช่น user ที่มี post เผยแพร่แล้วตั้งแต่ 5 รายการขึ้นไป
$users = User::whereHas('posts', function (Builder $query) {
    $query->where('status', 'published');
}, '>=', 5)->get();

use Illuminate\Database\Eloquent\Builder;

// ดึงเฉพาะ user ที่ยังไม่มี post เลย
$users = User::doesntHave('posts')->get();

// ดึงเฉพาะ user ที่ไม่มี post ซึ่งเผยแพร่แล้ว (อาจมีแต่ draft ก็ได้)
$users = User::whereDoesntHave('posts', function (Builder $query) {
    $query->where('status', 'published');
})->get();

// ❌ เกิดปัญหา N+1
$users = User::all();           // 1 query สำหรับดึง user ทั้งหมด

foreach ($users as $user) {
    echo $user->posts->count(); // ยิงเพิ่มอีก 1 query ต่อ user 1 คน
}

// ✅ แก้ปัญหา N+1 ด้วย Eager Loading
$users = User::with('posts')->get(); // เหลือเพียง 2 query เท่านั้น

foreach ($users as $user) {
    echo $user->posts->count();
}

// ดึง user พร้อมจำนวน post ของแต่ละคน
$users = User::withCount('posts')->get();

foreach ($users as $user) {
    echo $user->posts_count; // เข้าถึงผ่าน attribute ชื่อ posts_count
}

use Illuminate\Database\Eloquent\Builder;

// นับ post ทั้งหมด และนับเฉพาะ post ที่เผยแพร่แล้วแยกอีกชุด พร้อมตั้งชื่อ alias
$users = User::withCount([
    'posts',
    'posts as published_posts_count' => function (Builder $query) {
        $query->where('status', 'published');
    },
])->get();

echo $users[0]->posts_count;
echo $users[0]->published_posts_count;

// ถอด role เดียวออกจาก user
$user->roles()->detach($roleId);

// ถอดหลาย role พร้อมกัน
$user->roles()->detach([1, 2, 3]);

// ถอด role ทั้งหมดออก (ไม่ใส่ argument)
$user->roles()->detach();

// หลังจากนี้ user จะเหลือเฉพาะ role 1, 2, 3 เท่านั้น (ตัวอื่นถูกถอดออก)
$user->roles()->sync([1, 2, 3]);

// แนบ pivot data ไปพร้อมกันได้
$user->roles()->sync([
    1 => ['expires_at' => now()->addMonth()],
    2 => ['expires_at' => now()->addMonths(3)],
]);

// เพิ่ม role 4, 5 เข้าไป โดย role เดิมยังอยู่ครบ
$user->roles()->syncWithoutDetaching([4, 5]);

foreach ($user->roles as $role) {
    echo $role->pivot->expires_at;   // อ่านค่า expires_at จากตาราง role_user
    echo $role->pivot->assigned_by;
}

// ดึง user มาพร้อม post แต่เอาเฉพาะ post ที่เผยแพร่แล้ว
$users = User::with(['posts' => fn ($query) => $query->where('status', 'published')])->get();

// โหลด post -> user (เจ้าของ) -> profile แบบซ้อนชั้นในครั้งเดียว
$posts = Post::with('user.profile')->get();

// ใส่หลายความสัมพันธ์พร้อมกันได้
$posts = Post::with(['user.profile', 'comments'])->get();

// belongsTo(RelatedModel::class, foreign_key, owner_key)
public function country() {
    return $this->belongsTo(Country::class, 'country_id', 'id');
}