You better show your method code for better understanding.
Eloquent ORM nested eager loaded relations
Okay, I have a Laravel app I'm working on that's like a Netflix style streaming service. My main table for movies / TV shows looks like this:
CREATE TABLE `media` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`media_type` ENUM('movie','show') NOT NULL COLLATE 'utf8mb4_unicode_ci',
`title` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`summary` VARCHAR(4000) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`notes` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`poster` VARCHAR(191) NOT NULL DEFAULT 'missing-poster.jpg' COLLATE 'utf8mb4_unicode_ci',
`jumbotron` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=176
;
I designed everything with the idea that there could be multiple hard drives with content on them in the future (right now there's only one). So I have a drives table:
CREATE TABLE `drives` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE INDEX `drives_name_unique` (`name`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2
;
I also have an episodes table that looks like this:
CREATE TABLE `episodes` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`media_id` INT(10) UNSIGNED NOT NULL,
`season` INT(10) UNSIGNED NOT NULL,
`episode_number` INT(10) UNSIGNED NOT NULL,
`title` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`summary` VARCHAR(4000) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE INDEX `episodes_media_id_season_episode_number_unique` (`media_id`, `season`, `episode_number`),
CONSTRAINT `episodes_media_id_foreign` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=616
;
And finally I have the pivot tables, episode_file and file_movie that join a drive with an episode or movie:
CREATE TABLE `episode_file` (
`episode_id` INT(10) UNSIGNED NOT NULL,
`drive_id` INT(10) UNSIGNED NOT NULL,
`filename` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE INDEX `episode_file_drive_id_episode_id_unique` (`drive_id`, `episode_id`),
INDEX `episode_file_episode_id_foreign` (`episode_id`),
CONSTRAINT `episode_file_drive_id_foreign` FOREIGN KEY (`drive_id`) REFERENCES `drives` (`id`) ON DELETE CASCADE,
CONSTRAINT `episode_file_episode_id_foreign` FOREIGN KEY (`episode_id`) REFERENCES `episodes` (`id`) ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
CREATE TABLE `file_movie` (
`media_id` INT(10) UNSIGNED NOT NULL,
`drive_id` INT(10) UNSIGNED NOT NULL,
`filename` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE INDEX `file_movie_drive_id_media_id_unique` (`drive_id`, `media_id`),
INDEX `file_movie_media_id_foreign` (`media_id`),
CONSTRAINT `file_movie_drive_id_foreign` FOREIGN KEY (`drive_id`) REFERENCES `drives` (`id`) ON DELETE CASCADE,
CONSTRAINT `file_movie_media_id_foreign` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
What I want is to be able to return from a media controller one record that looks like this as a Javascript object:
{
id: 1,
title: 'Alien',
summary: 'fdsafdsafdsa',
notes: null,
poster: 'alien.jpg',
jumbotron: null,
created_at: '2019-05-21 00:00:00',
updated_at: '2019-05-21 00:00:00',
file: {
drive_id: 1,
media_id: 1,
created_at: '2019-05-21 00:00:00',
updated_at: '2019-05-21 00:00:00',
drive: {
id: 1,
name: 'hdd1',
created_at: '2019-05-21 00:00:00',
updated_at: '2019-05-21 00:00:00',
}
}
}
I have it now where I can join the episode_file or file_movie record onto the media record, but I don't have the 'name' field of the drive from the drives table (hdd1 in this example). That's what I want, to be able to get that without a second query or API call. Anyone know how I can do this with Eloquent relationships?
EDIT: Some more detail, here's my Media model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Laravel\Scout\Searchable;
class Media extends Model
{
use Searchable;
public function genres() {
return $this->belongsToMany('App\Genre');
}
public function collections() {
return $this->belongsToMany('App\Collection');
}
public function filename() {
return $this->hasOne('App\FileMovie');
}
public function episodes() {
return $this->hasMany('App\Episode', 'media_id', 'id');
}
public function toSearchableArray() {
$array = $this->toArray();
$array['created_at_unix'] = $this->created_at->timestamp;
$array['updated_at_unix'] = $this->updated_at->timestamp;
return $array;
}
}
My Drive model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Drive extends Model
{
//
}
The EpisodeFile and FileMovie models:
<?php
namespace App;
use Illuminate\Database\Eloquent\Relations\Pivot;
class EpisodeFile extends Pivot
{
public $table = 'episode_file';
}
<?php
namespace App;
use Illuminate\Database\Eloquent\Relations\Pivot;
class FileMovie extends Pivot
{
public $table = 'file_movie';
}
Here's my MediaController:
<?php
namespace App\Http\Controllers;
use App\Media;
use Illuminate\Http\Request;
class MediaController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
//
}
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
//
}
/**
* Display the specified resource.
*
* @param \App\Media $media
* @return \Illuminate\Http\Response
*/
public function show(Media $media, $id)
{
return
Media::find($id)
->load('genres')
->load('episodes')
->load('filename');
}
/**
* Update the specified resource in storage.
*
* @param \Illuminate\Http\Request $request
* @param \App\Media $media
* @return \Illuminate\Http\Response
*/
public function update(Request $request, Media $media)
{
//
}
/**
* Remove the specified resource from storage.
*
* @param \App\Media $media
* @return \Illuminate\Http\Response
*/
public function destroy(Media $media)
{
//
}
}
And here's what the JSON looks like when I call that controller:
{
"id": 94,
"media_type": "movie",
"title": "Spider-Man: Homecoming",
"summary": "Peter Parker balances his life as an ordinary high school student in Queens with his superhero alter-ego Spider-Man, and finds himself on the trail of a new menace prowling the skies of New York City.",
"notes": null,
"poster": "spiderman-homecoming.jpg",
"jumbotron": "",
"created_at": "2017-10-18 20:57:32",
"updated_at": "2017-10-18 20:57:32",
"genres": [
{
"id": 1,
"name": "Action",
"created_at": "2018-03-04 08:42:36",
"updated_at": "2018-03-04 08:42:36",
"pivot": {
"media_id": 94,
"genre_id": 1
}
},
{
"id": 5,
"name": "Comedy",
"created_at": "2018-03-04 08:42:37",
"updated_at": "2018-03-04 08:42:37",
"pivot": {
"media_id": 94,
"genre_id": 5
}
},
{
"id": 22,
"name": "Superhero",
"created_at": "2018-03-04 08:42:37",
"updated_at": "2018-03-04 08:42:37",
"pivot": {
"media_id": 94,
"genre_id": 22
}
}
],
"episodes": [],
"filename": {
"media_id": 94,
"drive_id": 1,
"filename": "spiderman-homecoming.mp4",
"created_at": "2019-05-21 11:16:08",
"updated_at": "2019-05-21 11:16:08"
}
}
Again, basically I just want to also get the drive name somehow in that filename property.
Please or to participate in this conversation.