Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

sraines's avatar

Get all from table2 where table1.id = x in join table

I'm working on rebuilding a personal streaming site I made as an excuse to learn Laravel. So let's say I have a few tables: movies, genres, and a join table, movie_genres. Here's how they're setup:

CREATE TABLE `movies` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `summary` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `notes` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `year` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `poster` VARCHAR(191) NOT NULL DEFAULT 'missing-poster.jpg' COLLATE 'utf8mb4_unicode_ci',
    `video` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `drive` VARCHAR(191) NOT NULL DEFAULT 'hdd1' 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=152
;

CREATE TABLE `genres` (
    `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`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=26
;


CREATE TABLE `movie_genres` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `movie_id` INT(10) UNSIGNED NOT NULL,
    `genre_id` INT(10) UNSIGNED NOT NULL,
    `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 `movie_genres_movie_id_genre_id_unique` (`movie_id`, `genre_id`),
    INDEX `movie_genres_genre_id_foreign` (`genre_id`),
    CONSTRAINT `movie_genres_genre_id_foreign` FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`) ON DELETE CASCADE,
    CONSTRAINT `movie_genres_movie_id_foreign` FOREIGN KEY (`movie_id`) REFERENCES `movies` (`id`) ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=404
;

I want to be able to call a method on my Genre model called movies like so:

return App\Genre::find($id)->movies();

This should return info from the movies table for all of the movies in movie_genres with a genre_id of $id. My movies method currently looks like this:

public function movies() {
    return $this->hasMany(MovieGenre::class);
}

But when returning from my API I get a JSON string that looks something like this:

[
    {
        "id": 232,
        "movie_id": 3,
        "genre_id": 13,
        "created_at": "2017-09-10 09:56:12",
        "updated_at": "2017-09-10 09:56:12"
    },
    {
        "id": 233,
        "movie_id": 9,
        "genre_id": 13,
        "created_at": "2017-09-10 11:04:05",
        "updated_at": "2017-09-10 11:04:05"
    },
    {
        "id": 234,
        "movie_id": 10,
        "genre_id": 13,
        "created_at": "2017-09-10 11:08:56",
        "updated_at": "2017-09-10 11:08:56"
    }
]

I don't want to get the data from movie_genres, I want to get the data from movies. Is there an easy Eloquent way to do this?

0 likes
2 replies
BishoyWagih's avatar
Level 13

you need to change the relation between Genre and Movie from hasMany to belongsToMany, so the new relation will be

public function movies() { return $this->belongsToMany(Movie::class , 'movie_genres' , 'genre_id' , 'movie_id'); }

then you can call

return App\Genre::find($id)->load('movies');

Please or to participate in this conversation.