Custom Select
By default, SeaORM will select all columns defined in the Column
enum. You can override the defaults if you wish to select certain columns only.
Clear Default Selection
Clear the default selection by calling the select_only
method if needed. Then, you can select some of the attributes or even custom expressions after it.
// Selecting all columns
assert_eq!(
cake::Entity::find()
.build(DbBackend::Postgres)
.to_string(),
r#"SELECT "cake"."id", "cake"."name" FROM "cake""#
);
Select Some Attributes Only
Use select_only
and column
methods together to select only the attributes you want.
// Selecting the name column only
assert_eq!(
cake::Entity::find()
.select_only()
.column(cake::Column::Name)
.build(DbBackend::Postgres)
.to_string(),
r#"SELECT "cake"."name" FROM "cake""#
);
Select Custom Expressions
Select any custom expression with column_as
method, it takes any sea_query::SimpleExpr
and an alias. Use sea_query::Expr
helper to build SimpleExpr
.
use sea_query::{Alias, Expr};
assert_eq!(
cake::Entity::find()
.column_as(Expr::col(cake::Column::Id).max().sub(Expr::col(cake::Column::Id)), "id_diff")
.column_as(Expr::cust("CURRENT_TIMESTAMP"), "current_time")
.build(DbBackend::Postgres)
.to_string(),
r#"SELECT "cake"."id", "cake"."name", MAX("id") - "id" AS "id_diff", CURRENT_TIMESTAMP AS "current_time" FROM "cake""#
);
Handling Custom Selects
You can use a custom struct
derived from the FromQueryResult
trait to handle the result of a complex query. It is especially useful when dealing with custom columns or multiple joins which cannot directly be converted into models. It may be used to receive the result of any query, even raw SQL.
use sea_orm::{FromQueryResult, JoinType, RelationTrait};
use sea_query::Expr;
#[derive(FromQueryResult)]
struct CakeAndFillingCount {
id: i32,
name: String,
count: i32,
}
let cake_counts: Vec<CakeAndFillingCount> = cake::Entity::find()
.column_as(filling::Column::Id.count(), "count")
.join_rev(
// construct `RelationDef` on the fly
JoinType::InnerJoin,
cake_filling::Entity::belongs_to(cake::Entity)
.from(cake_filling::Column::CakeId)
.to(cake::Column::Id)
.into()
)
// reuse a `Relation` from existing Entity
.join(JoinType::InnerJoin, cake_filling::Relation::Filling.def())
.group_by(cake::Column::Id)
.into_model::<CakeAndFillingCount>()
.all(db)
.await?;
Selecting a single value without a custom struct
is also possible.
use sea_orm::{entity::*, query::*, tests_cfg::cake, DeriveColumn, EnumIter};
#[derive(Copy, Clone, Debug, EnumIter, DeriveColumn)]
enum QueryAs {
CakeName,
}
let res: Vec<String> = cake::Entity::find()
.select_only()
.column_as(cake::Column::Name, QueryAs::CakeName)
.into_values::<_, QueryAs>()
.all(&db)
.await?;
assert_eq!(
res,
vec!["Chocolate Forest".to_owned(), "New York Cheese".to_owned()]
);
You can even select a tuple value.
use sea_orm::{entity::*, query::*, tests_cfg::cake, DeriveColumn, EnumIter};
#[derive(Copy, Clone, Debug, EnumIter, DeriveColumn)]
enum QueryAs {
CakeName,
NumOfCakes,
}
let res: Vec<(String, i64)> = cake::Entity::find()
.select_only()
.column_as(cake::Column::Name, QueryAs::CakeName)
.column_as(cake::Column::Id.count(), QueryAs::NumOfCakes)
.group_by(cake::Column::Name)
.into_values::<_, QueryAs>()
.all(&db)
.await?;
assert_eq!(res, vec![("Chocolate Forest".to_owned(), 2i64)]);