表结构展示
项目表:projects.sql
CREATE TABLE `projects` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`thumbnail` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `projects_user_id_foreign` (`user_id`) USING BTREE,
CONSTRAINT `projects_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC
任务表:tasks.sql
CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '任务名称',
`completion` int(11) NOT NULL COMMENT '完成状态',
`project_id` int(10) unsigned NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `tasks_project_id_foreign` (`project_id`) USING BTREE,
CONSTRAINT `tasks_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC
步骤表:steps.sql
CREATE TABLE `steps` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`task_id` int(10) unsigned NOT NULL,
`completion` tinyint(1) NOT NULL DEFAULT '0',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `steps_task_id_foreign` (`task_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
模型关系
Project模型
use Illuminate\Database\Eloquent\Model;
class Project extends Model
{
protected $fillable = [
'name', 'thumbnail'
];
public function user()
{
return $this->belongsTo(User::class);
}
public function tasks()
{
return $this->hasMany(Task::class);
}
public function getThumbnailAttribute($value)
{
return $value ?? 'avatar.jpg';
}
}
Task模型
use Illuminate\Database\Eloquent\Model;
class Task extends Model
{
protected $fillable = [
'name',
'completion',
'project_id',
];
public function project()
{
return $this->belongsTo(Project::class);
}
public function steps()
{
return $this->hasMany(Step::class);
}
}
Step模型
use Illuminate\Database\Eloquent\Model;
class Step extends Model
{
protected $fillable = [
'name', 'completion', 'task_id'
];
protected $attributes = [
'completion' => false, // 有默认属性的字段 0
];
public function task()
{
return $this->belongsTo(Task::class);
}
}
User用户模型
use Illuminate\Notifications\Notifiable;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
class User extends Authenticatable
{
......
public function projects()
{
return $this->hasMany(Project::class);
}
public function tasks()
{
return $this->hasManyThrough(Task::class, Project::class);
}
}
……代表本来已经自动生成的部分的代码
路由配置
Route::get('tasks/charts', 'TasksController@charts')->name('tasks.charts');
当有资源路由存在的时候,这一条路由必须放在资源路由至少,否则会存在路由模糊造成找不到该路由!
助手函数配置
函数名称:helper.php
路径:app/helper.php
composer加载:
在这部分加上以下代码
"autoload": {
"psr-4": {
"App\\": "app/"
},
"classmap": [
"database/seeds",
"database/factories"
],
"files": [
"app/helper.php"
]
},
使用composer进行自动加载:
composer dumpautoload
助手函数:返回任务总数
function TasksCountArray($projects)
{
$counts = [];
foreach ($projects as $project) {
$perCount = $project->tasks->count();
array_push($counts, $perCount);
}
return $counts;
}
后台代码实现
<?php
namespace App\Http\Controllers;
use App\Http\Requests\CreateTask;
use App\Http\Requests\UpdateTask;
use App\Project;
use App\Repositories\TasksRepository;
use App\Step;
use App\Task;
class TasksController extends Controller
{
protected $repo;
public function __construct(TasksRepository $repo)
{
$this->repo = $repo;
$this->middleware('auth'); // 必须登录才能访问
}
// ...... 其他的方法
public function charts()
{
$total = $this->repo->total();
$toDoCount = $this->repo->todoCount();
$doneCount = $this->repo->doneCount();
$names = \DB::table('projects')->pluck('name');
$projects = Project::with('tasks')->get();
return view('tasks.charts', compact('total', 'toDoCount', 'doneCount', 'names', 'projects'));
}
}
TasksRepository
<?php
namespace App\Repositories;
use App\Task;
class TasksRepository
{
// ...... 其他方法
public function todoCount()
{
return auth()->user()->tasks()->where('completion', 0)->count();
}
public function doneCount()
{
return auth()->user()->tasks()->where('completion', 1)->count();
}
public function total()
{
return auth()->user()->tasks()->count();
}
}
这里只需要其中的三个方法获取总数,未完成的数量,已完成的数量
$names = \DB::table('projects')->pluck('name');
这里获取
name
字段的collection
返回结果:
Collection {#283 ▼
#items: array:2 [▼
0 => "团队任务"
1 => "私人任务"
]
}
$projects = Project::with('tasks')->get();
使用关联与载入的方式查询所有的项目和项目有关的任务
前端代码实现
文件存储目录:resources/views/tasks/charts.blade.php
@extends('layouts.app')
@section('content')
<div class="container">
<div class="row">
<div class="col-md-4">
<canvas id="pieChart" width="300" height="300"></canvas>
</div>
<div class="col-md-4">
<canvas id="barChart" width="300" height="300"></canvas>
</div>
</div>
</div>
@endsection
@section('customJS')
<script src="https://cdn.bootcss.com/Chart.js/2.9.3/Chart.min.js"></script>
<script>
$(document).ready(function () {
var ctxPie = $('#pieChart');
var data = {
labels: [
"未完成",
"已完成",
],
datasets: [
{
data: [{{ $toDoCount }}, {{ $doneCount }}],
backgroundColor: [
"#FF6384",
"#36A2EB",
],
hoverBackground: [
"#FF6384",
"#36A2EB",
]
}
]
};
var pieChart = new Chart(ctxPie, {
type: 'pie',
data: data,
options: {
responsive: true,
title: {
display: true,
text: '所有任务的完成比例(总数:{{ $total }})'
}
},
});
var ctxBar = $('#barChart');
var barData = {
labels: {!! $names !!},
datasets: [
{
backgroundColor: [
'rgba(255, 99, 132, 0.2)'
],
borderColor: [
'rgba(255, 99, 132, 1)'
],
borderWidth: 1,
data: {!! json_encode(TasksCountArray($projects)) !!},
}
]
};
var myBarChart = new Chart(ctxBar, {
type: 'bar',
data: barData,
options: {
responsive: true,
title: {
display: true,
text: '项目之间的任务总数对比'
},
legend: {
display: false
}
},
})
});
</script>
@endsection
效果截图: