数据库关联查询

3157字约11分钟

后端 php laravel

2023-07-31

1.下载SQL文件

来自李炎恢老师

2.单对单查询

2.1正向查询

功能要求

获取 id 为 19 的用户的用户资料

2.1.1在 `User` 模型中

public function profile(){
  return $this->hasOne(Profile::class);
  //当然在创建关联关系的时候, 可以指定内键和外键
  //如果不设置的话也会自动生成, 但是可能会不正确
  //return $this->hasOne(Profile::class,$foreignKey,$localKey);
}

2.1.2在控制器中实现

User::query()->find(19)->profile;
//如果使用了where会返回一个数组, 在调用profile时, 需要选取第一位
User::query()->firstWhere('id',19)->profile;
//同理也可以用
User::query()->where('id',19)->first->profile;
//当然也可以, 但是没必要
User::query()->where('id',19)->get()[0]->profile;

2.2反向查询

功能要求

通过第 2 条用户资料查到对应的用户

2.2.1在 `Profile` 模型中

public function user(){
  return $this->belongsTo(User::class);
  //反向也一样可以指定内键和外键, 还有第四个参数, 但是我不知道是干啥的
  //return $this->hasOne(Profile::class,$foreignKey,$ownerKey);
}

2.2.2在控制器中实现

Profile::query()->find(2)->user;

3.单对多查询

3.1正向查询

功能要求

获取 id 为 19 的用户借的所有书

3.1.1在 `User` 模型中

public function book(){
  return $this->hasMany(Book::class);
  //也可以指定内键和外键
  //return $this->hasOne(Book::class,$foreignKey,$localKey);
}

3.1.2在控制器中实现

User::query()->find(19)->book;

3.2反向查询

功能要求

获取 id 为 19 的用户借的所有书中的第一本书上哪个用户的(假设不知道是哪个用户)

3.2.1在 `Book` 模型中

function user(){
  return $this->belongsTo(User::class);
}

3.2.2在控制器中实现

User::query()->find(19)->book()->first()->user;

题外话

如果想要获取第二本书的用户呢

User::query()->find(19)->book()[1]->user

提示

第三本或第 N 本都是同理

4.多对多查询

4.1正向查询

功能要求

获取 id 为 19 的用户借的所有职位

注意

一个用户可以有多个职位

4.1.1在 `User` 模型中

public function role(){
  return $this->belongsToMany(Role::class,'role_user','user_id','role_id','id');
}

4.1.2在控制器中实现

User::query()->find(19)->role;

4.2反向查询

功能要求

获取职位是超级管理员的所有用户

4.2.1在 `Role` 模型中

public function user(){
  // 参数解释在下面
  return $this->belongsToMany(User::class,'role_user','role_id','user_id','id');
}

4.2.2在控制器中实现

Role::query()->firstWhere('type','超级管理员')->user;

4.3简要概括

因为多对多反过来也是多对多, 所以创建关联时的关键字都是belongsToMany 他可以被传入 5 个参数

一共有三张表, 分别是 User 表 和 Role 表 以及 role_user 表

第一个参数: 传入 Role 表, 因为我是在用 User 查询 Role, 反之亦然

第二个参数: 他们中间还需要第三张表, 这张表就记录了 User 和 Role 之间多个的关系

第三个参数: 指向 User 表的 id, 就是这第三张表中记录 User 的 id

第四个参数: 指向 Role 表的 id, 就是第三张表中中记录 Role 的 id

第五个参数: 指向这第三张表的 id

第二个到第五个参数都可以省略,但是我觉得最好不要省略, 容易混乱

根据上面的我们可以写出这样的关联关系(指定用户去查找所有的权限):

public function role(){
  return $this->belongsToMany(Role::class,'role_user','user_id','role_id','id');
}

4.4多对多携带中间表

有的时候还是会需要中间表中的一些参数来做一些逻辑, 因为 laravel 自动只带了两个使用到的字段只需要在定义多对多的最后加上->withPivot('需要携带的字段') 然后就在返回的数据里的pivot里就会有这个需要带上的字段

例子:

