Skip to main content
Version: 0.10.x

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)]);