Laravel查詢生成器-連接三個(gè)表（A一對(duì)多B，B一對(duì)多C）

我有三張表。

國家：id，country_name州：id，country_id，state_name城市：id，state_id，city_name

我想得到一個(gè)特定的國家（$id=1），每個(gè)州的所有城市。我試試這個(gè)代碼。

DB::table('country as a')
   ->join('state as b', 'b.country_id', '=', 'a.id')
   ->join('city as c', 'c.state_id', '=', 'b.id')
   ->select('a.*', 'b.*', 'c.*') 
   ->where('a.id', '=', $id)
   ->get();

得到這樣的結(jié)果。

result: [
  {id: 1, country_id: 1, country_name: ABC, state_name: aaa, state_id: 0, city_name: city a1},
  {id: 2, country_id: 1, country_name: ABC, state_name: aaa, state_id: 0, city_name: city a2},
  {id: 3, country_id: 1, country_name: ABC, state_name: aaa, state_id: 0, city_name: city a3},
  {id: 4, country_id: 1, country_name: ABC, state_name: aaa, state_id: 0, city_name: city a4},
  {id: 5, country_id: 1, country_name: ABC, state_name: bbb, state_id: 1, city_name: city b1},
  {id: 6, country_id: 1, country_name: ABC, state_name: bbb, state_id: 1, city_name: city b2},
  {id: 7, country_id: 1, country_name: ABC, state_name: bbb, state_id: 1, city_name: city b3},
  {id: 8, country_id: 1, country_name: ABC, state_name: bbb, state_id: 1, city_name: city b4},
  .....
]

我希望能得到這樣的結(jié)果。

result: [
  {
    id: 1, 
    country_id: 1,
    country_name: ABC, 
    state_name: aaa, 
    state_id: 0, 
    city: [
      {city_name: city a1},
      {city_name: city a2},
      {city_name: city a3},
      {city_name: city a4}
    ]
  },
  {
    id: 2, 
    country_id: 1,
    country_name: ABC, 
    state_name: bbb, 
    state_id: 1, 
    city: [
      {city_name: city b1},
      {city_name: city b2},
      {city_name: city b3},
      {city_name: city b4}
    ]
  },
  .....
  and like that for other states.
]

也許有人能幫我給你一個(gè)新的見解。非常感謝你。