public function role(){
  return $this->belongsToMany(Role::class,'role_user','user_id','role_id','id')->withPivot('status');
}

5.远程单对单

功能要求

通过线路中的 ID 来获取城市信息

5.1数据库文件

-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- 主机： localhost
-- 生成日期： 2020-03-15 18:15:35
-- 服务器版本： 10.1.38-MariaDB
-- PHP 版本： 7.3.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 数据库： `railpack`
--

-- --------------------------------------------------------

--
-- 表的结构 `cities`
--

CREATE TABLE `cities` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- 转存表中的数据 `cities`
--

INSERT INTO `cities` (`id`, `name`) VALUES
(1, 'Shanghai'),
(2, 'Suzhou'),
(3, 'Nanjing'),
(4, 'Hangzhou'),
(5, 'Nanchang'),
(6, 'Changsha'),
(7, 'Zhengzhou'),
(8, 'Xian'),
(9, 'Guangzhou'),
(10, 'Beijing');

-- --------------------------------------------------------

--
-- 表的结构 `lines`
--

CREATE TABLE `lines` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `from_station_id` int(11) NOT NULL,
  `to_station_id` int(11) NOT NULL,
  `departure_time` time NOT NULL,
  `arrived_time` time NOT NULL,
  `first_class_price` decimal(10,2) NOT NULL,
  `second_class_price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- 转存表中的数据 `lines`
--

INSERT INTO `lines` (`id`, `from_station_id`, `to_station_id`, `departure_time`, `arrived_time`, `first_class_price`, `second_class_price`) VALUES
(1, 2, 10, '08:05:00', '12:30:00', '933.00', '553.00'),
(2, 2, 6, '13:00:00', '14:15:00', '30.00', '17.00'),
(3, 3, 5, '11:23:00', '12:50:00', '156.00', '87.00'),
(4, 2, 4, '07:00:00', '07:43:00', '36.00', '19.00'),
(5, 1, 7, '16:14:00', '19:29:00', '125.00', '56.00'),
(6, 7, 3, '05:21:00', '08:01:00', '180.00', '103.00'),
(7, 3, 7, '13:05:00', '15:00:00', '180.00', '103.00'),
(8, 3, 9, '17:00:00', '22:18:00', '866.00', '435.00'),
(9, 9, 3, '12:19:00', '18:00:00', '866.00', '435.00'),
(10, 5, 7, '07:22:00', '10:15:00', '345.00', '208.00'),
(11, 4, 10, '08:07:00', '12:27:00', '853.00', '438.00'),
(12, 10, 4, '12:00:00', '17:52:00', '869.00', '454.00'),
(13, 7, 5, '11:00:00', '14:44:00', '343.00', '210.00'),
(14, 1, 3, '13:38:00', '14:09:00', '21.00', '11.00'),
(15, 2, 10, '11:13:00', '17:00:00', '930.00', '550.00'),
(16, 1, 9, '06:14:00', '17:02:00', '430.00', '345.00'),
(17, 1, 9, '09:12:00', '20:27:00', '430.00', '345.00'),
(18, 1, 5, '09:15:00', '13:00:00', '189.00', '75.00'),
(19, 5, 1, '17:00:00', '11:25:00', '180.00', '70.00'),
(20, 8, 1, '11:00:00', '13:22:00', '142.00', '70.00');

-- --------------------------------------------------------

--
-- 表的结构 `orders`
--

CREATE TABLE `orders` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` int(11) NOT NULL,
  `trains_id` int(11) NOT NULL,
  `from_station_id` int(11) NOT NULL,
  `to_station_id` int(11) NOT NULL,
  `ticket_class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- 表的结构 `order_passengers`
--

CREATE TABLE `order_passengers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `order_id` int(11) NOT NULL,
  `passenger_id` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- 表的结构 `passengers`
--

CREATE TABLE `passengers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_card` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- 表的结构 `stations`
--

