Select

Once you have defined the entity, you are ready to retrieve data from the database. Each row of data in the database corresponds to a Model.

By default, SeaORM will select all columns defined in the Column enum.

Find by Primary Key

Find a model by its primary key, it can be a single key or composite key. We start by calling find_by_id on Entity which helps you construct the select query and condition automatically. Then, fetch a single model from the database with the one method.

use super::cake::Entity as Cake;
use super::cake_filling::Entity as CakeFilling;

// Find by primary key
let cheese: Option<cake::Model> = Cake::find_by_id(1).one(db).await?;

// Find by composite primary keys
let vanilla: Option<cake_filling::Model> = CakeFilling::find_by_id((6, 8)).one(db).await?;

Find with Conditions and Orders

In addition to retrieving a model by primary key, you can also retrieve one or more models matching specific conditions in a certain order. The find method gives you access to the query builder in SeaORM. It supports the construction of all common select expressions like where and order by. They can be constructed using filter and order_by_* methods respectively.

Read more about conditional expression.

let chocolate: Vec<cake::Model> = Cake::find()
    .filter(cake::Column::Name.contains("chocolate"))
    .order_by_asc(cake::Column::Name)
    .all(db)
    .await?;

Strongly-Typed COLUMN Constant

Since 2.0.0

Requires #[sea_orm::model] or #[sea_orm::compact_model].

SeaORM 2.0 generates a COLUMN constant with type-aware methods per column. Instead of using the Column enum (which accepts any value type), use COLUMN for compile-time type checking:

// Column enum: compiles even if the type is wrong
cake::Column::Name.contains("chocolate")

// COLUMN constant: type-checked, lowercase field names
cake::COLUMN.name.contains("chocolate")

// compile error: `like` expects a string, not an integer
cake::COLUMN.name.like(2)

Each column is wrapped in a type-specific struct (StringColumn, NumericColumn, DateLikeColumn, etc.) that exposes only methods relevant to that type:

user::COLUMN.name.contains("Bob")           // StringColumn: LIKE '%Bob%'
user::COLUMN.id.between(1, 100)             // NumericColumn
user::COLUMN.created_at.gt(some_date)       // DateTimeLikeColumn
collection::COLUMN.tags.contains(vec!["a"]) // ArrayColumn: @> ARRAY['a']

The COLUMN constant is also accessible as Entity::COLUMN:

user::Entity::find().filter(user::Entity::COLUMN.name.contains("Bob"))

Find by Unique Key

Since 2.0.0

If an entity has a #[sea_orm(unique)] attribute, SeaORM generates find_by_* and filter_by_* convenience methods:

#[sea_orm::model]
#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(table_name = "user")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    #[sea_orm(unique)]
    pub email: String,
    ..
}

let bob = user::Entity::find_by_email("bob@sea-ql.org").one(db).await?;

For composite unique keys defined with #[sea_orm(unique_key = "pair")], a find_by_pair method is generated:

let item = composite_a::Entity::find_by_pair((1, 2)).one(db).await?;

These methods are also available on the Entity Loader:

let bob = user::Entity::load()
    .filter_by_email("bob@sea-ql.org")
    .with(profile::Entity)
    .one(db)
    .await?;

Find Related Models

Read more on the Relation chapter.

Lazy Loading

Use the find_related method.

Related models are loaded on demand when you ask for them, preferable if you want to load related models based on some application logic. Note that lazy loading will increase database round trips compared to eager loading.

// Find a cake model first
let cheese: Option<cake::Model> = Cake::find_by_id(1).one(db).await?;
let cheese: cake::Model = cheese.unwrap();

// Then, find all related fruits of this cake
let fruits: Vec<fruit::Model> = cheese.find_related(Fruit).all(db).await?;

Eager Loading

All related models are loaded in the same query with join.

One to One

Use the find_also_related method.

let fruits_and_cakes: Vec<(fruit::Model, Option<cake::Model>)> = Fruit::find().find_also_related(Cake).all(db).await?;

One to Many / Many to Many

