memahami relasi database di laravel - Di framework laravel terdapat eloquent yang merupakan fitur untuk mengakses dan memanipulasi data secara lebih singkat dan lebih cepat dibandingkan menggunakan raw bahasa SQL.
Jika kita membuat model untuk table SQL, maka eloquent sudah bisa dipakai secara langsung dengan memanggil class model tersebut. Eloquent bekerja secara ORM (object-relational mapper) yang berjalan dengan memanggil model, kemudian mengkonversi urutan fungsi yang dipanggil ke bahasa SQL. Nah, SQL tersebut yang mengakses dan memanipulasi database.
Jadi kesimpulannya, eloquent hanya sebagai pengonversi function pada class model (php) ke bahasa sql. Berikut ini adalah flow nya.
Adanya eloquent juga sangat memudahkan developer untuk melakukan relasi data. Ini yang akan saya bahas di artikel kali ini. Saya akan coba jelaskan dengan cara mudah dan dengan contoh yang sederhana.
A. Persiapan Data
Kita akan membuat database untuk aplikasi pembelian makanan di restoran dengan relasi data sebagai berikut.
Kita akan membuat 4 table yaitu:
- restaurants
Table ini akan berisi data restoran yang ada. - menus
Table ini akan berisi data menu (hidangan) yang ada di restoran. - users
Table ini akan berisi data user/pelanggan yang bisa melakukan pembelian. - purchases
Table ini akan mencatat pembayaran user dan menu yang dipilih.
1. database/migrations/2021_05_31_154128_restaurants.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class Restaurants extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('restaurants', function (Blueprint $table) {
$table->id();
$table->string("name");
$table->string("location");
$table->string("balance");
$table->text("business_hours")->nullable();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('restaurants');
}
}2. database/migrations/2021_05_31_154315_menus.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class Menus extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('menus', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('restaurant_id');
$table->string('name');
$table->decimal('price');
$table->timestamps();
$table->foreign('restaurant_id')->references('id')->on('restaurants')->onDelete('cascade');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('menus');
}
}3. database/migrations/2021_05_31_154723_users.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class Users extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string("name");
$table->string("location");
$table->decimal("balance");
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}4. database/migrations/2021_05_31_154811_purchases.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class Purchases extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('purchases', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('user_id');
$table->unsignedBigInteger('menu_id');
$table->dateTime('date');
$table->timestamps();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->foreign('menu_id')->references('id')->on('menus')->onDelete('cascade');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('purchases');
}
}Flow dari aplikasi ini nantinya adalah, purchases akan mencatat data pembayaran users dan menus milik sebuah restaurants.
Setelah struktur data selesai, jalankan php artisan migrate dan coba isi beberapa data contoh sesuai dengan aturan relasi yang sudah dibuat.
B. Relasi Data
1. One to Many
Sebuah restoran pasti memiliki banyak menu. Disini, kita bisa terapkan relasi one-to-many dengan me-return data seluruh restoran beserta menu nya.
App\Restaurant.php (model)
public function menus() {
return $this->hasMany('App\Menu');
}App\Http\Controller\RestaurantController (controller)
return Restaurant::with('menus')->get();Result:
[
{
"id": 1,
"name": "Orange House",
"location": "-8.65504,115.110801",
"balance": "4483.84",
"business_hours": "Sunday: 10:45 AM - 5 PM | Monday, Friday: 2:30 PM - 8 PM | Tuesday: 11 AM - 2 PM | Wednesday: 1:15 PM - 3:15 AM | Thursday: 10 AM - 3:15 AM | Saturday: 5 AM - 11:30 AM",
"menus": [
{
"id": 1,
"restaurant_id": 1,
"name": "Kiwiberries",
"price": "10.64"
},
{
"id": 2,
"restaurant_id": 1,
"name": "Dates",
"price": "12.45"
}
]
}
]2. Many to One
Setiap record pembelian (purchases), pasti menyimpan data pembeli (users) dan menu yang dipilih (menus). Purchases bertindak sebagai table "many" yang menyimpan "one" data users dan "one" data menu.
App\Purchase.php (model)
public function menu() {
return $this->belongsTo('App\Menu');
}
public function user() {
return $this->belongsTo('App\User');
}App\Http\Controller\PurchaseController (controller)
return Purchase::with('menu', 'user')->get();Result:
[
{
"id": 1,
"user_id": 1,
"menu_id": 13271,
"date": "2020-02-10 04:09:00",
"user": {
"id": 1,
"name": "Don Reichert",
"location": "-8.640233,115.228221",
"balance": "700.70"
},
"menu": {
"id": 13271,
"restaurant_id": 1561,
"name": "Dos Equis Light Hybrid Beer",
"price": "13.18"
}
},
{
"id": 2,
"user_id": 1,
"menu_id": 10679,
"date": "2020-04-03 13:56:00",
"user": {
"id": 1,
"name": "Don Reichert",
"location": "-8.640233,115.228221",
"balance": "700.70"
},
"menu": {
"id": 10679,
"restaurant_id": 1256,
"name": "Coconut Key Lime Pie with Toffee Bits",
"price": "12.81"
}
},
{
"id": 3,
"user_id": 1,
"menu_id": 11466,
"date": "2020-02-29 00:13:00",
"user": {
"id": 1,
"name": "Don Reichert",
"location": "-8.640233,115.228221",
"balance": "700.70"
},
"menu": {
"id": 11466,
"restaurant_id": 1347,
"name": "Delirium Pilsner",
"price": "11.22"
}
},
{
"id": 4,
"user_id": 1,
"menu_id": 9168,
"date": "2018-05-13 18:02:00",
"user": {
"id": 1,
"name": "Don Reichert",
"location": "-8.640233,115.228221",
"balance": "700.70"
},
"menu": {
"id": 9168,
"restaurant_id": 1087,
"name": "Patagonia Stout",
"price": "13.03"
}
},
{
"id": 5,
"user_id": 1,
"menu_id": 15372,
"date": "2018-11-16 06:49:00",
"user": {
"id": 1,
"name": "Don Reichert",
"location": "-8.640233,115.228221",
"balance": "700.70"
},
"menu": {
"id": 15372,
"restaurant_id": 1811,
"name": "Neapolitan Brownie with Marshmallows",
"price": "12.46"
}
}
]3. Has Many Through
Ini adalah fungsi eloquent untuk menampilkan data dengan relasi lebih dari 2 tabel. Misalnya ketika kita ingin menampilkan restaurants beserta data purchases.
Result:
[
{
"id": 1,
"name": "Orange House",
"location": "-8.65504,115.110801",
"balance": "4483.84",
"business_hours": "Sunday: 10:45 AM - 5 PM | Monday, Friday: 2:30 PM - 8 PM | Tuesday: 11 AM - 2 PM | Wednesday: 1:15 PM - 3:15 AM | Thursday: 10 AM - 3:15 AM | Saturday: 5 AM - 11:30 AM",
"transactions": [
{
"id": 1412,
"user_id": 160,
"menu_id": 2,
"date": "2020-05-23 08:39:00"
},
{
"id": 6391,
"user_id": 688,
"menu_id": 2,
"date": "2018-11-26 20:44:00"
},
{
"id": 1329,
"user_id": 151,
"menu_id": 4,
"date": "2019-04-07 11:47:00"
},
{
"id": 5248,
"user_id": 560,
"menu_id": 5,
"date": "2018-06-30 16:05:00"
},
{
"id": 7930,
"user_id": 848,
"menu_id": 5,
"date": "2020-01-11 13:27:00"
},
{
"id": 7148,
"user_id": 766,
"menu_id": 10,
"date": "2018-03-20 09:22:00"
}
]
},
{
"id": 2,
"name": "34 Grill & Tap",
"location": "-8.679412,115.136036",
"balance": "4882.81",
"business_hours": "Sun, Fri: 6 AM - 9 PM | Mon, Weds: 11:45 AM - 4:45 PM | Tues: 7:45 AM - 2 AM | Thurs: 5:45 PM - 12 AM | Sat: 10:15 AM - 9 PM",
"transactions": [
{
"id": 498,
"user_id": 53,
"menu_id": 19,
"date": "2019-01-17 18:47:00"
},
{
"id": 7928,
"user_id": 848,
"menu_id": 19,
"date": "2019-08-26 00:54:00"
},
{
"id": 6636,
"user_id": 714,
"menu_id": 20,
"date": "2020-01-18 03:39:00"
},
{
"id": 5721,
"user_id": 617,
"menu_id": 23,
"date": "2018-05-17 03:21:00"
}
]
}
]C. Conditional Rendering
1. With-Where
Fungsi ini digunakan untuk merender tabel kiri dan tabel kanan. Namun untuk tabel kanan hanya akan tampil sesuai syarat kondisional (tidak mempengaruhi tabel kiri).
App\Http\Controller\RestaurantController (controller)
return Restaurant::with([
'menus' => function ($q) {
$q->where('id', '=', 1); // id menu
}
])->get();Result:
(hanya menampilkan menu sesuai syarat kondisi)
[
{
"id": 1,
"name": "Orange House",
"location": "-8.65504,115.110801",
"balance": "4483.84",
"business_hours": "Sunday: 10:45 AM - 5 PM | Monday, Friday: 2:30 PM - 8 PM | Tuesday: 11 AM - 2 PM | Wednesday: 1:15 PM - 3:15 AM | Thursday: 10 AM - 3:15 AM | Saturday: 5 AM - 11:30 AM",
"menus": [
{
"id": 1,
"restaurant_id": 1,
"name": "Kiwiberries",
"price": "10.64"
}
]
},
{
"id": 2,
"name": "34 Grill & Tap",
"location": "-8.679412,115.136036",
"balance": "4882.81",
"business_hours": "Sun, Fri: 6 AM - 9 PM | Mon, Weds: 11:45 AM - 4:45 PM | Tues: 7:45 AM - 2 AM | Thurs: 5:45 PM - 12 AM | Sat: 10:15 AM - 9 PM",
"menus": []
},
{
"id": 3,
"name": "76 King",
"location": "-8.832813,115.130664",
"balance": "1320.19",
"business_hours": "Sunday: 2 PM - 7 PM | Monday, Wednesday: 3:45 PM - 5 PM | Tuesday: 11:30 AM - 3 AM | Thursday: 10 AM - 11:30 PM | Friday: 7 AM - 9:45 AM | Saturday: 12:45 PM - 1:15 PM",
"menus": []
},
{
"id": 4,
"name": "Sweet Steakhouse",
"location": "-8.598099,115.167311",
"balance": "4629.91",
"business_hours": "Sun: 1:15 PM - 12:30 AM | Mon: 5:30 AM - 6 PM | Tue: 10 AM - 12:15 AM | Wed: 1:45 PM - 4:45 PM | Thu: 7:15 AM - 3:45 AM | Fri: 1:30 PM - 12:45 AM | Sat: 7 AM - 11:45 AM",
"menus": []
},
{
"id": 5,
"name": "Green Coffee",
"location": "-8.795632,115.125848",
"balance": "2614.96",
"business_hours": "Sunday: 12:45 PM - 6:15 PM | Monday, Wednesday: 5:15 AM - 8:30 PM | Tuesday, Saturday: 1:30 PM - 3:45 PM | Thursday: 7:45 AM - 8:15 AM | Friday: 1:30 PM - 7 PM",
"menus": []
}
]2. WhereHas
Fungsi ini digunakan untuk merender tabel kiri dan tabel kanan. Namun, syarat kondisional table kanan akan mempengaruhi hasil dari table kiri.
App\Http\Controller\RestaurantController (controller)
return Restaurant::with('menus')->whereHas('menus', function ($q){
$q->where('id', '=', 1); // id menu
})->get();Result:
(hanya menampilkan restoran dengan menu yang sesuai syarat kondisi)
[
{
"id": 1,
"name": "Orange House",
"location": "-8.65504,115.110801",
"balance": "4483.84",
"business_hours": "Sunday: 10:45 AM - 5 PM | Monday, Friday: 2:30 PM - 8 PM | Tuesday: 11 AM - 2 PM | Wednesday: 1:15 PM - 3:15 AM | Thursday: 10 AM - 3:15 AM | Saturday: 5 AM - 11:30 AM",
"menus": [
{
"id": 1,
"restaurant_id": 1,
"name": "Kiwiberries",
"price": "10.64"
},
{
"id": 2,
"restaurant_id": 1,
"name": "Dates",
"price": "12.45"
},
{
"id": 3,
"restaurant_id": 1,
"name": "Arborio Rice",
"price": "10.59"
},
{
"id": 4,
"restaurant_id": 1,
"name": "Figs juice",
"price": "13.50"
},
{
"id": 5,
"restaurant_id": 1,
"name": "Papaya",
"price": "13.50"
},
{
"id": 6,
"restaurant_id": 1,
"name": "Watermelon",
"price": "12.56"
},
{
"id": 7,
"restaurant_id": 1,
"name": "Budweiser Wood-aged Beer",
"price": "12.38"
},
{
"id": 8,
"restaurant_id": 1,
"name": "Oranges",
"price": "11.64"
},
{
"id": 9,
"restaurant_id": 1,
"name": "Pears smoothie",
"price": "10.51"
},
{
"id": 10,
"restaurant_id": 1,
"name": "French Toast",
"price": "10.20"
},
{
"id": 11,
"restaurant_id": 1,
"name": "Jarrahdale pumpkin",
"price": "14.00"
},
{
"id": 12,
"restaurant_id": 1,
"name": "Papaw",
"price": "11.79"
},
{
"id": 13,
"restaurant_id": 1,
"name": "Salmon Nigiri",
"price": "10.15"
},
{
"id": 14,
"restaurant_id": 1,
"name": "Goji Berry smoothie",
"price": "13.88"
}
]
}
]3. Has
Fungsi ini digunakan untuk merender table kiri yang memiliki data di tabel kanan.
App\Http\Controller\RestaurantController (controller)
return Restaurant::has('menus')->get();Result:
(hanya menampilkan restoran yang memiliki data menu)
[
{
"id": 1,
"name": "Orange House",
"location": "-8.65504,115.110801",
"balance": "4483.84",
"business_hours": "Sunday: 10:45 AM - 5 PM | Monday, Friday: 2:30 PM - 8 PM | Tuesday: 11 AM - 2 PM | Wednesday: 1:15 PM - 3:15 AM | Thursday: 10 AM - 3:15 AM | Saturday: 5 AM - 11:30 AM"
},
{
"id": 2,
"name": "34 Grill & Tap",
"location": "-8.679412,115.136036",
"balance": "4882.81",
"business_hours": "Sun, Fri: 6 AM - 9 PM | Mon, Weds: 11:45 AM - 4:45 PM | Tues: 7:45 AM - 2 AM | Thurs: 5:45 PM - 12 AM | Sat: 10:15 AM - 9 PM"
},
{
"id": 3,
"name": "76 King",
"location": "-8.832813,115.130664",
"balance": "1320.19",
"business_hours": "Sunday: 2 PM - 7 PM | Monday, Wednesday: 3:45 PM - 5 PM | Tuesday: 11:30 AM - 3 AM | Thursday: 10 AM - 11:30 PM | Friday: 7 AM - 9:45 AM | Saturday: 12:45 PM - 1:15 PM"
},
{
"id": 4,
"name": "Sweet Steakhouse",
"location": "-8.598099,115.167311",
"balance": "4629.91",
"business_hours": "Sun: 1:15 PM - 12:30 AM | Mon: 5:30 AM - 6 PM | Tue: 10 AM - 12:15 AM | Wed: 1:45 PM - 4:45 PM | Thu: 7:15 AM - 3:45 AM | Fri: 1:30 PM - 12:45 AM | Sat: 7 AM - 11:45 AM"
},
{
"id": 5,
"name": "Green Coffee",
"location": "-8.795632,115.125848",
"balance": "2614.96",
"business_hours": "Sunday: 12:45 PM - 6:15 PM | Monday, Wednesday: 5:15 AM - 8:30 PM | Tuesday, Saturday: 1:30 PM - 3:45 PM | Thursday: 7:45 AM - 8:15 AM | Friday: 1:30 PM - 7 PM"
}
]4. WithCount-Where
Fungsi ini digunakan untuk menghitung semua hasil pada table kanan yang tampil sesuai syarat kondisional.
App\Http\Controller\RestaurantController (controller)
return Restaurant::withCount([
'menus' => function ($q) {
$q->where('price', '<', 15.00);
}
])->get();Result:
(menampilkan restoran dan jumlah menu dengan harga > 15.00)
[
{
"id": 1,
"name": "Orange House",
"location": "-8.65504,115.110801",
"balance": "4483.84",
"business_hours": "Sunday: 10:45 AM - 5 PM | Monday, Friday: 2:30 PM - 8 PM | Tuesday: 11 AM - 2 PM | Wednesday: 1:15 PM - 3:15 AM | Thursday: 10 AM - 3:15 AM | Saturday: 5 AM - 11:30 AM",
"menus_count": 14
},
{
"id": 2,
"name": "34 Grill & Tap",
"location": "-8.679412,115.136036",
"balance": "4882.81",
"business_hours": "Sun, Fri: 6 AM - 9 PM | Mon, Weds: 11:45 AM - 4:45 PM | Tues: 7:45 AM - 2 AM | Thurs: 5:45 PM - 12 AM | Sat: 10:15 AM - 9 PM",
"menus_count": 12
},
{
"id": 3,
"name": "76 King",
"location": "-8.832813,115.130664",
"balance": "1320.19",
"business_hours": "Sunday: 2 PM - 7 PM | Monday, Wednesday: 3:45 PM - 5 PM | Tuesday: 11:30 AM - 3 AM | Thursday: 10 AM - 11:30 PM | Friday: 7 AM - 9:45 AM | Saturday: 12:45 PM - 1:15 PM",
"menus_count": 9
},
{
"id": 4,
"name": "Sweet Steakhouse",
"location": "-8.598099,115.167311",
"balance": "4629.91",
"business_hours": "Sun: 1:15 PM - 12:30 AM | Mon: 5:30 AM - 6 PM | Tue: 10 AM - 12:15 AM | Wed: 1:45 PM - 4:45 PM | Thu: 7:15 AM - 3:45 AM | Fri: 1:30 PM - 12:45 AM | Sat: 7 AM - 11:45 AM",
"menus_count": 3
},
{
"id": 5,
"name": "Green Coffee",
"location": "-8.795632,115.125848",
"balance": "2614.96",
"business_hours": "Sunday: 12:45 PM - 6:15 PM | Monday, Wednesday: 5:15 AM - 8:30 PM | Tuesday, Saturday: 1:30 PM - 3:45 PM | Thursday: 7:45 AM - 8:15 AM | Friday: 1:30 PM - 7 PM",
"menus_count": 5
}
]5. Having
Digunakan untuk memberi syarat kondisi pada jumlah table dengan withCount.
App\Http\Controller\RestaurantController (controller)
return Restaurant::withCount('menus')->having('menus_count', '<', 5)->get();Result:
(menampilkan restoran dengan jumlah menu < 5)
[
{
"id": 4,
"name": "Sweet Steakhouse",
"location": "-8.598099,115.167311",
"balance": "4629.91",
"business_hours": "Sun: 1:15 PM - 12:30 AM | Mon: 5:30 AM - 6 PM | Tue: 10 AM - 12:15 AM | Wed: 1:45 PM - 4:45 PM | Thu: 7:15 AM - 3:45 AM | Fri: 1:30 PM - 12:45 AM | Sat: 7 AM - 11:45 AM",
"menus_count": 3
},
{
"id": 27,
"name": "Red Juice Bar",
"location": "-8.719262,115.116279",
"balance": "304.11",
"business_hours": "Sunday, Thursday: 7:30 AM - 12:15 AM | Monday-Tuesday: 2:30 PM - 3:15 AM | Wednesday: 2 PM - 3 AM | Friday: 2 PM - 2:45 AM | Saturday: 5:15 AM - 3:45 AM",
"menus_count": 3
},
{
"id": 31,
"name": "Sweet Pizza",
"location": "-8.706058,115.234598",
"balance": "2303.13",
"business_hours": "Sun: 7:45 AM - 1:30 AM | Mon: 12:15 PM - 3 AM | Tues: 5 AM - 7 AM | Weds: 1:15 PM - 1 AM | Thurs, Sat: 10:15 AM - 3:45 AM | Fri: 2:15 PM - 2 AM",
"menus_count": 4
},
{
"id": 35,
"name": "Golden Curry",
"location": "-8.667185,115.210803",
"balance": "4873.88",
"business_hours": "Sun: 3 PM - 2 AM | Mon-Tues: 10:30 AM - 7:15 PM | Weds-Thurs: 10:30 AM - 9 PM | Fri: 5:30 PM - 11:30 PM | Sat: 12:45 PM - 9 PM",
"menus_count": 3
},
{
"id": 44,
"name": "EG Brasserie",
"location": "-8.817656,115.222977",
"balance": "2157.21",
"business_hours": "Sun: 8 AM - 4:30 PM | Mon, Weds: 12:30 PM - 5:30 PM | Tues: 4:45 PM - 9 PM | Thurs, Sat: 6:45 AM - 7 AM",
"menus_count": 4
}
]Oke, itulah penjelasan tentang beberapa fungsi eloquent untuk relasi data di laravel. Semoga bermanfaat! Terima kasih!
No comments:
Post a Comment
Komentar yang bermutu Insyaallah akan mendapatkan berkah