mysql - Laravel 查询生成器 - 连接三个表(A 一对多 B,B 一对多 C)
问题描述
我有三张桌子。
国家:id、country_name
状态:id、country_id、state_name
城市:id、state_id、city_name
我想获得一个包含每个州所有城市的特定国家($id = 1)。
我试试这段代码。
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();
并得到这样的结果。
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},
.....
]
我希望能得到这样的结果。
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.
]
也许有人可以帮助我提供新的见解。
非常感谢。
解决方案
利用雄辩的关系
模型与关系
class Country extends Model
{
public function states()
{
return $this->hasMany(State::class);
}
}
class State extends Model
{
public function country()
{
return $this->belongsTo(Country::class);
}
public function cities()
{
return $this->hasMany(City::class);
}
}
class City extends Model
{
public function state()
{
return $this->belongTo(State::class);
}
}
询问
$countries = Country::query()
->with('states.cities')
->get();
推荐阅读
- python - sql:如何为添加的每个新行增加一个 id?
- python - python中函数对象的哈希
- react-native - 如何使用 Expo 清理(重置缓存)React Native。不知道是不是缓存问题
- python-3.x - 从存储在不同位置的另一个 python 脚本执行(包括打印字符串等)python 脚本
- php - 更改发件人电子邮件地址
- c# - 如何使泛型类实现接口
- puppet - 在 puppet 中选择一个具有给定键值对的嵌套哈希
- hyperledger-composer - 使用超级账本作曲家启动业务网络时出错
- sql - 将一列中的字符串拆分为表维护 ID 的多个分隔符
- php - 与 laravel-datatable 中的排序和分页功能冲突