Using the find_with_related method, the related models will be grouped by the parent models. This method handles both 1-N and M-N cases, and will perform 2 joins when there is a junction table involved.

let cake_with_fruits: Vec<(cake::Model, Vec<fruit::Model>)> = Cake::find()
    .find_with_related(Fruit)
    .all(db)
    .await?;

Entity Loader

You can load related Entities into a nested struct called ModelEx.

Since 2.0.0

This requires the #[sea_orm::model] or #[sea_orm::compact_model] macro on entity definition. Learn more here.

// join paths:
// cake -> fruit
// cake -> cake_filling -> filling

let super_cake = cake::Entity::load()
    .with(fruit::Entity) // 1-1 uses join
    .with(filling::Entity) // M-N uses data loader
    .one(db)
    .await?
    .unwrap();

super_cake
    == cake::ModelEx {
        id: 12,
        name: "Black Forest".into(),
        fruit: Some(fruit::ModelEx {
            name: "Cherry".into(),
        }.into()),
        fillings: vec![filling::ModelEx {
            name: "Chocolate".into(),
        }],
    };

Model Loader

Use the LoaderTrait to load related entities in batches.

Compared to eager loading, it saves bandwidth (consider the one to many case, the one side rows may duplicate) at the cost of one more database query.

One to One

Use the load_one method.

let fruits: Vec<fruit::Model> = Fruit::find().all(db).await?;
let cakes: Vec<Option<cake::Model>> = fruits.load_one(Cake, db).await?;

One to Many

Use the load_many method.

let cakes: Vec<cake::Model> = Cake::find().all(db).await?;
let fruits: Vec<Vec<fruit::Model>> = cakes.load_many(Fruit, db).await?;

Many to Many

Use the same load_many method.

Since 2.0.0

You don't have to provide the junction Entity. It's where SeaORM shines!

let cakes: Vec<cake::Model> = Cake::find().all(db).await?;
let fillings: Vec<Vec<filling::Model>> = cakes.load_many(Filling, db).await?;

Paginate Result

Convert any SeaORM select into a paginator with custom page size.

use sea_orm::{entity::*, query::*, tests_cfg::cake};
let mut cake_pages = cake::Entity::find()
    .order_by_asc(cake::Column::Id)
    .paginate(db, 50);
 
while let Some(cakes) = cake_pages.fetch_and_next().await? {
    // Do something on cakes: Vec<cake::Model>
}

Cursor Pagination

Use cursor pagination If you want to paginate rows based on column(s) such as the primary key.

use sea_orm::{entity::*, query::*, tests_cfg::cake};
// Create a cursor that order by `cake`.`id`
let mut cursor = cake::Entity::find().cursor_by(cake::Column::Id);

// Filter paginated result by `cake`.`id` > 1 AND `cake`.`id` < 100
cursor.after(1).before(100);

// Get first 10 rows (order by `cake`.`id` ASC)
for cake in cursor.first(10).all(db).await? {
    // Do something on cake: cake::Model
}

// Get last 10 rows (order by `cake`.`id` DESC but rows are returned in ascending order)
for cake in cursor.last(10).all(db).await? {
    // Do something on cake: cake::Model
}

Paginate rows based on a composite primary keys are also supported.

use sea_orm::{entity::*, query::*, tests_cfg::cake_filling};
let rows = cake_filling::Entity::find()
    .cursor_by((cake_filling::Column::CakeId, cake_filling::Column::FillingId))
    .after((0, 1))
    .before((10, 11))
    .first(3)
    .all(&db)
    .await?;

Select Partial Model

If you want to select just a subset of columns, you can define a Partial Model.

use sea_orm::DerivePartialModel;

#[derive(DerivePartialModel)]
#[sea_orm(entity = "cake::Entity")]
struct CakeWithFruit {
    name: String,
    #[sea_orm(nested)]
    fruit: Option<fruit::Model>, // this can be a regular or another partial model
}

let cakes: Vec<CakeWithFruit> = Cake::find()
    .left_join(fruit::Entity)
    .into_partial_model() // only the columns in the partial model will be selected
    .all(db)
    .await?;