CREATE TABLE `stations` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `city_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- 转存表中的数据 `stations`
--

INSERT INTO `stations` (`id`, `city_id`, `name`) VALUES
(1, 1, 'North Shanghai'),
(2, 1, 'Hongqiao Shanghai'),
(3, 2, 'North Suzhou'),
(4, 2, 'South Suzhou'),
(5, 3, 'North Nanjing'),
(6, 3, 'South Nanjing'),
(7, 4, 'South Hangzhou'),
(8, 4, 'North Hangzhou'),
(9, 10, 'West Beijing'),
(10, 10, 'South Beijing');

-- --------------------------------------------------------

--
-- 表的结构 `trains`
--

CREATE TABLE `trains` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `lines_id` int(11) NOT NULL,
  `departure_date` date NOT NULL,
  `number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_class_capacity` int(11) NOT NULL,
  `second_class_capacity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- 转存表中的数据 `trains`
--

INSERT INTO `trains` (`id`, `lines_id`, `departure_date`, `number`, `first_class_capacity`, `second_class_capacity`) VALUES
(1, 1, '2020-02-20', 'E24', 50, 80),
(2, 2, '2020-02-20', 'K8529', 40, 90),
(3, 3, '2020-02-20', 'E5279', 40, 80),
(4, 4, '2020-02-20', 'E1096', 50, 70),
(5, 5, '2020-02-20', 'B9429', 20, 90),
(6, 6, '2020-02-20', 'W2047', 30, 90),
(7, 7, '2020-02-20', 'E348', 50, 90),
(8, 8, '2020-02-20', 'E435', 40, 90),
(9, 9, '2020-02-20', 'E853', 80, 90),
(10, 10, '2020-02-20', 'E43', 50, 70),
(11, 11, '2020-02-20', 'E75', 30, 50),
(12, 12, '2020-02-20', 'E34', 50, 70),
(13, 13, '2020-02-20', 'E87', 40, 50),
(14, 14, '2020-02-20', 'K421', 60, 80),
(15, 15, '2020-02-20', 'W42', 25, 90),
(16, 16, '2020-02-20', 'K353', 50, 80),
(17, 17, '2020-02-20', 'K230', 60, 80),
(18, 18, '2020-02-20', 'K39', 10, 70),
(19, 19, '2020-02-20', 'K320', 20, 70),
(20, 20, '2020-02-20', 'E87', 20, 90);

-- --------------------------------------------------------

--
-- 表的结构 `users`
--

CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- 转储表的索引
--

--
-- 表的索引 `cities`
--
ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `lines`
--
ALTER TABLE `lines`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `order_passengers`
--
ALTER TABLE `order_passengers`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `passengers`
--
ALTER TABLE `passengers`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `stations`
--
ALTER TABLE `stations`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `trains`
--
ALTER TABLE `trains`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- 在导出的表使用AUTO_INCREMENT
--

--
-- 使用表AUTO_INCREMENT `cities`
--
ALTER TABLE `cities`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- 使用表AUTO_INCREMENT `lines`
--
ALTER TABLE `lines`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

--
-- 使用表AUTO_INCREMENT `orders`
--
ALTER TABLE `orders`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `order_passengers`
--
ALTER TABLE `order_passengers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `passengers`
--
ALTER TABLE `passengers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `stations`
--
ALTER TABLE `stations`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- 使用表AUTO_INCREMENT `trains`
--
ALTER TABLE `trains`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

--
-- 使用表AUTO_INCREMENT `users`
--
ALTER TABLE `users`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

5.2 代码

5.2.1在 `Line` 模型中

public function fromCity(){
  return $this->hasOneThrough(City::class,Station::class,'id','id','from_station_id','city_id');
}

5.2.2在控制器中实现

Line::query()->firstWhere('id',1)->fromCity

5.3简要概括

hasOneThrough可以被传入 6 个参数

一共有三张表, 分别是 Line 表 和 City 表 以及 Station 表

第一个参数是需要前往查找的第二张表

第二个参数是需要使用第二张表查到的数据去查找的第三张表

第三个参数是第二张表的主键 ( 一般是 id )

第四个参数是第三张表的主键

第五个参数是第一张表中需要去第二张表中查找的 id , 也就是第一张表的 from_station_id 就是第二张表的 id

第六个参数是在第二张表中查到的数据去第三张表中查找的 id , 也就是第二张表中的 city_id 就是第三张表的 id

