startmvc php框架学习社区
在Web开发中,多表关联查询是非常常见的需求。StartMVC框架提供了强大而灵活的JOIN操作支持,本文将详细介绍如何使用StartMVC框架的join()
方法进行各种数据库关联查询。
首先,我们准备以下测试表结构:
-- 用户表
CREATE TABLE `sm_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单表
CREATE TABLE `sm_orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`order_no` varchar(50) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0:待支付 1:已支付 2:已取消',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 商品表
CREATE TABLE `sm_products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`price` decimal(10,2) NOT NULL,
`stock` int(11) NOT NULL DEFAULT '0',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单详情表
CREATE TABLE `sm_order_items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入用户数据
INSERT INTO `sm_users` (`username`, `email`) VALUES
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com'),
('王五', 'wangwu@example.com'),
('赵六', 'zhaoliu@example.com');
-- 插入商品数据
INSERT INTO `sm_products` (`name`, `price`, `stock`) VALUES
('iPhone 13', 5999.00, 100),
('MacBook Pro', 12999.00, 50),
('AirPods Pro', 1999.00, 200),
('iPad Air', 4799.00, 80);
-- 插入订单数据
INSERT INTO `sm_orders` (`user_id`, `order_no`, `amount`, `status`) VALUES
(1, 'ORDER20231101', 7998.00, 1),
(1, 'ORDER20231102', 12999.00, 1),
(2, 'ORDER20231103', 5999.00, 0),
(3, 'ORDER20231104', 4799.00, 1),
(2, 'ORDER20231105', 1999.00, 2);
-- 插入订单详情数据
INSERT INTO `sm_order_items` (`order_id`, `product_id`, `quantity`, `price`) VALUES
(1, 1, 1, 5999.00),
(1, 3, 1, 1999.00),
(2, 2, 1, 12999.00),
(3, 1, 1, 5999.00),
(4, 4, 1, 4799.00),
(5, 3, 1, 1999.00);
join($table, $field1, $operator, $field2, $type = '')
参数 | 类型 | 说明 | 示例 |
---|---|---|---|
$table | string | 要连接的表名(不含前缀) | 'orders' |
$field1 | string | 第一个字段(需包含完整表名) | 'sm_users.id' |
$operator | string | 操作符 | '=', '>', '<' 等 |
$field2 | string | 第二个字段(需包含完整表名) | 'sm_orders.user_id' |
$type | string | JOIN类型(可选) | 'INNER', 'LEFT', 'RIGHT' 等 |
内连接返回两个表中满足连接条件的所有记录。
use startmvc\core\Db;
// 查询用户及其订单信息
$result = Db::table('users')
->select('sm_users.username, sm_orders.order_no, sm_orders.amount')
->join('orders', 'sm_users.id', '=', 'sm_orders.user_id') // 默认INNER JOIN
->where('sm_orders.status', 1)
->limit(3)
->get();
生成的SQL:
SELECT sm_users.username, sm_orders.order_no, sm_orders.amount
FROM sm_users
JOIN sm_orders ON sm_users.id = sm_orders.user_id
WHERE sm_orders.status = 1
LIMIT 3
返回结果:
Array(
[0] => Array(
[username] => 张三
[order_no] => ORDER20231101
[amount] => 7998.00
),
[1] => Array(
[username] => 张三
[order_no] => ORDER20231102
[amount] => 12999.00
),
[2] => Array(
[username] => 王五
[order_no] => ORDER20231104
[amount] => 4799.00
)
)
左连接返回左表的所有记录,即使右表中没有匹配的记录。
// 查询所有用户及其订单数量(包括没有订单的用户)
$result = Db::table('users')
->select('sm_users.username, COUNT(sm_orders.id) as order_count')
->join('orders', 'sm_users.id', '=', 'sm_orders.user_id', 'LEFT')
->group('sm_users.id')
->get();
生成的SQL:
SELECT sm_users.username, COUNT(sm_orders.id) as order_count
FROM sm_users
LEFT JOIN sm_orders ON sm_users.id = sm_orders.user_id
GROUP BY sm_users.id
返回结果:
Array(
[0] => Array(
[username] => 张三
[order_count] => 2
),
[1] => Array(
[username] => 李四
[order_count] => 2
),
[2] => Array(
[username] => 王五
[order_count] => 1
),
[3] => Array(
[username] => 赵六
[order_count] => 0 // 注意:赵六没有订单
)
)
右连接返回右表的所有记录,即使左表中没有匹配的记录。
// 查询所有商品的销售情况
$result = Db::table('order_items')
->select('sm_products.name, COUNT(sm_order_items.id) as sold_count')
->join('products', 'sm_order_items.product_id', '=', 'sm_products.id', 'RIGHT')
->group('sm_products.id')
->get();
生成的SQL:
SELECT sm_products.name, COUNT(sm_order_items.id) as sold_count
FROM sm_order_items
RIGHT JOIN sm_products ON sm_order_items.product_id = sm_products.id
GROUP BY sm_products.id
返回结果:
Array(
[0] => Array(
[name] => iPhone 13
[sold_count] => 2
),
[1] => Array(
[name] => MacBook Pro
[sold_count] => 1
),
[2] => Array(
[name] => AirPods Pro
[sold_count] => 2
),
[3] => Array(
[name] => iPad Air
[sold_count] => 1
)
)