rust:axum+sqlx实战学习笔记7

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第7天，点击查看活动详情

书接上回，一个简单的博客系统除了文章、评论，当然还需要一些分类和标签之类的方便对文章进行分类归纳。

那么，我们开始构建文章的分类和标签数据表。

照例还是先看文件目录：

首先明白分类和标签，在一篇文章中，其实的一对多的关系：即一个文章可以有多个分类或者标签。

那么在数据库中，我们需要三张数据表来存储数据，一个文章表、一个分类/标签表，一个文章—分类关系表/文章-标签关系表。

一、文章分类

首先看api.rs中的路由：

use super::handlers::{get_category, get_category_count};
use axum::{routing::get, Router};
pub fn handler_category() -> Router {
    //构建注册路由
    Router::new()
        .route("/all", get(get_category))
        .route("/count", get(get_category_count))
}

这里我只是写了两个简单的路由，一个查询全部分类，另一个查询分类的总数。这里可能有同学会很奇怪，为什么没有基本的crud操作呢？因为分类的新增、删除逻辑，我还没有写完独立的crud接口（😓）。

接下来看handler函数的处理逻辑：

use super::dto::CategoryList;
use super::servers;
use crate::common::{response::RespVO, types::TotalResponse};
use axum::{http::StatusCode, response::IntoResponse, Json};

//查询文章列表
pub async fn get_category() -> impl IntoResponse {
    let result = servers::all().await;
    match result {
        Ok(res) => Json(RespVO::<Vec<CategoryList>>::from_result(&res)),
        Err(err) => {
            tracing::error!("get_articles_lists: {:?}", err);
            let info = err.to_string();
            let code = StatusCode::NOT_FOUND;
            Json(RespVO::<Vec<CategoryList>>::from_error_info(code, &info))
        }
    }
}

//查询文章列表
pub async fn get_category_count() -> impl IntoResponse {
    let result = servers::count().await;
    match result {
        Ok(res) => Json(RespVO::<TotalResponse>::from_result(&res)),
        Err(err) => {
            tracing::error!("get_category_count: {:?}", err);
            let info = err.to_string();
            let code = StatusCode::NOT_FOUND;
            Json(RespVO::<TotalResponse>::from_error_info(code, &info))
        }
    }
}

然后是servers.rs进行数据库操作：

use super::dto::{ArticleCategoryList, CategoryList};
use crate::common::types::{RequestId, TotalResponse};
use crate::db;
use sqlx::{self, Error};
/**
 * 测试接口: 查全部分类
 */
pub async fn all() -> Result<Vec<CategoryList>, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT c.*, COUNT(*) AS category_count FROM category c
 LEFT JOIN article_category a_c ON c.id = a_c.category_id 
 LEFT JOIN article a ON a.id = a_c.article_id
 GROUP BY c.id;";
    let list = sqlx::query_as::<_, CategoryList>(sql)
        .fetch_all(pool)
        .await?;
    Ok(list)
}

/**
 * 测试接口: 查分类总数
 */
pub async fn count() -> Result<TotalResponse, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT COUNT(*) AS total FROM category;";
    let list = sqlx::query_as::<_, TotalResponse>(sql)
        .fetch_one(pool)
        .await?;
    Ok(list)
}

/**
 * 测试接口: 新增分类
 */
pub async fn create(name: String) -> Result<u32, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "INSERT ignore into category (category_name) values (?)";
    let rows_affected = sqlx::query(sql)
        .bind(name)
        .execute(pool)
        .await?
        .rows_affected();
    if rows_affected == 1 {
        let sql = "select max(id) as id from category;";
        let res = sqlx::query_as::<_, RequestId>(sql).fetch_one(pool).await?;
        Ok(res.id)
    } else {
        Ok(0)
    }
}

/**
 * 测试接口: 新增文章-分类关系
 */
pub async fn create_article_category(article_id: &u32, category_id: &u32) -> Result<u64, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "INSERT INTO article_category (article_id, category_id) VALUES (?, ?)";
    let rows_affected = sqlx::query(sql)
        .bind(article_id)
        .bind(category_id)
        .execute(pool)
        .await?
        .rows_affected();
    Ok(rows_affected)
}

/**
 * 测试接口: 通过名称查询分类
 */
pub async fn get_cate_by_name(name: String) -> Result<Vec<CategoryList>, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT * FROM category WHERE category_name = ? ;";
    let list = sqlx::query_as::<_, CategoryList>(sql)
        .bind(name)
        .fetch_all(pool)
        .await?;
    Ok(list)
}

/**
 * 测试接口: 通过文章id查询分类
 */
