Eloquent ORM nested eager loaded relations

Posted 3 weeks 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.

Reply to

Use Markdown with GitHub-flavored code blocks.