联合查询（Relation、Linked）
数据表：
stock_group：
| index | name |
|---|---|
| 0 | 全部 |
| 1 | 持有 |
stock_info:
| code | name | box | hold |
|---|---|---|---|
| 123456 | 芯片ETF | 0 | |
| 234567 | 测试ETF | 1 | |
| 345678 | 新能源 | 1 |
group_stock_relation：
| group_name | stock_code | index |
|---|---|---|
| 全部 | 123456 | 0 |
| 全部 | 234567 | 2 |
| 持有 | 123456 | 2 |
| 全部 | 345678 | 1 |
三个表关系为：stock_info存股票信息，stock_group存股票分组信息，group_stock_relation存分组和分组持有的股票信息及其索引。
1.Relation
relation用在两个表之间。
1.1 定义Relation
relation分为一对一、一对多（比如一个stock_group对多个group_stock_relation）、多对多。
stock_info与group_stock_relation是一对多，stock_group与group_stock_relation也是一对多，所以可以这样定义：
//stock_info.rs
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
#[sea_orm(has_many = "super::group_stock_relation::Entity")]
GroupStockRs,
}
impl Related<super::group_stock_relation::Entity> for Entity {
fn to() -> RelationDef {
Relation::GroupStockRs.def()
}
}
//stock_group.rs
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
#[sea_orm(has_many = "super::group_stock_relation::Entity")]
GroupStockRs,
}
impl Related<super::group_stock_relation::Entity> for Entity {
fn to() -> RelationDef {
Relation::GroupStockRs.def()
}
}
两个表关系是相互的，定义完一个表关系后要到另一个表定义反向关系。
定义group_stock_relation与stock_group、stock_info的反向关系：
//group_stock_relation.rs
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
#[sea_orm(
belongs_to = "super::stock_group::Entity",
from = "Column::GroupName",
to = "super::stock_group::Column::Name"
)]
StockGroups,
#[sea_orm(
belongs_to = "super::stock_info::Entity",
from = "Column::StockCode",
to = "super::stock_info::Column::Code"
)]
StockInfos,
}
impl Related<super::stock_group::Entity> for Entity {
fn to() -> RelationDef {
Relation::StockGroups.def()
}
}
impl Related<super::stock_info::Entity> for Entity {
fn to() -> RelationDef {
Relation::StockInfos.def()
}
}
1.2 使用Relation
比如我要查询某个股票在哪些分组里面，可以这样查：
///根据股票代码查询所有所在的分组（没有该股票和没有任何分组时返回None）
pub async fn find_groups_by_stock_code(stock_code: String) -> AppResult<Option<Vec<String>>> {
let db = crate::entities::DB
.get()
.ok_or(anyhow::anyhow!("数据库未初始化"))?;
let option = StockInfos::find_by_id(stock_code).one(db).await?;
match option {
None => {
return Ok(None);
}
Some(model) => {
let groups = model
.find_related(GroupStockRs)
.all(db)
.await?
.into_iter()
.map(|model| model.group_name)
.collect::<Vec<_>>();
return Ok(Some(groups));
}
}
//当然下面这种也可以
// let vec = GroupStockRs::find().filter(<group_stock_relation::Entity as sea_orm::EntityTrait>::Column::StockCode.eq(stock_code)).all(db).await?;
let vec = GroupStockRs::find()
.select_only()
.column(Column::GroupName)
.filter(Column::StockCode.eq(stock_code))
.into_tuple::<String>()
.all(db)
.await?;
println!("{:?}", vec);
// let result = GroupStockRs::find_by_stock_code(stock_code).one(db).await?;
Ok(None)
}
Relational Select - SeaORM Tutorials (sea-ql.org)
One to One | SeaORM 🐚 An async & dynamic ORM for Rust (sea-ql.org)
2.Linked
linked用在多个表之间。
A Linked is composed of a chain of relations, and is useful when:
- there exist multiple join paths between a pair of entities
- joining across multiple entities in a relational query
比如我有一个分组名，需要查出其中所有的股票：
注：当时的表不是这样设计的：当时group_stock_relation存的是分组id而不是name，stock_group的主键不是name而是一个id、stock_info不变。
当时的问题就是，拿着一个分组名需要获得所有的股票信息，即需要根据分组名在stock_group获得分组id→拿分组id去group_stock_relation查股票id→拿股票id去stock_info查股票信息。使用Linked可以很方便的解决。
pub struct GroupToStockInfo;
impl Linked for GroupToStockInfo {
type FromEntity = StockGroups;
type ToEntity = StockInfos;
fn link(&self) -> Vec<LinkDef> {
vec![
group_stock_relation::Relation::StockGroups.def().rev(),
group_stock_relation::Relation::StockInfos.def(),
]
}
}
///根据分组名称查询分组下的所有股票（按照索引排序）（但是这样没排）
pub async fn find_stocks_by_group_name(group_name: String) -> AppResult<i32> {
let db = crate::entities::DB
.get()
.ok_or(anyhow::anyhow!("数据库未初始化"))?;
let model = StockGroups::find()
.filter(stock_group::Column::Name.eq(group_name.clone()))
.one(db)
.await?
.ok_or(anyhow!("未找到{}分组", group_name))?;
let vec = model.find_linked(GroupToStockInfo).all(db).await?;
//vec是[Model { code: "123456", name: "芯片ETF", box: None, hold: false }, Model { code: "234567", name: "测试ETF", box: None, hold: true }, Model { code: "345678", name: "新能源", box: None, hold: true }]
// let x = StockGroups::find().find_also_linked(GroupToStockInfo).all(db).await?;
// let x = StockGroups::find().find_with_linked(GroupToStockInfo).all(db).await?;
// let x = StockGroups::find().find_with_linked(GroupToStockInfo).filter(stock_group::Column::Name.eq(group_name.clone())).all(db).await?;
Ok(1)
}
但是遇到一个问题，就是无法按照分组内的索引排序，因为在GroupToStockInfo中定义了ToEntity = StockInfos，可能结果只能是纯粹的stock_info，查了好多也试了很多不知道怎么解，所以使用Custom Join Condition。
Chained Relations | SeaORM 🐚 An async & dynamic ORM for Rust (sea-ql.org)
3.Custom Join Condition
首先明确我们想要的结果：
#[derive(FromQueryResult, Debug)]
struct MoreStockInfo {
group_name: String,
index: i32,
stock_code: String,
stock_name: String,
r#box: Option<String>,
}
构建查询语句（这个其实也是有id时写的）:
///根据分组名称查询分组下的所有股票（按照索引排序）
pub async fn find_stocks_by_group_name(group_name: String) -> AppResult<i32> {
let db = crate::entities::DB
.get()
.ok_or(anyhow::anyhow!("数据库未初始化"))?;
// let model = StockGroups::find()
// .filter(stock_group::Column::Name.eq(group_name.clone()))
// .one(db)
// .await?
// .ok_or(anyhow!("未找到{}分组", group_name))?;
// let stock_infos = model.find_linked(GroupToStockInfo).all(db).await?;
// println!("{:?}",stock_infos);
// let x = StockGroups::find().find_also_linked(GroupToStockInfo).all(db).await?;
// let x = StockGroups::find().find_with_linked(GroupToStockInfo).all(db).await?;
// let x = StockGroups::find().find_with_linked(GroupToStockInfo).filter(stock_group::Column::Name.eq(group_name.clone())).all(db).await?;
let more_infos = GroupStockRs::find()
// .column_as(stock_info::Column::Code, "stock_code") // 假设stock_code来自stock_info表
// .column_as(stock_group::Column::Name, "group_name")
// .column_as(group_stock_relation::Column::Index, "index")
.column_as(stock_info::Column::Name, "stock_name")
.column_as(stock_info::Column::Box, "box")
.join(
JoinType::InnerJoin,
group_stock_relation::Relation::StockGroups.def(),
)
.join(
JoinType::InnerJoin,
group_stock_relation::Relation::StockInfos.def(),
)
// .join_rev(JoinType::InnerJoin, stock_group::Relation::GroupStockRs.def()) join_rev是反向连接
// .join_rev(JoinType::InnerJoin, stock_info::Relation::GroupStockRs.def())
.filter(stock_group::Column::Name.eq(group_name.clone()))
.order_by_asc(group_stock_relation::Column::Index)
.into_model::<MoreStockInfo>()
// .into_tuple::<(String,String,i32,String,Option<String>)>()
.all(db)
.await?;
println!("{:?}", more_infos);
//[MoreStockInfo { group_name: "全部", index: 0, stock_code: "123456", stock_name: "芯片ETF", box: None }, MoreStockInfo { group_name: "全部", index: 1, stock_code: "345678", stock_name: "新能源", box: None }, MoreStockInfo { group_name: "全部", index: 2, stock_code: "234567", stock_name: "测试ETF", box: None }]
Ok(1)
}
