@vincent15000
Sorry, I thought I was clear but perhaps not.
- I have a
songs table which contains all the info about each song the band have/have played.
- Then i have a
shows table which contains all the live shows they've played. Date, venue, country, etc.
- Finally there is a
releases table which contains their albums, EPs - title, release date, artwork etc
Then as I say, these are all related. songs belong to many shows (ie the setlists) and also many releases (tracklists of each album). So there are release_song and song_show pivot tables.
That means each release has many songs, and in turn each song has been played at many shows.
//Models/Song.php
public function shows()
{
return $this->belongsToMany(Show::class);
}
public function releases()
{
return $this->belongsToMany(Release::class)->withPivot(
'is_main'
);
}
//Models/Release.php
public function songs()
{
return $this->belongsToMany(Song::class)->withPivot('is_main', 'order');
}
//Models/Show.php
public function songs()
{
return $this->belongsToMany(Song::class);
}
So on the page for each release /release/{release}/{slug} I need to get all of the songs on that release. That is the basic $release->songs() relationship. No problem. But then I want to get the first and latest shows each of those songs was played at.
As I say when I'm just on the song page, for a single song, this is simple.
$firstShow = $song->shows()->orderBy('date')->first();
$latestShow = $song->shows()->orderBy('date', desc)->first();
Just two queries. But a release might have upwards of 20 songs, and I need to get the first and last show for each one, on one page/view.
I get the main songs data by using $release->songs() of course, but then I need a with('something') where something is returning those two shows. Well, two somethings I guess. ('firstShow') and ('latestShow').
As I say, as far as I can understand hasOne..latestOfMany doesn't work on a M2M relationship. When I tried it, I got an error about missing columns because it's trying to look at the wrong table. However since I posted originally I had come up with functions that do work
//Songs.php Model
public function firstShow()
{
return $this->belongsToMany(Show::class)->oldest('date')->limit(1);
}
public function latestShow()
{
return $this->belongsToMany(Show::class)->latest('date')->limit(1);
}
//Release.php Model
public function getSongs()
{
$songs = $this->songs()->orderBy('order')->withCount('shows')->with(['firstShow', 'latestShow'])->get();
}
//ReleaseController.php
public function show(Release $release)
{
return view('releases.show', [
'release' => $release,
'songs' => $release->getSongs(),
]);
}
Which works, not sure if it's the most efficent though? It only executes one query each for all of the songs but it's relatively complex.
select * from (select "shows".*, "show_song"."song_id" as "pivot_song_id", "show_song"."show_id" as "pivot_show_id", row_number() over (partition by "show_song"."song_id" order by "date" asc) as "laravel_row" from "shows" inner join "show_song" on "shows"."id" = "show_song"."show_id" where "show_song"."song_id" in (56, 90, 107, 139, 146, 147, 149, 174, 182, 222, 233, 259, 284, 309, 350, 429, 469, 477, 497) and "shows"."deleted_at" is null) as "laravel_table" where "laravel_row" <= 1 order by "laravel_row"
The second one the same but with order by "date" desc of course.
Something else => I wonder why you have at the same time song and slug in this route : /song/{song}/{slug}/stats.
Because the slug is just the song title which may not be unique (usually cover songs with the same title as an original song), so I still use the ID as the unique identifier and include the slug for a clearer/nicer URI.
I suppose I could modify the slug to include the artist name or something so they were all unique but I dunno, this seemed fine, for now at least. This is still my 'learn Laravel with my first proper website' project. :)
