php - Laravel 中的多 JOIN 表

Question

我有下面 4 个表:

跟踪

+----+-----+----------------+-------+
| ID | TID |     TITLE      | ALBUM |
+----+-----+----------------+-------+
|  1 | AAA | Yesterday      |     1 |
|  2 | BBB | Happy          |     2 |
|  3 | CCC | Gangname Style |     3 |
+----+-----+----------------+-------+

专辑

+----+-----+---------+-------+
| ID | AID |  TITLE  | COVER |
+----+-----+---------+-------+
|  1 | AAA | Album A | 1.jpg |
|  2 | BBB | Album B | 2.jpg |
|  3 | CCC | Album C | 3.jpg |
+----+-----+---------+-------+

track_artist

+----+-----+-----------+
| ID | TID | ARTIST_ID |
+----+-----+-----------+
|  1 |   1 |         1 |
|  2 |   2 |         2 |
|  3 |   3 |         3 |
+----+-----+-----------+

艺术家

+----+--------+--------+
| ID |  NAME  | AVATAR |
+----+--------+--------+
|  1 | Taylor | 1.JPG  |
|  2 | T-ara  | 2.JPG  |
|  3 | M2M    | 3.JPG  |
+----+--------+--------+

我想根据 track.TID 获取 track.TITLE、album.TITLE 和 artist.NAME > (其中 TID = $XXX) 在上面的 4 表中。当我使用原始 INNER JOIN MySQL 代码时很容易，但我想在 Laravel 中使用 Eloquent ORM。我怎样才能做到这一点？谢谢。

我分析过:

一首轨道 属于一张专辑

一个轨道 有很多 track_artist

一位track_artist hasOne 艺术家

一个轨道 有很多 艺术家

Answer 1

你可以试试这个

The table should be like this, you need 4 tables

albums(id, title, cover)
artist(id, name, avatar)
tracks(id, album_id, title)
track_artists (id, artist_id, track_id) ManyToMany with tracks

Models

class Album extends Model{

    function tracks(){
        $this->hasMany(Track::class);
    }
}

class Artist extends Model{

   function tracks(){
      $this->belongsToMany(Track::class, 'track_artists'); //here track_artists table is as pivot table
   }   
}

class Track extends Model{

   function album(){
        $this->belongsTo(Album::class);
   }

   function artist(){
      $this->belongsToMany(Artist::class, 'track_artists'); //here track_artists table is as pivot table
   }   
}

Fetch Data

$track = Track::with('album','artists')->find($trackId);

echo $track->title." - ". $track->album->title;
//now print all artist of this track
foreach($track->artists as $artist){
   echo $artist->name;
}

注意:您不需要为 track_artists 创建模型，因为它是 tracks 和 artists< 的数据透视表 表。您可以使用 Track 或 Artist laravel eloquent 模型在数据透视表中插入和更新。不过，如果您愿意，您可以创建它来扩展Pivot。

Save Data in pivot table

$track = Track::find(1);
$artist = Artist::create(['name' => 'Katy Perry', 'avatar' => 'Katy_Perry.jpg']);
$track->artists()->save($artist); //this save track and artist relation in track_artists pivot table

详情https://laravel.com/docs/5.6/eloquent-relationships#many-to-many