6.远程单对多

功能要求

通过列车(Train)信息来获取对应的站点(Station)信息

6.1 数据库文件

-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- 主机： 127.0.0.1
-- 生成日期： 2023-01-17 13:51:17
-- 服务器版本： 10.4.24-MariaDB
-- PHP 版本： 8.1.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 数据库： `train`
--

-- --------------------------------------------------------

--
-- 表的结构 `lines`
--

CREATE TABLE `lines` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `from_city_id` int(11) NOT NULL,
  `to_city_id` int(11) NOT NULL,
  `departure_time` time NOT NULL,
  `arrived_time` time NOT NULL,
  `first_class_price` decimal(10,2) NOT NULL,
  `second_class_price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- 转存表中的数据 `lines`
--

INSERT INTO `lines` (`id`, `from_city_id`, `to_city_id`, `departure_time`, `arrived_time`, `first_class_price`, `second_class_price`) VALUES
(1, 1, 2, '08:05:00', '12:30:00', '933.00', '553.00'),
(2, 2, 10, '13:00:00', '14:15:00', '30.00', '17.00'),
(3, 2, 10, '11:23:00', '12:50:00', '156.00', '87.00');

-- --------------------------------------------------------

--
-- 表的结构 `stations`
--

CREATE TABLE `stations` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `city_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- 转存表中的数据 `stations`
--

INSERT INTO `stations` (`id`, `city_id`, `name`) VALUES
(1, 1, 'North Shanghai'),
(2, 1, 'Hongqiao Shanghai'),
(3, 2, 'North Suzhou'),
(4, 2, 'South Suzhou'),
(5, 3, 'North Nanjing'),
(6, 3, 'South Nanjing'),
(7, 4, 'South Hangzhou'),
(8, 4, 'North Hangzhou'),
(9, 10, 'West Beijing'),
(10, 10, 'South Beijing');

-- --------------------------------------------------------

--
-- 表的结构 `trains`
--

CREATE TABLE `trains` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `lines_id` int(11) NOT NULL,
  `departure_date` date NOT NULL,
  `number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_class_capacity` int(11) NOT NULL,
  `second_class_capacity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- 转存表中的数据 `trains`
--

INSERT INTO `trains` (`id`, `lines_id`, `departure_date`, `number`, `first_class_capacity`, `second_class_capacity`) VALUES
(1, 1, '2020-02-20', 'E24', 50, 80),
(2, 2, '2020-02-20', 'K8529', 40, 90),
(3, 3, '2020-02-20', 'E5279', 40, 80);

--
-- 转储表的索引
--

--
-- 表的索引 `lines`
--
ALTER TABLE `lines`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `stations`
--
ALTER TABLE `stations`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `trains`
--
ALTER TABLE `trains`
  ADD PRIMARY KEY (`id`);

--
-- 在导出的表使用AUTO_INCREMENT
--

--
-- 使用表AUTO_INCREMENT `lines`
--
ALTER TABLE `lines`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

--
-- 使用表AUTO_INCREMENT `stations`
--
ALTER TABLE `stations`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- 使用表AUTO_INCREMENT `trains`
--
ALTER TABLE `trains`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

6.2 代码

6.2.1在 `Train` 模型中

public function fromStations(){
  return $this->hasManyThrough(Station::class, Line::class, 'id', 'city_id', 'lines_id', 'from_city_id');
}

6.2.2在控制器中实现

Train::query()->firstWhere('id',1)->fromStations

6.3简要概括

hasManyThrough可以被传入 6 个参数

一共有三张表, 分别是 Train 表 和 Line 表 以及 Station 表

第一个参数是 Station 表 第二个参数是 Line 表 第三个参数是 Line 表 的外键名第四个参数是 Station 表 的外键名第五个参数是 Train 表 的本地键名第六个参数是 Line 表 的本地键名

可能有点混乱, 大概的流程就是

从 Train 表 中查找 id 是 1 的列车然后根据数据中的 lines_id 去 Line 表 中查找与 from_city_id 相同的数据然后根据数据中的 from_city_id 去 Station 表 中查找与 city_id 相同的数据