pub async fn get_cate_by_id(article_id: &u32) -> Result<Vec<ArticleCategoryList>, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT * FROM article_category WHERE article_id = ? and deleted = 0;";
    let list = sqlx::query_as::<_, ArticleCategoryList>(sql)
        .bind(article_id)
        .fetch_all(pool)
        .await?;
    Ok(list)
}

/**
 * 测试接口: 通过分类id查询已删除分类
 */
pub async fn get_by_cate_id(category_id: &u32) -> Result<Option<ArticleCategoryList>, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT * FROM article_category WHERE deleted = 1  and category_id = ?;";
    let list = sqlx::query_as::<_, ArticleCategoryList>(sql)
        .bind(category_id)
        .fetch_optional(pool)
        .await?;
    Ok(list)
}

/**
 * 测试接口: 更新文章-分类关系
 */
pub async fn update_article_category(category_id: &u32) -> Result<u64, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "update article_category set deleted = 0 WHERE category_id = ?;";
    let pg_done = sqlx::query(sql)
        .bind(category_id)
        .execute(pool)
        .await?
        .rows_affected();
    Ok(pg_done)
}

/**
 * 测试接口: 删除文章-分类关系
 */
pub async fn delete_article_category(category_id: &u32) -> Result<u64, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "update article_category set deleted = 1 WHERE category_id = ?;";
    let pg_done = sqlx::query(sql)
        .bind(category_id)
        .execute(pool)
        .await?
        .rows_affected();
    Ok(pg_done)
}

当然，对于新增、删除分类的操作，还是写在servers.rs里面。

然后就是dto.rs所用到的数据结构：

use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::FromRow;

/// 分类列表模型
#[derive(Debug, Clone, Deserialize, Serialize, FromRow)]
pub struct CategoryList {
    pub id: u32,
    pub create_time: Option<DateTime<Utc>>,
    pub category_name: Option<String>,
    pub category_count: i64,
    pub poster: Option<String>,
}

/// 文章-分类关联表结构
#[derive(Debug, Clone, Deserialize, Serialize, FromRow)]
pub struct ArticleCategoryList {
    pub id: u32,
    pub create_time: DateTime<Utc>,
    pub category_id: u32,
    pub article_id: u32,
    pub deleted: bool,
}

最后，肯定要到在mod.rs里面进行导出：

mod api;
mod dto;
mod handlers;
mod servers;

pub use api::handler_category;
pub use servers::{
    create, create_article_category, delete_article_category, get_by_cate_id, get_cate_by_id,
    get_cate_by_name, update_article_category,
};

二、文章标签

看过前面分类的处理，那基本上标签也是大同小异了，毕竟这两个不能说一模一样吧，起码也是半斤八两。

所以连api.rs中定义的路由，几乎也是一样的(cv大法好！)：

use super::handlers::{get_tags, get_tags_count};
use axum::{routing::get, Router};

pub fn handler_tags() -> Router {
    //构建注册路由
    Router::new()
        .route("/all", get(get_tags))
        .route("/article_count", get(get_tags_count))
}

同样逻辑进行handler函数的处理：

use super::dto::{TagCount, TagList};
use super::servers;
use crate::common::response::RespVO;
use axum::{http::StatusCode, response::IntoResponse, Json};

//查询标签列表
pub async fn get_tags() -> impl IntoResponse {
    let result = servers::all().await;
    match result {
        Ok(res) => Json(RespVO::<Vec<TagList>>::from_result(&res)),
        Err(err) => {
            tracing::error!("get_tags: {:?}", err);
            let info = err.to_string();
            let code = StatusCode::NOT_FOUND;
            Json(RespVO::<Vec<TagList>>::from_error_info(code, &info))
        }
    }
}

//获取标签数量
pub async fn get_tags_count() -> impl IntoResponse {
    let result = servers::count().await;
    match result {
        Ok(res) => Json(RespVO::<Vec<TagCount>>::from_result(&res)),
        Err(err) => {
            tracing::error!("get_tags_count: {:?}", err);
            let info = err.to_string();
            let code = StatusCode::NOT_FOUND;
            Json(RespVO::<Vec<TagCount>>::from_error_info(code, &info))
        }
    }
}

接着在servers.rs进行数据库操作：

use super::dto::{ArticleTagList, OneTag, TagCount, TagList};
use crate::common::types::RequestId;
use crate::db;
use sqlx::{self, Error};

/**
 * 测试接口: 查全部标签列表
 */
pub async fn all() -> Result<Vec<TagList>, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT t.*, COUNT(*) AS tag_count FROM tag t
        LEFT JOIN article_tag a_t ON t.id = a_t.tag_id 
        LEFT JOIN article a ON a.id = a_t.article_id
        GROUP BY t.id;";
    let list = sqlx::query_as::<_, TagList>(sql).fetch_all(pool).await?;
    Ok(list)
}

