- 問(wèn)答系統(tǒng)
用戶(hù)API
- Migration
1. 簡(jiǎn)介
什么是Migration呢？
- 是對(duì)數(shù)據(jù)表操作的一套機(jī)制
- 將sql語(yǔ)句轉(zhuǎn)換成PHP語(yǔ)言去執(zhí)行
- 可做到對(duì)數(shù)據(jù)庫(kù)結(jié)構(gòu)的版本控制
為什么需要Migration呢？
解決團(tuán)隊(duì)合作下數(shù)據(jù)庫(kù)結(jié)構(gòu)不統(tǒng)一的問(wèn)題
2. 傳統(tǒng)操作數(shù)據(jù)庫(kù)的方式
開(kāi)啟數(shù)據(jù)庫(kù)服務(wù)，使用命令行連接數(shù)據(jù)庫(kù)。
mysql -uroot -p
建立數(shù)據(jù)庫(kù)
create database zhihu
使用數(shù)據(jù)庫(kù)
use zhihu
創(chuàng)建數(shù)據(jù)表文件database.sql并創(chuàng)建表結(jié)構(gòu)。
DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user`(
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '用戶(hù)名',
`password` VARCHAR(64) NOT NULL DEFAULT '' UNIQUE COMMENT '密碼',
PRIMARY KEY `id`(`id`),
UNIQUE KEY `user_username_unique`(`username`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 COMMENT='用戶(hù)表';
導(dǎo)出數(shù)據(jù)庫(kù)
mysqldump -uroot -p zhihu > zhihu.sql
3. 使用Migration操作數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)表明約定使用復(fù)數(shù)形式
使用migration創(chuàng)建數(shù)據(jù)表users
php artisan make:migration create_table_users --create=users
# laravel5.4 中此語(yǔ)句語(yǔ)法報(bào)錯(cuò)
[ErrorException]
include(/Users/junchow/Code/laravel/vendor/composer/../../database/migrations/2017_06_22_073036_create_table_users.php): failed to open stream: No such file or directory
# create_table_users 修改語(yǔ)法為 create_users_table
php artisan make:migration create_users_table --create=uesrs
創(chuàng)建文件位于
\database\migrations\2017_03_02_030628_create_table_users.php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTableUsers extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
// 創(chuàng)建數(shù)user據(jù)庫(kù)表
Schema::create('users',function(Blueprint $table){
$table->unsignedInteger('id',10)->autoIncrement();// 創(chuàng)建數(shù)據(jù)表自增主鍵
// $table->increments('id');
$table->string('username',20)->nullable()->unique();//字符串類(lèi)型為空唯一
$table->string('password',64);
$table->text('intro')->nullable();
$table->string('email',128)->unique()->nullable();
$table->string('country_code',10)->default('+86');
$table->string('phone',20)->unique()->nullable();
$table->text('avatar')->nullable();
$table->timestamps();
});
//修改表名
//Schema::rename('users','admins');
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
//刪除表
Schema::drop('users');
}
}
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('username',20)->default('')->comment('賬戶(hù)')->unique();
$table->string('password',255)->default('')->comment('密碼')->unique();
$table->string('email',128)->default('')->comment('郵箱')->unique();
$table->string('phone',30)->default('')->comment('手機(jī)')->unique();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}
執(zhí)行migration創(chuàng)建表類(lèi)的up()內(nèi)的操作
// 查看執(zhí)行的SQL
$ php artisan migrate --pretend [15:49:09]
CreateUsersTable: create table `users` (`id` int unsigned not null auto_increment primary key, `username` varchar(20) not null default '' comment '賬戶(hù)', `password` varch(255) not null default '' comment '密碼', `email` varchar(128) not null default '' comment '郵箱', `phone` varchar(30) not null default '' comment '手機(jī)', `created_at` tip null, `updated_at` timestamp null) default character set utf8mb4 collate utf8mb4_unicode_ci
CreateUsersTable: alter table `users` add unique `users_username_unique`(`username`)
CreateUsersTable: alter table `users` add unique `users_password_unique`(`password`)
CreateUsersTable: alter table `users` add unique `users_email_unique`(`email`)
CreateUsersTable: alter table `users` add unique `users_phone_unique`(`phone`)
// 執(zhí)行SQL
php artisan migrate
執(zhí)行Migration創(chuàng)建表類(lèi)的down()內(nèi)的操作
// 查看執(zhí)行的SQL
php artisan migrate:rollback --pretend
// 回滾到上一次執(zhí)行的SQL
php artisan migrate:rollback
數(shù)據(jù)填充
# 創(chuàng)建數(shù)據(jù)填充
php artisan make:seeder UsersTableSeeder
# app/database/seeds/UsersTableSeeder.php
<?php
use Illuminate\Database\Seeder;
class UsersTableSeeder extends Seeder
{
public function run()
{
// 使用工廠(chǎng)批量生成數(shù)據(jù)
factory('App\Models\User',10)->create()->each(function($u){
//$u->posts()->save(factory('App\Models\Post')->make());
});
}
}
# app/database/factories/ModelFactory.php
<?php
use App\Models\User;
$factory->define(User::class, function (Faker\Generator $faker) {
static $password;
return [
'username' => $faker->username,
'password' => $password ?: $password = bcrypt('secret'),
'email' => $faker->unique()->safeEmail,
'phone' => str_random(10),
//'remember_token' => str_random(10),
];
});
# 批量填充數(shù)據(jù)
php artisan db:seed --class=UsersTableSeeder
# 若出現(xiàn)錯(cuò)誤
[PDOException]
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '$2y$10$Hni.nH7IseLz/ifwgoj5qe7p8klDdbMui1pW1cQUGguuI0tXEu.Ha' for key 'users_password_unique'
問(wèn)題原因是 password 設(shè)置了 unique 索引，而批量填充的時(shí)候統(tǒng)一使用了 secret 密碼。可暫時(shí)刪除索引，或修改隨機(jī)種子。
創(chuàng)建用戶(hù)資料表
php artisan make:migration craete_profiles_table --create=profiles
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateProfilesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('profiles', function (Blueprint $table) {
$table->increments('id');
$table->unsignedInteger('user_id',10)->default('0')->comment('用戶(hù)編號(hào)');
$table->foreign('user_id')->references('users')->on('id')->onDelete('cascade');
$table->string('nickname',20)->default('')->comment('昵稱(chēng)');
$table->string('realname',20)->default('')->comment('真名');
$table->string('avatar',255)->default('')->comment('頭像');
$table->text('introduce')->nullable()->comment('簡(jiǎn)介');
$table->boolean('gender')->default(0)->comment('性別');
$table->date('birth')->default('0000-00-00')->comment('出生日期');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('profiles');
}
}
執(zhí)行遷移后出現(xiàn)錯(cuò)誤1
$ php artisan migrate [16:14:49]
[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'user_id' (SQL: create table `profiles` (`id` int unsigned not null auto_increment
primary key, `user_id` int unsigned not null default '0' auto_increment primary key comment '用戶(hù)編號(hào)', `nickname` varchar(20) not null default '' comment '昵稱(chēng)', `r
ealname` varchar(20) not null default '' comment '真名', `avatar` varchar(255) not null default '' comment '頭像', `introduce` text null comment '簡(jiǎn)介', `gender` tin
yint(1) not null default '0' comment '性別', `birth` date not null default '0000-00-00' comment '出生日期', `created_at` timestamp null, `updated_at` timestamp null)
default character set utf8mb4 collate utf8mb4_unicode_ci)
[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'user_id'
FAIL: 1
錯(cuò)誤定位：
`user_id` int unsigned not null default '0' auto_increment primary key comment '用戶(hù)編號(hào)',
解決方案：
Remove the second parameter in the integer method. It sets the column as auto-increment. Check the Laravel API for more detials.
整型中不能加入字段長(zhǎng)度，否則會(huì)出現(xiàn) auto_increment primary
數(shù)據(jù)遷移錯(cuò)誤問(wèn)題2：
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'birth'
錯(cuò)誤定位：
$table->date('birth')->default('0000-00-00')->comment('出生日期');
解決方案：
Mysql配置的問(wèn)題，查證后確定是mysql配置項(xiàng)sql_mode中的NO_ZERO_IN_DATE和NO_ZERO_DATE導(dǎo)致的問(wèn)題。
NO_ZERO_DATE：在非嚴(yán)格模式下，可以插入形如“0000-00-00 00:00:00”的非法日期，MySQL數(shù)據(jù)庫(kù)僅拋出一個(gè)警告。而啟用該選項(xiàng)后，MySQL數(shù)據(jù)庫(kù)不允許插入零日期，插入零日期會(huì)拋出錯(cuò)誤而非警告。
NO_ZERO_IN_DATE：在嚴(yán)格模式下，不允許日期和月份為零。如“2011-00-01”和“2011-01-00”這樣的格式是不允許的。采用日期或月份為零的格式時(shí)MySQL都會(huì)直接拋出錯(cuò)誤而非警告。
mysql 執(zhí)行
SHOW VARIABLES LIKE 'sql_mode';
SELECT @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
mysql5.0以上版本支持三種sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。
- ANSI模式：寬松模式，對(duì)插入數(shù)據(jù)進(jìn)行校驗(yàn)，如果不符合定義類(lèi)型或長(zhǎng)度，對(duì)數(shù)據(jù)類(lèi)型調(diào)整或截?cái)啾４?，?bào)warning警告。
- TRADITIONAL模式：嚴(yán)格模式，當(dāng)向mysql數(shù)據(jù)庫(kù)插入數(shù)據(jù)時(shí)，進(jìn)行數(shù)據(jù)的嚴(yán)格校驗(yàn)，保證錯(cuò)誤數(shù)據(jù)不能插入，報(bào)error錯(cuò)誤。用于事物時(shí)，會(huì)進(jìn)行事物的回滾。
- STRICT_TRANS_TABLES模式：嚴(yán)格模式，進(jìn)行數(shù)據(jù)的嚴(yán)格校驗(yàn)，錯(cuò)誤數(shù)據(jù)不能插入，報(bào)error錯(cuò)誤。
解決方案2：
$table->date('birth')->nullable()->comment('出生日期');
數(shù)據(jù)遷移問(wèn)題3：
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
解決方案：暫時(shí)先屏蔽外鍵連接
4. 用戶(hù)注冊(cè)API
- Route的建立
- Model的建立
- 注冊(cè)方法的建立
接口返回值約定：err表示返回狀態(tài)，err=1表示出錯(cuò)，err=0表示正確。
Route的建立
function userInstance(){
return new App\Model\User;
}
Route::any('/api/signup',function(){
return userInstance()->signup();
});
Model的建立
php artisan make:model Model\User
namespace App\Model;
use Illuminate\Database\Eloquent\Model;
use Request;
use Hash;
class User extends Model
{
//定義表名
protected $table = 'user';
}
注冊(cè)方法的建立
/*用戶(hù)注冊(cè)API*/
public function signup()
{
$username = Request::get('username');
$password = Request::get('password');
//檢查用戶(hù)名密碼是否為空
if(!($username && $password)){
return ['err'=>1, 'msg'=>'用戶(hù)名和密碼必填'];
}
//檢查用戶(hù)名是否存在
if($this->where('username',$username)->exists()){
return ['err'=>1, 'msg'=>'用戶(hù)名已存在'];
}
//加密密碼
// $hash_password = Hash::make($password);
$hash_password = bcrypt($password);//快捷寫(xiě)法
//新增數(shù)據(jù)
$this->username = $username;
$this->password = $hash_password;
if($this->save()){
return ['err'=>0, 'id'=>$this->id];
}else{
return ['err'=>1, 'msg'=>'數(shù)據(jù)庫(kù)新增失敗'];
}
// 插入數(shù)據(jù)并返回
// return $this->save() ? ['err'=>0, 'id'=>$this->id] : ['err'=>1, 'msg'=>'注冊(cè)失敗'];
}
用戶(hù)注冊(cè) API v2
# 路由提取公共部分
// 返回用戶(hù)實(shí)例
function user(){
return new User();
}
//新用戶(hù)注冊(cè)
Route::any('api/user', function(){
return user()->signup();
});
# 接口提取公共部分
/*api common : 檢測(cè)賬戶(hù)和密碼*/
// 注意，雖然是公共方法，但在其他模型中使用，此處不要使用 protected
public function has_username_password()
{
//接收請(qǐng)求參數(shù)并檢查賬戶(hù)和密碼是否同時(shí)存在
$username = Request::get('username');
$password = Request::get('password');
return $username&&$password ? ['username'=>$username, 'password'=>$password] : false;
}
/*api 用戶(hù)注冊(cè)*/
public function signup()
{
//判斷參數(shù)
$user = $this->has_username_password();
if(!$user){
return ['err'=>1, 'msg'=>'賬戶(hù)或密碼為空'];
}
//密碼加密
$this->password = bcrypt($user['password']);//bcrypt()是 Hash::make() 的快捷寫(xiě)法
$this->username = $user['username'];
// 插入數(shù)據(jù)并返回
return $this->save() ? ['err'=>0, 'id'=>$this->id] : ['err'=>1, 'msg'=>'注冊(cè)失敗'];
}
5. 用戶(hù)登錄API
Route的建立
Route::any('/api/login',function(){
return userInstance()->login();
});
登錄方法實(shí)現(xiàn)
/*用戶(hù)登錄API*/
public function login()
{
$username = Request::get('username');
$password = Request::get('password');
//檢測(cè)用戶(hù)名和密碼是否為空
if(!($username && $password)){
return ['err'=>1, 'msg'=>'用戶(hù)名和密碼必填'];
}
//判斷數(shù)據(jù)庫(kù)是否存在
$user = $this->where('username',$username)->first();
if(!$user){
return ['err'=>1, 'msg'=>'用戶(hù)不存在'];
}
//檢查密碼是否匹配
$hash_password = $user->password;
if(!Hash::check($password, $hash_password)){
return ['err'=>1, 'msg'=>'密碼有誤'];
}
//登錄成功保存入session
session()->put('user.id',$user->id);
session()->put('user.username',$user->username);
//dd(session()->all());
return ['err'=>0, 'id'=>$user->id];
}
用戶(hù)注冊(cè)和登錄中存在公共的部分，檢測(cè)用戶(hù)名和密碼是否為空，可提取出來(lái)作為公方法調(diào)用。
/*判斷用戶(hù)名與密碼*/
public function checkUser()
{
$username = Request::get('username');
$password = Request::get('password');
//檢測(cè)用戶(hù)名和密碼是否為空
if($username && $password){
return [$username,$password];
}else{
return false;
}
}
用戶(hù)登陸 API v2
操作流程
- 判斷參數(shù)是否正確 has_username_password()
- 判斷唯一性賬戶(hù)是否存在 first()
- 比對(duì)密碼是否正確 Hash::check()
- 保存回話(huà) session()->put()
session使用
dd(session()->all());
array:6 [▼
"_token" => "49xoCJDqzefW729KZykmfTfc3vZJUORq6YtAYFau"
"_previous" => array:1 [▼
"url" => "http://laravel.app/api/login?password=secret&username=junchow52"
]
"_flash" => array:2 [▼
"old" => []
"new" => []
]
"url" => [] "login_admin_59ba36addc2b2f9401580f014c7f58ea4e30989d" => 1 "PHPDEBUGBAR_STACK_DATA" => []
]
接口實(shí)現(xiàn)
/*api 用戶(hù)登陸*/
public function login()
{
//參數(shù)判斷
$user = $this->has_username_password();
if(!$user){
return ['err'=>1, 'msg'=>'賬戶(hù)或密碼為空'];
}
//檢查賬戶(hù)是否存在
$dbuser = $this->where('username',$user['username'])->first();//獲取唯一一條
if(!$dbuser){
return ['err'=>1, 'msg'=>'賬戶(hù)不存在'];
}
//檢查密碼
if(!Hash::check($user['password'], $dbuser['password'])){
return ['err'=>1, 'msg'=>'密碼錯(cuò)誤'];
}
//回話(huà)記錄
session()->put('user_id', $dbuser->id);
session()->put('username', $dbuser->username);
//dd(session()->all());
return ['err'=>0, 'id'=>$dbuser->id];
}
6. 用戶(hù)退出API
- 選擇性清除session信息
- 頁(yè)面跳轉(zhuǎn)
Route::any('/api/logout',function(){
return userInstance()->logout();
});
/*用戶(hù)退出API*/
public function logout()
{
//session()->flush();//清空所有session信息
//設(shè)置值為null
//session()->put('user.id',null);
//session()->put('user.username',null);
//刪除變量（推薦）
session()->forget('user.id');
session()->forget('user.username');
// dd(session()->all());
return redirect('/');//跳轉(zhuǎn)到首頁(yè)
//return ['err'=>0];//SPA應(yīng)用返回?cái)?shù)據(jù)轉(zhuǎn)換為json
}
用戶(hù)退出 API v2
操作流程
- 清空 session 中保存的數(shù)據(jù)
//小應(yīng)用清空所用 session
//session()->flush();
//設(shè)置 session 中變量
//session()->put('user_id',null);
//session()->put('username',null);
//session 嵌套
//session()->put('user.friend.name','alice');
//dd(session()->all());
接口實(shí)現(xiàn)
/*api 用戶(hù)退出*/
public function logout()
{
session()->forget('user_id');
session()->forget('username');
return ['err'=>0, 'msg'=>'安全退出'];
}
7. 是否登錄
此函數(shù)作為公共方法調(diào)用，不直接使用接口調(diào)用。返回的 false 若在接口中會(huì)直接報(bào)錯(cuò)。
創(chuàng)建路由
Route::any('test',function(){
dd(user()->islogin());
});
實(shí)現(xiàn)方法
/*判斷用戶(hù)是否登錄*/
// 此處不要使用 protected，由于其他模型會(huì)直接使用。
public function islogin()
{
return session('user.id')?:false;
}
注意：此函數(shù)為公共函數(shù)，返回 false頁(yè)面會(huì)出現(xiàn)錯(cuò)誤。僅用于接口內(nèi)部使用。
8. 修改密碼API
創(chuàng)建路由
Route::any('/api/change_password',function(){
return userInstance()->changePassword();
});
實(shí)現(xiàn)方法
/*修改密碼API*/
public function changePassword()
{
//判斷用戶(hù)是否登錄
if(!session('user.id')){
return ['err'=>1,'msg'=>'請(qǐng)先登錄'];
}
//判斷新舊密碼
if(!rq('old_password') || !rq('new_password')){
return ['err'=>1,'msg'=>'舊密碼或舊密碼為空'];
}
//判斷舊密碼是否正確
$user = $this->find(session('user.id'));
if(!Hash::check(rq('old_password'),$user->password)){
return ['err'=>1, 'msg'=>'舊密碼錯(cuò)誤'];
}
//更改密碼
$user->password = bcrypt(rq('new_password'));
return $user->save()?['err'=>0,'msg'=>'修改成功']:['err'=>1,'msg'=>'修改失敗'];
}
# 版本2
/*api 修改密碼*/
public function resetpwd()
{
//判斷是否登錄
if(!$this->islogin()){
return ['err'=>1, 'msg'=>'尚未登錄'];
}
//判斷新舊密碼
if(!rq('oldpwd') || !rq('newpwd')){
return ['err'=>1, 'msg'=>'缺少新舊密碼'];
}
//判斷舊密碼
$user = $this->find(session('user_id'));
if(!Hash::check(rq('oldpwd'), $user->password)){
return ['err'=>1, 'msg'=>'舊密碼錯(cuò)誤'];
}
//更新密碼
$user->password = bcrypt(rq('newpwd'));
return $user->save() ? ['err'=>0,'msg'=>'設(shè)置成功'] : ['err'=>0,'msg'=>'設(shè)置失敗'];
}
9.找回密碼API
創(chuàng)建路由
Route::any('/api/getback_password',function(){
return userInstance()->getbackPassword();
});
通過(guò)手機(jī)短信找回密碼，此時(shí)需要手機(jī)短信服務(wù)商，并需要在users表中新增phone_captcha字段用于存儲(chǔ)短信驗(yàn)證碼，以便于密碼找回時(shí)使用。
創(chuàng)建Migration
php artisan make:migration add_field_phone_captcha -- table=users
添加字段
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class AddFieldPhoneCaptcha extends Migration
{
public function up()
{
Schema::table('users', function (Blueprint $table) {
//添加字段
$table->string('phone_captcha');
});
}
public function down()
{
Schema::table('users', function (Blueprint $table) {
$table->string('phone_captcha');
});
}
}
執(zhí)行Migration
php artisan migrate --pretend
php artisan migrate
php artisan migrate:rollback
實(shí)現(xiàn)接口
/*獲取手機(jī)驗(yàn)證碼*/
public function getPhoneCaptcha()
{
//測(cè)試暫時(shí)使用隨機(jī)數(shù)
return rand(1000,9999);
}
/*發(fā)送短信驗(yàn)證碼*/
public function sendSms($data)
{
return true;
}
/*找回密碼API*/
public function getbackPassword()
{
//使用手機(jī)找回密碼
if(!rq('phone')){
return ['err'=>1, 'msg'=>'請(qǐng)?zhí)顚?xiě)手機(jī)號(hào)碼'];
}
//判斷手機(jī)號(hào)碼是否存在數(shù)據(jù)庫(kù)中
$user = $this->where('phone',rq('phone'))->first();
if(!$user){
return ['err'=>1, 'msg'=>'手機(jī)號(hào)碼不存在'];
}
//獲取手機(jī)驗(yàn)證碼
$phone_captcha = $this->getPhoneCaptcha();
//若保存成功則發(fā)送短信否則給出提示;
$user->phone_captcha = $phone_captcha;
if($user->save()){
//發(fā)送短信驗(yàn)證碼
$this->sendSms($phone_captcha);
return ['err'=>0, 'msg'=>'操作成功'];
}else{
return ['err'=>1,'msg'=>'操作失敗'];
}
}
此時(shí)存在問(wèn)題是，若用戶(hù)使用短信轟炸的方式發(fā)送大量垃圾短信，此此處接口調(diào)用會(huì)出現(xiàn)問(wèn)題，如何預(yù)防此種方式呢？
可采用時(shí)間判斷和黑名單的方式來(lái)對(duì)此進(jìn)行約束，此處使用兩次發(fā)送時(shí)間間隔判斷的方式來(lái)避免機(jī)器人發(fā)送短信。
/*找回密碼API*/
public function getbackPassword()
{
//預(yù)防短信轟炸
$curtime = time();
$smstime = session('send_sms_time');
//若10秒內(nèi)調(diào)用兩次接口
if($curtime-$smstime > 10){
return ['err'=>1,'msg'=>'操作頻繁，請(qǐng)間隔10秒后操作！'];
}
//使用手機(jī)找回密碼
if(!rq('phone')){
return ['err'=>1, 'msg'=>'請(qǐng)?zhí)顚?xiě)手機(jī)號(hào)碼'];
}
//判斷手機(jī)號(hào)碼是否存在數(shù)據(jù)庫(kù)中
$user = $this->where('phone',rq('phone'))->first();
if(!$user){
return ['err'=>1, 'msg'=>'手機(jī)號(hào)碼不存在'];
}
//獲取手機(jī)驗(yàn)證碼
$phone_captcha = $this->getPhoneCaptcha();
//若保存成功則發(fā)送短信否則給出提示;
$user->phone_captcha = $phone_captcha;
if($user->save()){
//發(fā)送短信驗(yàn)證碼
$this->sendSms($phone_captcha);
//檢查兩次短信發(fā)送時(shí)間
session('validate_time',time());
return ['err'=>0, 'msg'=>'操作成功'];
}else{
return ['err'=>1,'msg'=>'操作失敗'];
}
}
10. 驗(yàn)證找回密碼API
創(chuàng)建路由
Route::any('/api/validate_password',function(){
return userInstance()->validatePassword();
});
實(shí)現(xiàn)方法
/*驗(yàn)證找回密碼*/
public function validatePassword()
{
//檢查是否傳入電話(huà)號(hào)碼
if(!rq('phone') || !rq('phone_captcha') || !rq('password')){
return ['err'=>1, 'msg'=>'請(qǐng)?zhí)顚?xiě)手機(jī)號(hào)碼和短信驗(yàn)證碼'];
}
//根據(jù)手機(jī)號(hào)碼獲取用戶(hù)
$user = $this->where(['phone'=>rq('phone'),'phone_captcha'=>rq('phone_captcha')])->first();
if(!$user){
return ['err'=>1,'msg'=>'用戶(hù)不存在或短信驗(yàn)證碼錯(cuò)誤'];
}
//修改密碼
$user->password = bcrypt(rq('password'));
return $user->save() ? ['err'=>0,'msg'=>'操作成功'] : ['err'=>1,'msg'=>'操作失敗'];
}
此處存在的安全漏洞是，當(dāng)找回密碼接口頻繁調(diào)用時(shí)，有可能是采用機(jī)器人不斷進(jìn)行暴力破解的方式，來(lái)對(duì)密碼進(jìn)行猜測(cè)。
封裝公共方法用于判斷是否為機(jī)器人提交
/*判斷是否為機(jī)器人提交*/
public function isRobot($second)
{
if(!session('validate_time')){
return false;
}
//若兩次時(shí)間間隔小于n秒則斷定為機(jī)器人訪(fǎng)問(wèn)
return time()-session('validate_time') < $second;
}
/*更新機(jī)器人時(shí)間*/
public function setRobotTime()
{
// session()->set('validate_time',time());
session(['validate_time'=>time()]);
}
使用時(shí)間間隔判斷來(lái)進(jìn)行約束
/*驗(yàn)證找回密碼*/
public function validatePassword()
{
//判斷兩次調(diào)用接口的時(shí)間間隔來(lái)防止機(jī)器人暴力破解，同時(shí)用戶(hù)可能存在誤操作。
if($this->isRobot(3)){
return ['err'=>1,'msg'=>'操作頻繁，請(qǐng)間隔3秒后操作。'];
}
//檢查是否傳入電話(huà)號(hào)碼
if(!rq('phone') || !rq('phone_captcha') || !rq('password')){
return ['err'=>1, 'msg'=>'請(qǐng)?zhí)顚?xiě)手機(jī)號(hào)碼和短信驗(yàn)證碼'];
}
//根據(jù)手機(jī)號(hào)碼獲取用戶(hù)
$user = $this->where(['phone'=>rq('phone'),'phone_captcha'=>rq('phone_captcha')])->first();
if(!$user){
return ['err'=>1,'msg'=>'用戶(hù)不存在或短信驗(yàn)證碼錯(cuò)誤'];
}
//修改密碼
$user->password = bcrypt(rq('password'));
if($user->save()){
$this->setRobotTime();//更新機(jī)器人行為時(shí)間
return ['err'=>0,'msg'=>'操作成功'];
}else{
return ['err'=>1,'msg'=>'操作失敗'];
}
}
10. 個(gè)人資料API
創(chuàng)建路由
Route::any('/api/profile',function(){
return userInstance()->profile();
});
實(shí)現(xiàn)方法
/*獲取用戶(hù)個(gè)人資料API*/
public function profile()
{
//獲取用戶(hù)編號(hào)，游客也可查看個(gè)人信息。
if(!rq('id')){
return ['err'=>1,'msg'=>'參數(shù)錯(cuò)誤'];
}
//獲取指定用戶(hù)信息
$fields = ['username','avatar','intro'];
$user = $this->find(rq('id'),$fields);
if(!$user){
return ['err'=>1,'msg'=>'用戶(hù)不存在'];
}
//獲取用戶(hù)的提問(wèn)數(shù)
$question_count = questionInstance()->where('user_id',rq('id'))->count();
//獲取用戶(hù)的回答數(shù)
$answer_count = $user->answers()->count();//多表查詢(xún)必須提前建立多表關(guān)系
//返回?cái)?shù)據(jù)
$data = $user->toArray();
$data['question_count'] = $question_count;
$data['answer_count'] = $answer_count;
return ['err'=>0,'data'=>$data];
}
問(wèn)題API
1. 創(chuàng)建問(wèn)題的Migration
php artisan make:migration create_questions_table --create=questions
出現(xiàn)錯(cuò)誤
[ErrorException]
include(/Users/junchow/Code/laravel/vendor/composer/../../database/migrations/2017_06_22_150313_create_questions_table.php): failed to open stream: No such file or directory
解決方案
composer update
composer dumpautoload
database/migrations/2017_03_02_065319_create_questions_table.php
注意：創(chuàng)建表時(shí)表名使用復(fù)數(shù)形式
2. 創(chuàng)建表結(jié)構(gòu)與操作
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTableQuestions extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('questions', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
// 自定義字段
$table->string('title',64)->comment('問(wèn)題標(biāo)題');//標(biāo)題非空可重復(fù)
$table->text('desc')->nullable()->comment('問(wèn)題描述');//描述為空
$table->unsignedInteger('user_id')->comment('用戶(hù)編號(hào)');
$table->tinyInteger('status')->default(0)->comment('審核狀態(tài)');
//外鍵
$table->foreign('user_id')->references('id')->on('users');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('questions');
}
}
# 版本2
Schema::create('questions', function (Blueprint $table) {
$table->increments('id');
$table->string('title',128)->default('')->comment('標(biāo)題');
$table->text('description')->nullable()->comment('描述');
$table->unsignedInteger('user_id')->default('0')->comment('評(píng)論人');
$table->foreign('user_id')->references('id')->on('users');
$table->unsignedInteger('admin_id')->default('0')->comment('審核人');
$table->unsignedInteger('status')->default('0')->comment('審核狀態(tài)');
$table->timestamps();
});
創(chuàng)建表結(jié)構(gòu)
php artisan migrate --pretend
php artisan migrate:rollback
php artisan migrate
3. 創(chuàng)建問(wèn)題模型
php artisan make:model Model\Question
namespace App\Model;
use Illuminate\Database\Eloquent\Model;
class Question extends Model
{
//
}
4.問(wèn)題API的實(shí)現(xiàn)
在路由文件中為對(duì)公共方法進(jìn)行抽取
/*獲取URL地址參數(shù)*/
function rq($key=null, $default=null){
if(!$key){
return Request::all();
}
return Request::get($key,$default);
}
/*實(shí)例化問(wèn)題對(duì)象*/
function questionInstance(){
return new App\Model\Question;
}
版本2
# 路由文件 route.php 或 web.php 中提取公共方法
use Illuminate\Support\Facades\Request;
/*Common API Function*/
function rq($key=null, $default=null){
return $key ? Request::get($key,$default) : Request::all();
}
//獲取 question 模型實(shí)例
function question(){
return new Question();
}
接口返回值約定：err為錯(cuò)誤狀態(tài)，err=1表示出錯(cuò)，err=0表示正確。
4.1 增加問(wèn)題API
建立路由
Route::any('/api/question/add',function(){
return questionInstance()->add();
});
實(shí)現(xiàn)方法
/*添加問(wèn)題API*/
public function add()
{
//判斷用戶(hù)是否登錄
if(!userInstance()->isLogin()){
return ['err'=>1, 'msg'=>'請(qǐng)先登錄'];
}
//判斷必填字段
if(!rq('title')){
return ['err'=>1, 'msg'=>'請(qǐng)?zhí)顚?xiě)標(biāo)題'];
}
//判斷可選字段
if(!rq('desc')){
$this->desc = rq('desc');
}
$this->user_id = session('user.id');
$this->title = rq('title');
return $this->save() ? ['err'=>0, 'id'=>$this->id] : ['err'=>1,'msg'=>'添加失敗'];
}
接口實(shí)現(xiàn) v2
//API 添加問(wèn)題
public function add()
{
//判斷用戶(hù)是否登錄
if(!user()->islogin()){
return ['err'=>1, 'msg'=>'尚未登錄'];
}
$this->user_id = session('user_id');
//判斷標(biāo)題是否傳入（必選）
if(!rq('title')){
return ['err'=>1, 'msg'=>'標(biāo)題不存在'];
}
$this->title = rq('title');
//判斷描述是否傳入（可選）
if(rq('description')){
$this->description = rq('description');
}
return $this->save() ? ['err'=>0, 'msg'=>'添加成功','id'=>$this->id] : ['err'=>1,'添加失敗'];
}
遺留問(wèn)題
接口中字段的驗(yàn)證，需使用 validator 完善。
4.2 修改問(wèn)題API
建立路由
Route::any('/api/question/edit',function(){
return questionInstance()->edit();
});
實(shí)現(xiàn)方法
/*修改問(wèn)題API*/
public function edit()
{
//判斷用戶(hù)是否登錄
if(!userInstance()->isLogin()){
return ['err'=>1, 'msg'=>'請(qǐng)先登錄'];
}
//判斷必填字段
if(!rq('id')){
return ['err'=>1, 'msg'=>'參數(shù)錯(cuò)誤'];
}
//獲取記錄
$question = $this->find(rq('id'));
//若數(shù)據(jù)庫(kù)不存在問(wèn)題記錄
if(!$question){
return ['err'=>1, 'msg'=>'暫無(wú)數(shù)據(jù)'];
}
//問(wèn)題僅限創(chuàng)建者可修改
if($question->user_id != session('user.id')){
return ['err'=>1, 'msg'=>'權(quán)限不足'];
}
//獲取用戶(hù)修改的數(shù)據(jù)
if(rq('title')){
$question->title = rq('title');
}
if(rq('desc')){
$question->desc = rq('desc');
}
return $question->save() ? ['err'=>0] : ['err'=>1,'msg'=>'修改失敗'];
}
實(shí)現(xiàn)版本2
// API 編輯問(wèn)題
public function edit()
{
//判斷用戶(hù)是否登錄
if(!user()->islogin()){
return ['err'=>1, 'msg'=>'尚未登錄'];
}
//判斷問(wèn)題編號(hào)
if(!rq('id')){
return ['err'=>1,'msg'=>'參數(shù)錯(cuò)誤'];
}
//僅發(fā)布人可修改
$question = $this->find(rq('id'));
if(!$question){
return ['err'=>1,'msg'=>'問(wèn)題不存在'];
}
//僅發(fā)布人可修改
if($question['user_id'] != session('user_id')){
return ['err'=>1, 'msg'=>'僅發(fā)布人可編輯'];
}
//獲取更新字段
if(rq('title')){
$question->title = rq('title');
}
if(rq('description')){
$question->description = rq('description');
}
//數(shù)據(jù)庫(kù)更新
return $question->save()?['err'=>0,'msg'=>'編輯成功']:['err'=>1,'編輯失敗'];
}
4.3 查看問(wèn)題API
建立路由
Route::any('/api/question/read',function(){
return questionInstance()->read();
});
實(shí)現(xiàn)方法
/*查看問(wèn)題API*/
public function read()
{
if(rq('id')){
$data = $this->find(rq('id'),['id','title','desc']);
return ['err'=>0, 'data'=>$data];
}
//默認(rèn)分頁(yè)查詢(xún)
$limit = rq('limit')?:15;//每頁(yè)顯示條數(shù)
$skip = (rq('page')?:0)*$limit;//頁(yè)碼從0開(kāi)始
$data = $this->orderBy('created_at')->limit($limit)->skip($skip)->get(['id','title','desc'])->keyBy('id');
return ['err'=>0, 'data'=>$data];
}
實(shí)現(xiàn)版本2
//API 查看問(wèn)題
public function read()
{
//指定ID查詢(xún)
if(rq('id')){
return ['err'=>0,'data'=>$this->find(rq('id'))];
}
//默認(rèn)查看多條
$limit = rq('limit') ? : 3;// 每頁(yè)條數(shù)
$skip = (rq('page')?rq('page')-1:0) * $limit;// 頁(yè)碼求位移
$field = ['id','title','description','created_at'];
$data = $this->orderBy('created_at')->limit($limit)->skip($skip)->get($field)->keyBy('id');
//返回 collection 數(shù)據(jù)
return $data ? ['err'=>0, 'data'=>$data] : ['err'=>1,'暫無(wú)數(shù)據(jù)'];
}
4.4 刪除問(wèn)題API
建立路由
Route::any('/api/question/remove',function(){
return questionInstance()->remove();
});
實(shí)現(xiàn)方法
/*刪除問(wèn)題API*/
public function remove()
{
//判斷用戶(hù)是否登錄
if(!userInstance()->isLogin()){
return ['err'=>1, 'msg'=>'請(qǐng)先登錄'];
}
//判斷必填字段
if(!rq('id')){
return ['err'=>1, 'msg'=>'參數(shù)錯(cuò)誤'];
}
//判斷問(wèn)題是否存在
$question = $this->find(rq('id'));
if(!$question){
return ['err'=>1, 'msg'=>'暫無(wú)數(shù)據(jù)'];
}
//僅有問(wèn)題所有者才有權(quán)限刪除
if($question->user_id != session('user.id')){
return ['err'=>1, 'msg'=>'權(quán)限不足'];
}
//刪除記錄
return $question->delete() ? ['err'=>0] : ['err'=>1,'msg'=>'刪除失敗'];
}
實(shí)現(xiàn)版本2
//API 刪除問(wèn)題
public function del()
{
//判斷用戶(hù)是否登錄
if(!user()->islogin()){
return ['err'=>1, 'msg'=>'尚未登錄'];
}
//判斷問(wèn)題編號(hào)是否存在
if(!rq('id')){
return ['err'=>1, 'msg'=>'參數(shù)錯(cuò)誤'];
}
//判斷問(wèn)題是否存在
$question = $this->find(rq('id'));
if(!$question){
return ['err'=>1, 'msg'=>'數(shù)據(jù)不存在'];
}
//發(fā)布者可刪除，審核人可刪除（未實(shí)現(xiàn)）
if(session('user_id') != $question['user_id']){
return ['err'=>1, 'msg'=>'權(quán)限不足'];
}
//返回?cái)?shù)據(jù)
return $question->delete() ? ['err'=>0,'msg'=>'刪除成功'] : ['err'=>1,'msg'=>'刪除失敗'];
}
回答API
- 創(chuàng)建回答Migration
- 創(chuàng)建回答表結(jié)構(gòu)
- 創(chuàng)建回答表模型
- 實(shí)現(xiàn)回答表操作
1. 創(chuàng)建回答Migration
php artisan make:migration create-table-answers --create=answers
# Lavarel 5.4
php artisan make:migration create_answers_table --create=answers
2. 創(chuàng)建回答表結(jié)構(gòu)
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTableAnswers extends Migration
{
public function up()
{
Schema::create('answers', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
//自定義字段
$table->text('content')->comment('回答內(nèi)容');
$table->unsignedInteger('user_id')->comment('回答用戶(hù)');
$table->unsignedInteger('question_id')->comment('問(wèn)題編號(hào)');
//外鍵連接
$table->foreign('user_id')->references('id')->on('users');
$table->foreign('question_id')->references('id')->on('questions');
});
}
public function down()
{
Schema::dropIfExists('answers');
}
}
php artisan migrate --pretend
php artisan migrate
php artisan migrate:rollback
3. 創(chuàng)建回答表模型
php artisan make:model Model\Answer
MAC Laravel5.4
php artian make:model Models\\Answer
namespace App\Model;
use Illuminate\Database\Eloquent\Model;
class Answer extends Model
{
}
4. 實(shí)現(xiàn)回答表操作
路由文件中提取公共函數(shù)
function answerInstance(){
return new App\Model\Answer;
}
# 版本2
use App\Models\Answer;
function answer(){
return new Answer();
}
4.1 添加回答API
創(chuàng)建路由
Route::any('/api/answer/add',function(){
return answerInstance()->add();
});
實(shí)現(xiàn)方法
/*添加回答API*/
public function add()
{
//檢查用戶(hù)是否登錄
if(!userInstance()->isLogin()){
return ['err'=>1, 'msg'=>'請(qǐng)先登錄'];
}
//檢查問(wèn)題是否存在
if(!(rq('question_id') && rq('content'))){
return ['err'=>1, 'msg'=>'參數(shù)錯(cuò)誤'];
}
//檢查問(wèn)題是否存在
$question = questionInstance()->find(rq('question_id'));
if(!$question){
return ['err'=>1, 'msg'=>'問(wèn)題不存在'];
}
//相同問(wèn)題進(jìn)行回答一次
$count = $this->where(['question_id'=>rq('question_id'), 'user_id'=>session('user.id')])->count();
if($count){
return ['err'=>1, 'msg'=>'您已回答'];
}
//添加數(shù)據(jù)庫(kù)
$this->user_id = session('user.id');
$this->question_id = rq('question_id');
$this->content = rq('content');
return $this->save() ? ['err'=>0,'id'=>$this->id] : ['err'=>1,'msg'=>'添加失敗'];
}
# 版本2
/*API 添加回答*/
public function add()
{
//判斷用戶(hù)是否登錄
if(!user()->islogin()){
return ['err'=>1, 'msg'=>'尚未登錄'];
}
$this->user_id = session('user_id');
//判斷問(wèn)題和回答
if(!rq('question_id') || !rq('content')){
return ['err'=>1, 'msg'=>'缺少問(wèn)題編號(hào)和回答內(nèi)容'];
}
//判斷問(wèn)題是否存在
$question = question()->find(rq('question_id'));
if(!$question){
return ['err'=>1, 'msg'=>'問(wèn)題不存在'];
}
$this->question_id = $question->id;
//同一問(wèn)題僅作答一次
$count = $this->where(['question_id'=>rq('question_id'), 'user_id'=>session('user_id')])->count();
if($count){
return ['err'=>1, 'msg'=>'已回答過(guò)'];
}
//數(shù)據(jù)庫(kù)添加
$this->content=rq('content');
return $this->save() ? ['err'=>0,'msg'=>'回答成功','id'=>$this->id] : ['err'=>0,'msg'=>'回答失敗'];
}
4.2 更新回答API
創(chuàng)建路由
Route::any('/api/answer/edit',function(){
return answerInstance()->edit();
});
實(shí)現(xiàn)方法
/*更新回答API*/
public function edit()
{
//檢查用戶(hù)是否登錄
if(!userInstance()->isLogin()){
return ['err'=>1, 'msg'=>'請(qǐng)先登錄'];
}
//檢查答案是否存在
if(!(rq('id') && rq('content')) ){
return ['err'=>1, 'msg'=>'參數(shù)錯(cuò)誤'];
}
//檢查當(dāng)答案的擁有者是否為當(dāng)前用戶(hù)
$answer = $this->find(rq('id'));
if($answer->user_id != session('user.id')){
return ['err'=>1, 'msg'=>'權(quán)限不足'];
}
//更新答案
if(rq('content')){
$answer->content = rq('content');
}
return $answer->save() ? ['err'=>0] : ['err'=>1,'msg'=>'更新失敗'];
}
# 版本2
/*API 編輯回答*/
public function edit()
{
//判斷用戶(hù)是否登錄
if(!user()->islogin()){
return ['err'=>1, 'msg'=>'尚未登錄'];
}
//判斷回答編號(hào)是否存在
if(!rq('id') || !rq('content')){
return ['err'=>1, 'msg'=>'缺少回答編號(hào)和回答內(nèi)容'];
}
//判斷回答是否存在
$answer = answer()->find(rq('id'));
if(!$answer){
return ['err'=>1, 'msg'=>'回答不存在'];
}
//作答人可編輯
if($answer['user_id'] != session('user_id')){
return ['err'=>1, 'msg'=>'權(quán)限不足'];
}
//數(shù)據(jù)庫(kù)更新
$answer->content=rq('content');
return $answer->save() ? ['err'=>0,'msg'=>'編輯成功'] : ['err'=>0,'msg'=>'編輯失敗'];
}
4.3 查看回答API
創(chuàng)建路由
Route::any('/api/answer/read',function(){
return answerInstance()->read();
});
實(shí)現(xiàn)方法
/*查看回答API*/
public function read()
{
//當(dāng)任意一個(gè)參數(shù)不存在時(shí)均報(bào)錯(cuò)
if(!rq('id') && !rq('question_id')){
return ['err'=>1, 'msg'=>'參數(shù)錯(cuò)誤'];
}
//根據(jù)回答編號(hào)查看回答
if(rq('id')){
$data = $this->find(rq('id'));
if(!$data){
return ['err'=>1, 'msg'=>'暫無(wú)數(shù)據(jù)'];
}
return ['err'=>0, 'data'=>$data];
}
//根據(jù)問(wèn)題編號(hào)查看回答
if(rq('question_id')){
$data = $this->where('question_id',rq('question_id'))->get(['id','content'])->keyBy('id');
if(!$data){
return ['err'=>1, 'msg'=>'暫無(wú)數(shù)據(jù)'];
}
return ['err'=>0, 'data'=>$data];
}
}
# 版本2
/*API 查看回答*/
public function read()
{
if(!rq('id') && !rq('question_id')){
return ['err'=>1, 'msg'=>'缺少回答編號(hào)或問(wèn)題編號(hào)'];
}
//查看單條回答
if(rq('id')){
$answer = $this->find(rq('id'));
if(!$answer){
return ['err'=>1,'msg'=>'回答不存在'];
}
return ['err'=>0,'data'=>$answer];
}
//判斷問(wèn)題是否存在
$question = question()->find(rq('question_id'));
if(!$question){
return ['err'=>1, 'msg'=>'問(wèn)題不存在'];
}
//查看指定問(wèn)題下的回答
$answers = $this->where('question_id',rq('question_id'))->get()->keyBy('id');
return ['err'=>0,'data'=>$answers];
}
評(píng)論API
- 創(chuàng)建評(píng)論Migration
- 創(chuàng)建評(píng)論表結(jié)構(gòu)
- 創(chuàng)建評(píng)論表模型
- 實(shí)現(xiàn)評(píng)論API
- 添加評(píng)論API
- 查看評(píng)論API
- 刪除評(píng)論API
1. 創(chuàng)建評(píng)論Migration
php artisan make:migration create_table_comments --create=comments
2. 創(chuàng)建評(píng)論表結(jié)構(gòu)
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTableComments extends Migration
{
public function up()
{
Schema::create('comments', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
//自定義字段
$table->text('content')->comment('評(píng)論內(nèi)容');
$table->unsignedInteger('user_id')->comment('評(píng)論人');
$table->unsignedInteger('question_id')->nullable()->comment('問(wèn)題編號(hào)');//可為空，由于無(wú)法確認(rèn)是針對(duì)問(wèn)題評(píng)論
$table->unsignedInteger('answer_id')->nullable()->comment('回答編號(hào)');//可為空，由于無(wú)法確認(rèn)是針對(duì)回答評(píng)論
$table->unsignedInteger('reply_to')->nullable()->comment('回復(fù)評(píng)論');
//外鍵關(guān)系
$table->foreign('user_id')->references('id')->on('users');
$table->foreign('question_id')->references('id')->on('questions');
$table->foreign('answer_id')->references('id')->on('answers');
$table->foreign('reply_to')->references('id')->on('comments');
});
}
public function down()
{
Schema::dropIfExists('comments');
}
}
版本2
public function up()
{
Schema::create('comments', function (Blueprint $table) {
$table->increments('id');
$table->text('content')->comment('內(nèi)容');
$table->unsignedInteger('user_id')->default('0')->comment('評(píng)論人');
$table->foreign('user_id')->references('id')->on('users');
$table->unsignedInteger('question_id')->nullable()->comment('問(wèn)題');
$table->foreign('question_id')->references('id')->on('questions');
$table->unsignedInteger('answer_id')->nullable()->comment('回答');
$table->foreign('answer_id')->references('id')->on('answers');
$table->unsignedInteger('reply_id')->nullable()->comment('回復(fù)評(píng)論');
$table->foreign('reply_id')->references('id')->on('comments');
$table->timestamps();
});
}
注意：reply_id 是針對(duì)評(píng)論而發(fā)表的評(píng)論，因此外鍵連接的位置仍然是 comments 表。
php artisan migrate --pretend
php artisan migrate:rollback
php artisan migrate
3. 創(chuàng)建評(píng)論表模型
php artisan make:model Model\Comment
namespace App\Model;
use Illuminate\Database\Eloquent\Model;
class Comment extends Model
{
}
4.實(shí)現(xiàn)評(píng)論API
路由文件中提取公共函數(shù)
function commentInstance(){
return new App\Model\Comment;
}
4.1 添加評(píng)論API
創(chuàng)建路由
Route::any('/api/comment/add',function(){
return commentInstance()->add();
});
實(shí)現(xiàn)方法
/*添加評(píng)論API*/
public function add()
{
//檢查用戶(hù)是否登錄
if(!userInstance()->isLogin()){
return ['err'=>1, 'msg'=>'請(qǐng)先登錄'];
}
//判斷是否具有評(píng)論內(nèi)容
if(!rq('content')){
return ['err'=>1, 'msg'=>'請(qǐng)先評(píng)論'];
}
//判斷是對(duì)問(wèn)題評(píng)論或是對(duì)回答評(píng)論
if(!rq('question_id') && !rq('answer_id')){
return ['err'=>1, 'msg'=>'請(qǐng)針對(duì)問(wèn)題或回答進(jìn)行評(píng)論'];
}
//判斷是否同時(shí)對(duì)問(wèn)題或答案評(píng)論
if(rq('questioin_id') && rq('answer_id')){
return ['err'=>1, 'msg'=>'請(qǐng)勿針對(duì)問(wèn)題和答案同時(shí)評(píng)論'];
}
//若針對(duì)問(wèn)題評(píng)論
if(rq('question_id')){
//判斷問(wèn)題是否存在
$question = questionInstance()->find(rq('question_id'));
if(!$question){
return ['err'=>1, 'msg'=>'問(wèn)題不存在'];
}
$this->question_id = rq('question_id');
}
//若針對(duì)回答評(píng)論
if(rq('answer_id')){
//判斷回答是否存在
$answer = answerInstance()->find(rq('answer_id'));
if(!$answer){
return ['err'=>1, 'msg'=>'回答不存在'];
}
$this->answer_id = rq('answer_id');
}
//判斷是否對(duì)自身評(píng)論進(jìn)行回復(fù)
if(rq('reply_to')){
$target = $this->find(rq('reply_to'));
if(!$target){
return ['err'=>1, 'msg'=>'目錄評(píng)論不存在'];
}
//禁止自己回復(fù)自己
if($target->user_id == session('user.id')){
return ['err'=>1, 'msg'=>'禁止回復(fù)自身評(píng)論'];
}
}
$this->content = rq('content');
$this->user_id = session('user.id');
$this->reply_to = rq('reply_to');
//插入數(shù)據(jù)庫(kù)并返回結(jié)果
return $this->save() ? ['err'=>0,'id'=>$this->id] : ['err'=>1, 'msg'=>'新增失敗'];
}
# 版本2
/*API 添加評(píng)論*/
public function add()
{
//判斷用戶(hù)是否登錄
if(!user()->islogin()){
return ['err'=>1, 'msg'=>'尚未登錄'];
}
$this->user_id = session('user_id');
//判斷評(píng)論內(nèi)容
if(!rq('content')){
return ['err'=>1, 'msg'=>'缺少評(píng)論內(nèi)容'];
}
$this->content = rq('content');
//針對(duì)問(wèn)題或回答評(píng)論
if(!rq('question_id') && !rq('answer_id')){
return ['err'=>1, 'msg'=>'缺少問(wèn)題編號(hào)或回答編號(hào)'];
}
if(rq('question_id') && rq('answer_id')){
return ['err'=>1, 'msg'=>'禁止同時(shí)評(píng)論問(wèn)題和回答'];
}
//針對(duì)問(wèn)題評(píng)論
if(rq('question_id')){
//判斷問(wèn)題是否存在
$question = question()->find(rq('question_id'));
if(!$question){
return ['err'=>1,'msg'=>'問(wèn)題不存在'];
}
$this->question_id = rq('question_id');
}
//針對(duì)回答評(píng)論
if(rq('answer_id')){
//判斷回答是否存在
$answer = answer()->find(rq('answer_id'));
if(!$answer){
return ['err'=>1, 'msg'=>'回答不存在'];
}
$this->answer_id = rq('answer_id');
}
//針對(duì)評(píng)論而評(píng)論
if(rq('reply_id')){
//判斷評(píng)論是否存在
$comment = $this->find(rq('reply_id'));
if(!$comment){
return ['err'=>1, 'msg'=>'評(píng)論不存在'];
}
//禁止對(duì)自己評(píng)論進(jìn)行評(píng)論
if($comment->user_id == session('user_id')){
return ['err'=>1, 'msg'=>'禁止自身評(píng)論'];
}
$this->reply_id = rq('reply_id');
}
//保存數(shù)據(jù)
return $this->save()?['err'=>0,'msg'=>'評(píng)論成功','id'=>$this->id]:['err'=>1,'msg'=>'評(píng)論失敗'];
}
4.2 查看評(píng)論API
創(chuàng)建路由
Route::any('/api/comment/read',function(){
return commentInstance()->read();
});
實(shí)現(xiàn)方法
public function read()
{
//判斷參數(shù)是否存在
if(!rq('question_id') && !rq('answer_id')){
return ['err'=>1, 'msg'=>'參數(shù)錯(cuò)誤'];
}
//根據(jù)問(wèn)題查看評(píng)論
if(rq('question_id')){
//判斷問(wèn)題是否存在
$question = questionInstance()->find(rq('question_id'));
if(!$question){
return ['err'=>1, 'msg'=>'問(wèn)題不存在'];
}
//獲取問(wèn)題下所有評(píng)論
$data = $this->where('question_id',rq('question_id'))->get();
}
//根據(jù)回答查看評(píng)論
if(rq('answer_id')){
//判斷回答是否存在
$answer = answerInstance()->find(rq('answer_id'));
if(!$answer){
return ['err'=>1, 'msg'=>'回答不存在'];
}
//獲取回答下所有評(píng)論
$data = $this->where('answer_id',rq('answer_id'))->get();
}
return $data->isEmpty() ? ['err'=>1, 'msg'=>'暫無(wú)評(píng)論'] : ['err'=>0,'data'=>$data->keyBy('id')];
}
# 版本2
/*API 查看評(píng)論*/
public function read()
{
//判斷參數(shù)
if(!rq('question_id') && !rq('answer_id')){
return ['err'=>1, 'msg'=>'缺少問(wèn)題編號(hào)或回答編號(hào)'];
}
//查看問(wèn)題的評(píng)論
if(rq('question_id')){
//判斷問(wèn)題是否存在
$question = question()->find(rq('question_id'));
if(!$question){
return ['err'=>1, 'msg'=>'問(wèn)題不存在'];
}
//獲取問(wèn)題下所有評(píng)論
$comment = $this->where('question_id',rq('question_id'));
}
//查看回答下的評(píng)論
if(rq('answer_id')){
//判斷回答是否存在
$answer = answer()->find(rq('answer_id'));
if(!$answer){
return ['err'=>1, 'msg'=>'回答不存在'];
}
//獲取回答下所有評(píng)論
$comment = $this->where('answer_id', rq('answer_id'));
}
$data = $comment->get()->keyBy('id');
//返回評(píng)論
return $data->isEmpty() ? ['err'=>1, 'msg'=>'暫無(wú)評(píng)論'] : ['err'=>0, 'data'=>$data];
}
4.3 刪除評(píng)論API
創(chuàng)建路由
Route::any('/api/comment/remove',function(){
return commentInstance()->remove();
});
實(shí)現(xiàn)方法
/*刪除評(píng)論API*/
public function remove()
{
//檢查用戶(hù)是否登錄
if(!userInstance()->isLogin()){
return ['err'=>1, 'msg'=>'請(qǐng)先登錄'];
}
//根據(jù)評(píng)論編號(hào)刪除
if(!rq('id')){
return ['err'=>1, 'msg'=>'參數(shù)錯(cuò)誤'];
}
//判斷評(píng)論是否存在
$comment = $this->find(rq('id'));
if(!$comment){
return ['err'=>1, 'msg'=>'評(píng)論不存在'];
}
//判斷是否為本人評(píng)論
if($comment->user_id != session('user.id')){
return ['err'=>1, 'msg'=>'權(quán)限錯(cuò)誤'];
}
//刪除所有回復(fù)過(guò)評(píng)論的評(píng)論
$this->where('reply_to',rq('id'))->delete();
//刪除評(píng)論
return $comment->delete() ? ['err'=>0, 'msg'=>'刪除成功'] : ['err'=>1, 'msg'=>'刪除失敗'];
}
# 版本2
/*API 刪除評(píng)論*/
public function del()
{
//判斷用戶(hù)是否登錄
if(!user()->islogin()){
return ['err'=>1, 'msg'=>'尚未登錄'];
}
//判斷評(píng)論編號(hào)
if(!rq('id')){
return ['err'=>1, 'msg'=>'缺少評(píng)論編號(hào)'];
}
//判斷評(píng)論是否存在
$comment = $this->find(rq('id'));
if(!$comment){
return ['err'=>1, 'msg'=>'評(píng)論不存在'];
}
//自身可刪除
if($comment->user_id != session('user_id')){
return ['err'=>1, 'msg'=>'權(quán)限不足'];
}
//刪除評(píng)論下的回復(fù)
$this->where('reply_id',rq('id'))->delete();
//刪除評(píng)論
return $comment->delete()?['err'=>0,'msg'=>'刪除成功']:['err'=>1,'msg'=>'刪除失敗'];
}
頂踩投票API
簡(jiǎn)介
當(dāng)對(duì)某用戶(hù)的回答做點(diǎn)贊或點(diǎn)差時(shí)，此時(shí)用戶(hù)、回答兩個(gè)實(shí)體之間存在多對(duì)多的關(guān)聯(lián)關(guān)系，而用戶(hù)、回答、投票三者組合必須是唯一的，即用戶(hù)不能為回答投多票。而類(lèi)似功能均可作為通用API來(lái)實(shí)現(xiàn)。
通用API應(yīng)用場(chǎng)景
- 當(dāng)涉及到多表查詢(xún)時(shí)需用通用API
- 零碎功能點(diǎn)所需要的API
1.創(chuàng)建多對(duì)多關(guān)聯(lián)關(guān)系
回答表和用戶(hù)表之間隸屬于多對(duì)多的關(guān)聯(lián)關(guān)系，laravel中稱(chēng)這種中間表為軸心表。
（1）在模型中創(chuàng)建多對(duì)多的關(guān)聯(lián)關(guān)系
app\Model\Answer.php
/*答案和用戶(hù)之間隸屬于多對(duì)多的關(guān)聯(lián)關(guān)系*/
public function users()
{
//默認(rèn)中間表僅保存關(guān)聯(lián)字段，新增字段需使用withPivot注明
return $this
->belongsToMany('App\Model\User')
->withPivot('vote') //執(zhí)行自定義新增字段(軸心)
->withTimestamps();//同時(shí)更新時(shí)間
}
app\Model\User.php
/*答案和用戶(hù)之間隸屬于多對(duì)多的關(guān)聯(lián)關(guān)系*/
public function answers()
{
//默認(rèn)中間表僅保存關(guān)聯(lián)字段，新增字段需使用withPivot注明
return $this
->belongsToMany('App\Model\Answer')
->withPivot('vote') //執(zhí)行自定義新增字段
->withTimestamps();//同時(shí)更新時(shí)間
}
（2）創(chuàng)建多對(duì)多中間表
php artisan make:migration create_table_answer_user --create=answer_user
注意：中間表表名不使用復(fù)數(shù)形式，必須使用單數(shù)。
（3）創(chuàng)建中間關(guān)聯(lián)表結(jié)構(gòu)
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTableAnswerUser extends Migration
{
public function up()
{
Schema::create('answer_user', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
//自定義字段
$table->unsignedInteger('user_id');//用戶(hù)表主鍵
$table->unsignedInteger('answer_id');//回答表主鍵
$table->unsignedSmallInteger('vote');//票數(shù)
//外鍵
$table->foreign('user_id')->references('id')->on('user');
$table->foreign('answer_id')->references('id')->on('answer');
//唯一鍵設(shè)置
$table->unique(['user_id','answer_id','vote']);
});
}
public function down()
{
Schema::dropIfExists('answer_user');
}
}
# 版本2
Schema::create('answer_user', function (Blueprint $table) {
$table->increments('id');
$table->unsignedInteger('user_id')->default('0')->comment('用戶(hù)');
$table->foreign('user_id')->references('id')->on('users');
$table->unsignedInteger('answer_id')->default('0')->comment('回答');
$table->foreign('answer_id')->references('id')->on('answers');
$table->unsignedTinyInteger('vote')->default('0')->comment('投票:0踩1贊');
$table->unique(['user_id','answer_id','vote']);//一個(gè)用戶(hù)只能對(duì)一個(gè)問(wèn)題投一次票（頂或踩）
$table->timestamps();
});
（4）生成表結(jié)構(gòu)
php artisan migrate:pretend
php artisan migrate
php artisan migrate:rollback
2. 為回答投票
（1）創(chuàng)建路由 routes\web.php
Route::any('/api/answer/vote',function(){
return answerInstance()->vote();
});
（2）實(shí)現(xiàn)方法 app\Model\Answer.php
/*使用中間表為問(wèn)題投票*/
public function vote()
{
//檢查用戶(hù)是否登錄
if(!userInstance()->isLogin()){
return ['err'=>1, 'msg'=>'請(qǐng)先登錄'];
}
//判斷必填參數(shù)
if(!rq('id') || !rq('vote')){
return ['err'=>1, 'msg'=>'參數(shù)錯(cuò)誤'];
}
//檢查回答是否存在
$answer = $this->find(rq('id'));
if(!$answer){
return ['err'=>1, 'msg'=>'回答不存在'];
}
//檢查當(dāng)前用戶(hù)是否在回答上重復(fù)投票,若中間表已存在數(shù)據(jù)則先存在后添加
$answer->users()->newPivotStatement()
->where('user_id',session('user.id'))
->where('answer_id',rq('id'))
->delete();
//向中間表添加
$vote = rq('vote')<=1 ? 1 : 2;//1贊同 2反對(duì)
$answer->users()->attach(session('user.id'),['vote'=>$vote]);
return ['err'=>0];
}
# 版本2
/*API 用戶(hù)投票*/
public function vote()
{
//判斷用戶(hù)是否登錄
if(!user()->islogin()){
return ['err'=>1, 'msg'=>'尚未登錄'];
}
//判斷回答編號(hào)和投票是否存在
if(!rq('id')){
return ['err'=>1, 'msg'=>'缺少回答編號(hào)'];
}
if(!Request::has('vote')){
return ['err'=>1, 'msg'=>'缺少投票'];
}
//判斷投票類(lèi)型
$vote = rq('vote') ? 1 : 0;//1頂0踩
//判斷問(wèn)題是否存在
$answer = $this->find(rq('id'));
if(!$answer){
return ['err'=>1, 'msg'=>'回答不存在'];
}
//判斷用戶(hù)在相同回答下是否已投票，注意 answer_user 中 answer_id+user_id+vote 必須唯一，即一個(gè)用戶(hù)對(duì)一個(gè)問(wèn)題只能投一票。
//獲取中間表
$answer_user = $answer->user()->newPivotStatement();
//刪除投票記錄
$answer_user->where(['answer_id'=>rq('id'), 'user_id'=>session('user_id')])->delete();
//添加投票
$answer->user()->attach(session('user_id'), ['vote'=>$vote]);
return ['err'=>0,'msg'=>'投票成功'];
}
時(shí)間線(xiàn)API
簡(jiǎn)介
首頁(yè)需要獲取時(shí)間線(xiàn)的數(shù)據(jù)，時(shí)間線(xiàn)的數(shù)據(jù)是問(wèn)題和回答的組合，作為一個(gè)綜合模型調(diào)用的功能，可作為通用API。
創(chuàng)建路由
由于時(shí)間線(xiàn)調(diào)用多張表的數(shù)據(jù)，因此將其放置在單獨(dú)的控制器中實(shí)現(xiàn)。
Route::any('/api/timeline','CommonController@timeline');
路由文件中提取公共分頁(yè)函數(shù)
function paginate($page=1,$limit=15){
$page = $page?$page-1:0;
$limit = $limit?:15;
return [$limit, $page*$limit];
}
創(chuàng)建控制器
php artisan make:controller CommonController
實(shí)現(xiàn)接口
/*獲取時(shí)間線(xiàn)API*/
public function timeline()
{
//獲取頁(yè)碼和每頁(yè)顯示條數(shù)
list($limit, $skip)=paginate(rq('page'),rq('limit'));
//獲取所有問(wèn)題與答案
$questions = questionInstance()->limit($limit)->skip($skip)->orderBy('created_at','desc')->get();
$answers = answerInstance()->limit($limit)->skip($skip)->orderBy('created_at','desc')->get();
//將問(wèn)題和答案合并
$data = $questions->merge($answers);//merge()合并集合存在bug
//根據(jù)創(chuàng)建時(shí)間排序
$data = $data->sortBy(function($item){
return $item->created_at;
});
return $data->values()->all();
}
# 版本2
/*API 時(shí)間線(xiàn)*/
public function timeline()
{
list($limit,$skip) = pager(rq('page'), rq('limit'));
//獲取所有問(wèn)題和回答
$questions = question()->limit($limit)->skip($skip)->orderBy('created_at', 'DESC')->get();
$answers = answer()->limit($limit)->skip($skip)->orderBy('created_at', 'DESC')->get();
//合并數(shù)據(jù)后按時(shí)間降序排列
$data = $questions->merge($answers);
$data->sortByDesc(function($item){
return $item->created_at;
});
//去除默認(rèn)的 key
$data = $data->values()->all();
return $data;
}
遺留問(wèn)題
當(dāng)前時(shí)間線(xiàn)中糅合了問(wèn)題和回答，若種類(lèi)比較多時(shí)，此時(shí)為區(qū)分每條記錄所代表的類(lèi)型，需添加類(lèi)型字段以指明具體數(shù)據(jù)所屬模塊。
