这是整理以前做过的项目想起来的，想整理一下一个社区网站的一些功能的设计方案的实现，这个开源项目是使用PHP Laravel框架 msyql作为数据库，不详细读源码，只是想记录一些功能的实现原理，其他语言设计通用，想到哪里写点东西记录一下。
只介绍一些基本功能设计不涉及特殊业务
(一)文章功能
image.png
一个社区问答的基本功能有话题、标签、作者和发帖组成
分类：
image.png
后台的基本维护操作
表结构：
DROP TABLE IF EXISTS `community_categories`;
CREATE TABLE `community_categories` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL DEFAULT 0,
`grade` int(11) NOT NULL DEFAULT 1,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`icon` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
`slug` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`type` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`sort` int(11) NOT NULL DEFAULT 0,
`role_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
`status` smallint(6) NOT NULL DEFAULT 1,
`created_at` timestamp(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
`must` smallint(2) NULL DEFAULT 0 COMMENT '0不是必须可以删除1不能删除',
`operation_by` int(10) NULL DEFAULT NULL COMMENT '操作人',
PRIMARY KEY (`id`, `name`) USING BTREE,
UNIQUE INDEX `categories_slug_unique`(`slug`) USING BTREE,
INDEX `categories_name_index`(`name`) USING BTREE,
INDEX `categories_created_at_index`(`created_at`) USING BTREE,
INDEX `categories_updated_at_index`(`updated_at`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
话题
image.png
表结构：
DROP TABLE IF EXISTS `community_tags`;
CREATE TABLE `community_tags` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`category_id` int(11) NOT NULL DEFAULT 0,
`logo` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`summary` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
`parent_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
`followers` int(10) UNSIGNED NOT NULL DEFAULT 0,
`created_at` timestamp(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `tags_name_unique`(`name`) USING BTREE,
INDEX `tags_parent_id_index`(`parent_id`) USING BTREE,
INDEX `tags_followers_index`(`followers`) USING BTREE,
INDEX `tags_category_id_index`(`category_id`) USING BTREE,
INDEX `tags_created_at_index`(`created_at`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1736 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
分类和话题属于一对多的关系通过category_id关联
public function tags()
{
return $this->hasMany('App\Models\Tag','category_id');
}
作者： 这里作者就是用户根据不公系统都有自己的用户管理设计这里就不多说了
image.png
文章：
image.png
数据库设计：
DROP TABLE IF EXISTS `community_articles`;
CREATE TABLE `community_articles` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(10) UNSIGNED NOT NULL,
`logo` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
`category_id` int(11) NOT NULL DEFAULT 0,
`title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`summary` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`views` int(10) UNSIGNED NOT NULL DEFAULT 0,
`collections` int(10) UNSIGNED NOT NULL DEFAULT 0,
`comments` int(10) UNSIGNED NOT NULL DEFAULT 0,
`supports` int(10) UNSIGNED NOT NULL DEFAULT 0,
`status` tinyint(4) NOT NULL DEFAULT 0,
`device` tinyint(4) NOT NULL DEFAULT 1,
`created_at` timestamp(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
`product_id` int(10) UNSIGNED NULL DEFAULT 0 COMMENT '产品id 不关联产品id的为0',
PRIMARY KEY (`id`) USING BTREE,
INDEX `articles_user_id_index`(`user_id`) USING BTREE,
INDEX `articles_category_id_index`(`category_id`) USING BTREE,
INDEX `articles_title_index`(`title`) USING BTREE,
INDEX `articles_views_index`(`views`) USING BTREE,
INDEX `articles_created_at_index`(`created_at`) USING BTREE,
INDEX `articles_updated_at_index`(`updated_at`) USING BTREE,
INDEX `articles_product_id_index`(`product_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 187 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
一篇文章会关联作者、分类信息
public function articles()
{
return $this->hasMany('App\Models\Article','category_id');
}
public function user()
{
return $this->belongsTo('App\Models\User');
}
还会有点赞、点击数、评论数等统计信息用户根据这些信息推荐热门、精品文章
/*推荐文章*/
public static function recommended($categoryId=0 , $pageSize=20)
{
$query = self::query();
if( $categoryId > 0 ){
$query->where('category_id','=',$categoryId);
}
$list = $query->where('status','>',0)->orderBy('supports','DESC')->orderBy('created_at','DESC')->paginate($pageSize);
return $list;
}
/*热门文章*/
public static function hottest($categoryId=0 , $pageSize=20)
{
$query = self::with('user');
if( $categoryId > 0 ){
$query->where('category_id','=',$categoryId);
}
$list = $query->where('status','>',0)->orderBy('views','DESC')->orderBy('collections','DESC')->orderBy('created_at','DESC')->paginate($pageSize);
return $list;
}
/*热门话题*/
public static function hotcomments($categoryId=0 , $pageSize=20)
{
$query = self::with('user');
if( $categoryId > 0 ){
$query->where('category_id','=',$categoryId);
}
$list = $query->where('status','>',0)->orderBy('comments','DESC')->orderBy('collections','DESC')->orderBy('created_at','DESC')->paginate($pageSize);
return $list;
}
看一下一个用户创建一篇文章的过程
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request, CaptchaService $captchaService)
{
$loginUser = $request->user();
if($request->user()->status === 0){
return $this->error(route('website.index'),'操作失败！您的邮箱还未验证，验证后才能进行该操作！');
}
/*防灌水检查*/
if( Setting()->get('article_limit_num') > 0 ){
$questionCount = $this->counter('article_num_'. $loginUser->id,1);
if( $questionCount > Setting()->get('article_limit_num')){
return $this->showErrorMsg(route('website.index'),'你已超过每小时文章发表限制数'.Setting()->get('article_limit_num').'，请稍后再进行该操作，如有疑问请联系管理员!');
}
}
$request->flash();
/*如果开启验证码则需要输入验证码*/
if( Setting()->get('code_create_article') ){
$captchaService->setValidateRules('code_create_article',$this->validateRules);
}
//$this->validate($request,$this->validateRules);
$validator = Validator::make($request->all(), $this->validateRules, $this->message);
if($validator->passes()) {
$data = [
'user_id' => $loginUser->id,
'category_id' => intval($request->input('category_id', 0)),
'title' => trim($request->input('title')),
'content' => clean($request->input('content')),
'summary' => $request->input('summary'),
'status' => 1,
'logo' => $request->input('logo') ?? 'http://inte-service.static.chanjet.com/avatars/000/00/00/20190821052916.jpg',
'product_id' => $request->input('product_id') ?? 0,
'created_at' => time(),
'updated_at' => time(),
];
$article = Article::create($data);
/*判断问题是否添加成功*/
if ($article) {
/*添加标签*/
$tagString = trim($request->input('tags'));
Tag::multiSave($tagString, $article);
//记录动态
$this->doing($article->user_id, 'create_article', get_class($article), $article->id, $article->title, $article->summery);
/*用户提问数+1*/
$loginUser->userData()->increment('articles');
UserTag::multiIncrement($loginUser->id, $article->tags()->get(), 'articles');
$this->credit($request->user()->id, 'create_article', Setting()->get('coins_write_article'), Setting()->get('credits_write_article'), $article->id, $article->title);
if ($article->status === 1) {
$message = '文章发布成功! ' . get_credit_message(Setting()->get('credits_write_article'), Setting()->get('coins_write_article'));
} else {
$message = '文章发布成功！为了确保文章的质量，我们会对您发布的文章进行审核。请耐心等待......';
}
$this->counter('article_num_' . $article->user_id, 1, 60);
return $this->ajaxSuccess($article->id);
}
return $this->ajaxError("文章发布失败，请稍后再试",2);
}
else
{
return $this->ajaxError($validator->messages());
}
}
网友评论