Eloquent ORM nested eager loaded relations

Posted 3 months ago by sraines

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 sign in or create an account to participate in this conversation.