/**
 * 测试接口: 查标签数量列表
 */
pub async fn count() -> Result<Vec<TagCount>, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT t.id, t.tag_name, COUNT(*) AS blog_count FROM tag t
        LEFT JOIN article_tag a_t ON t.id = a_t.tag_id
        GROUP BY t.id;";
    let list = sqlx::query_as::<_, TagCount>(sql).fetch_all(pool).await?;
    Ok(list)
}

/**
 * 测试接口: 通过标签名称查询标签
 */
pub async fn get_tag_by_name(name: String) -> Result<Vec<OneTag>, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT * FROM tag WHERE tag_name = ? ;";
    let list = sqlx::query_as::<_, OneTag>(sql)
        .bind(name)
        .fetch_all(pool)
        .await?;
    Ok(list)
}

/**
 * 测试接口: 新增标签
 */
pub async fn create(name: String) -> Result<u32, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "INSERT ignore into tag (tag_name) values (?)";
    let rows_affected = sqlx::query(sql)
        .bind(name)
        .execute(pool)
        .await?
        .rows_affected();
    if rows_affected == 1 {
        let sql = "select max(id) as id from tag;";
        let res = sqlx::query_as::<_, RequestId>(sql).fetch_one(pool).await?;
        Ok(res.id)
    } else {
        Ok(0)
    }
}

/**
 * 测试接口: 新增文章-标签关系
 */
pub async fn create_article_tag(article_id: &u32, tag_id: &u32) -> Result<u64, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "INSERT INTO article_tag (article_id, tag_id) VALUES (?, ?)";
    let rows_affected = sqlx::query(sql)
        .bind(article_id)
        .bind(tag_id)
        .execute(pool)
        .await?
        .rows_affected();
    Ok(rows_affected)
}

/**
 * 测试接口: 通过文章id查询分类
 */
pub async fn get_tag_by_id(article_id: &u32) -> Result<Vec<ArticleTagList>, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT * FROM article_tag WHERE article_id = ? and deleted = 0;";
    let list = sqlx::query_as::<_, ArticleTagList>(sql)
        .bind(article_id)
        .fetch_all(pool)
        .await?;
    Ok(list)
}

/**
 * 测试接口: 通过标签id查询已删除标签
 */
pub async fn get_by_tag_id(tag_id: &u32) -> Result<Option<ArticleTagList>, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "SELECT * FROM article_tag WHERE deleted = 1 and tag_id = ?;";
    let list = sqlx::query_as::<_, ArticleTagList>(sql)
        .bind(tag_id)
        .fetch_optional(pool)
        .await?;
    Ok(list)
}

/**
 * 测试接口: 更新文章-标签关系
 */
pub async fn update_article_tag(tag_id: &u32) -> Result<u64, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "update article_tag set deleted = 0 WHERE tag_id = ?;";
    let pg_done = sqlx::query(sql)
        .bind(tag_id)
        .execute(pool)
        .await?
        .rows_affected();
    Ok(pg_done)
}

/**
 * 测试接口: 删除文章-标签关系
 */
pub async fn delete_article_tag(tag_id: &u32) -> Result<u64, Error> {
    let pool = db::get_pool().unwrap();
    let sql = "update article_tag set deleted = 1 WHERE tag_id = ?;";
    let pg_done = sqlx::query(sql)
        .bind(tag_id)
        .execute(pool)
        .await?
        .rows_affected();
    Ok(pg_done)
}

在dto.rs中定义好结构体：

use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::FromRow;

/// 标签列表结构
#[derive(Debug, Clone, Deserialize, Serialize, FromRow)]
pub struct TagList {
    pub id: u32,
    pub create_time: Option<DateTime<Utc>>,
    pub tag_name: Option<String>,
    pub tag_count: i64,
}

/// 标签+使用数量结构
#[derive(Debug, Clone, Deserialize, Serialize, FromRow)]
pub struct TagCount {
    pub id: u32,
    pub tag_name: Option<String>,
    pub blog_count: i64,
}

/// 单个标签结构
#[derive(Debug, Clone, Deserialize, Serialize, FromRow)]
pub struct OneTag {
    pub id: u32,
    pub create_time: Option<DateTime<Utc>>,
    pub tag_name: Option<String>,
}

最后在mod.rs中导出：

mod api;
mod dto;
mod handlers;
mod servers;

pub use api::handler_tags;
pub use servers::{
    create, create_article_tag, delete_article_tag, get_by_tag_id, get_tag_by_id, get_tag_by_name,
    update_article_tag,
};

这样，文章的分类和标签体系就构建完成了。因为文章和分类/标签是一对多的关系，所以我们需要一个维护一个关系表，把分类/标签的id和文章id进行关联。