#### :-: **數據庫表**
~~~
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tp_book`
-- ----------------------------
DROP TABLE IF EXISTS `tp_book`;
CREATE TABLE `tp_book` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`status` int(11) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of tp_book
-- ----------------------------
INSERT INTO `tp_book` VALUES ('1', '乾坤大挪移', '1', '1');
INSERT INTO `tp_book` VALUES ('2', '六脈神劍', '2', '1');
INSERT INTO `tp_book` VALUES ('3', '如來神掌', '3', '1');
INSERT INTO `tp_book` VALUES ('4', '九陰真經', '4', '1');
INSERT INTO `tp_book` VALUES ('5', '降龍十八掌', '5', '1');
INSERT INTO `tp_book` VALUES ('6', '葵花寶典', '6', '1');
-- ----------------------------
-- Table structure for `tp_profile`
-- ----------------------------
DROP TABLE IF EXISTS `tp_profile`;
CREATE TABLE `tp_profile` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of tp_profile
-- ----------------------------
INSERT INTO `tp_profile` VALUES ('2', '李四的個人資料', '2');
INSERT INTO `tp_profile` VALUES ('3', '王五的個人資料', '3');
INSERT INTO `tp_profile` VALUES ('8', '趙六的個人資料', '6');
INSERT INTO `tp_profile` VALUES ('9', '張三的個人資料', '7');
-- ----------------------------
-- Table structure for `tp_user`
-- ----------------------------
DROP TABLE IF EXISTS `tp_user`;
CREATE TABLE `tp_user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) DEFAULT NULL,
`user_pwd` int(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of tp_user
-- ----------------------------
INSERT INTO `tp_user` VALUES ('7', '張三', null);
INSERT INTO `tp_user` VALUES ('2', '李四', '222222');
INSERT INTO `tp_user` VALUES ('3', '王五', '333333');
INSERT INTO `tp_user` VALUES ('6', '趙六', null);
~~~
#### :-: **模型**
~~~
<?php
namespace app\index\model;
use think\Model;
class UserModel extends Model
{
protected $table = 'tp_user';
public function books()
{
return $this->hasOne('BookModel','user_id','id');
}
public function profile()
{
return $this->hasOne('ProfileModel','user_id','id');
}
~~~
~~~
<?php
namespace app\index\model;
use think\Model;
class ProfileModel extends Model
{
protected $table='tp_profile';
}
~~~
~~~
<?php
namespace app\index\model;
use think\Model;
class BookModel extends Model
{
protected $table = 'tp_book';
protected $autoWriteTimestamp = true;
}
~~~
#### :-: **控制器**
~~~
<?php
namespace app\index\controller;
use app\index\model\ProfileModel;
use app\index\model\UserModel;
use think\Controller;
class Index extends Controller
{
//關聯查詢
public function index()
{
$db = new UserModel();
$user = $db->get('2');
//獲取user表id為2的數據
dump($user->books);
//也可以進行條件搜索
dump($user->books()->where('tp_book.status', 1)->select());//tp_book表下面的status
}
//根據關聯條件來查詢當前模型對象數據,返回當前模型數據
public function inde1()
{
$db = new UserModel();
$users = $db->hasWhere('books', ['name' => '六脈神劍'])->select();
dump($users);
}
//可以使用閉包查詢
public function inde2()
{
$db = new UserModel();
$users = $db->hasWhere('books', function ($query) {
$query->where('name', 'like', '六脈神劍%');
})->select();
dump($users);
}
//預載入查詢
//可以使用預載入查詢解決典型的N+1查詢問題，使用：
public function inde3()
{
$db = new UserModel();
$users = $db->with('books')->select();
dump($users);
foreach ($users as $user) {
echo $user->user_name."<br>";//查詢主表的user_name字段
echo $user->books->name."<br>";//查詢關聯表的name字段
}
}
//對多個關聯的表預載入查詢
public function inde4()
{
$db = new UserModel();
$users = $db->with(['profile', 'books'])->select();
dump($users);
}
//新增數據到關聯的表
public function inde5()
{
$db = new UserModel();
$user = $db->get(1);
// 如果還沒有關聯數據 則進行新增
$user->profile()->save(['name' => '趙六']);//數據新增加到profile表的name字段
}
//更新關聯表數據
public function inde6()
{
$db = new UserModel();
$user = $db->get(1);
$user->profile->save(['name' => 'thinkphpp']);
}
//關聯自動寫入新的信息
public function inde7()
{
$user = new UserModel();
$user->user_name = '張三';//寫入user表user_name字段
$user->user_pwd = '123456';
$profile = new ProfileModel();
$profile->name = '張三的個人資料';//寫入profile表name字段
$user->profile = $profile;
$res = $user->together('profile')->save();
if ($res) {
return '添加成功';
}
return '添加失敗';
}
//關聯自動更新
public function inde8()
{
// 查詢
$users = new UserModel();
$user = $users->get(1);
$user->user_name = '張三豐';//修改user表user_name字段的值
$user->profile->name = '張三豐的個人資料';
// 更新當前模型及關聯模型
$user->together('profile')->save();
}
//關聯自動刪除
public function inde9()
{
// 查詢
$users = new UserModel();
$user = $users->get(1,'profile');
// 刪除當前及關聯模型
$res =$user->together('profile')->delete();
if ($res){
return '刪除成功';
}
return '刪除失敗';
}
}
~~~
### 定義相對關聯
~~~
<?php
namespace app\index\model;
use think\Model;
class ProfileModel extends Model
{
protected $table='tp_profile';
public function user()
{
return $this->belongsTo('UserModel','user_id','id');
}
}
~~~
---------------------------------------------定義相對關聯查詢------------------------------------------------
~~~
//定義相對關聯后查詢數據
public function inde10()
{
$dbs=new ProfileModel();
$profile = $dbs->get(1);
// 輸出User關聯模型的屬性
echo $profile->user->user_name;
}
~~~
## 綁定屬性到父模型
~~~
<?php
namespace app\index\model;
use think\Model;
class UserModel extends Model
{
protected $table = 'tp_user';
protected $autoWriteTimestamp = true;
public function books()
{
return $this->hasOne('BookModel','user_id','id');
}
public function profile()
{
return $this->hasOne('ProfileModel','user_id','id')->bind('name');
}
}
~~~
---------------------------------------------綁定屬性到父模型查詢------------------------------
~~~
//關聯模型字段綁定到主模型后輸出
public function inde11()
{
$db = new UserModel();
$user = $db->get(1,'profile');
// 輸出Profile關聯模型的email屬性
echo $user->name;
}
~~~
