Advanced Queries

Aggregate Queries

let count: usize = db.select::<User>().count(|u| u.id).await?;

let total: Option<i32> = db.select::<Product>().sum(|p| p.price).await?;

let avg: Option<f64> = db.select::<User>().avg(|u| u.age).await?;

let max: Option<i32> = db.select::<User>().max(|u| u.age).await?;

let min: Option<i32> = db.select::<User>().min(|u| u.age).await?;

With conditions:

let adult_count: usize = db
    .select::<User>()
    .filter(|u| u.age.ge(18))
    .count(|u| u.id)
    .await?;

GROUP BY and HAVING Queries

Basic Grouping

use ormer::Select;

let sql = Select::<User>::new()
    .select_column(|u| u.id.count())
    .group_by(|u| u.age)
    .to_sql();

Multiple Columns + Grouping

let sql = Select::<User>::new()
    .select_column(|u| (u.department, u.id.count()))
    .group_by(|u| u.department)
    .to_sql();

HAVING Condition Filter

let sql = Select::<User>::new()
    .select_column(|u| (u.department, u.id.count()))
    .group_by(|u| u.department)
    .having(|u| u.id.count().gt(5))
    .to_sql();

Multi-Column Grouping

let sql = Select::<User>::new()
    .select_column(|u| (u.department, u.age, u.id.count(), u.score.avg()))
    .group_by(|u| (u.department, u.age))
    .to_sql();

Complete Query: WHERE + GROUP BY + HAVING + ORDER BY + LIMIT

let sql = Select::<User>::new()
    .filter(|u| u.age.ge(18))
    .select_column(|u| (u.department, u.id.count(), u.score.avg()))
    .group_by(|u| u.department)
    .having(|u| u.id.count().gt(0))
    .order_by(|u| u.department)
    .range(0..10)
    .to_sql();

Supported Aggregate Functions

count() - Count, returns usize
sum() - Sum, returns original type (numeric types)
avg() - Average, returns f64
max() - Maximum, returns original type (numeric types)
min() - Minimum, returns original type (numeric types)

JOIN Queries

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    name: String,
}

#[derive(Debug, Model)]
#[table = "roles"]
struct Role {
    #[primary]
    id: i32,
    user_id: i32,
    role_name: String,
}

LEFT JOIN

let user_roles: Vec<(User, Option<Role>)> = db
    .select::<User>()
    .left_join::<Role>(|u, r| u.id.eq(r.user_id))
    .collect()
    .await?;

INNER JOIN

let user_roles: Vec<(User, Role)> = db
    .select::<User>()
    .inner_join::<Role>(|u, r| u.id.eq(r.user_id))
    .collect()
    .await?;

RIGHT JOIN

let user_roles: Vec<(Option<User>, Role)> = db
    .select::<User>()
    .right_join::<Role>(|u, r| u.id.eq(r.user_id))
    .collect()
    .await?;

JOIN with Filter

let admin_users: Vec<(User, Role)> = db
    .select::<User>()
    .inner_join::<Role>(|u, r| u.id.eq(r.user_id))
    .filter(|u| u.name.eq("Alice".to_string()))
    .collect()
    .await?;

JOIN with Right-Table Sorting and Pagination (LATERAL JOIN)

When order_by / order_by_desc or range is used in the JOIN condition, the framework automatically generates LATERAL JOIN SQL to enable sorting and pagination on the right table.

// Right table sorted by role_name desc, take only the first row
let user_roles: Vec<(User, Option<Role>)> = db
    .select::<User>()
    .left_join::<Role>(|u, r| u.id.eq(r.user_id).order_by_desc(r.role_name).range(..1))
    .collect()
    .await?;

// Sort only
let user_roles: Vec<(User, Option<Role>)> = db
    .select::<User>()
    .left_join::<Role>(|u, r| u.id.eq(r.user_id).order_by_desc(r.role_name))
    .collect()
    .await?;

// Pagination only
let user_roles: Vec<(User, Option<Role>)> = db
    .select::<User>()
    .left_join::<Role>(|u, r| u.id.eq(r.user_id).range(..3))
    .collect()
    .await?;

Supported JOIN types: left_join, inner_join, right_join.

Can be combined with filter, range, and other methods on the main query.

Multi-Table Joins

Two Tables (from)

let users: Vec<User> = db
    .select::<User>()
    .from::<User, Role>()
    .filter(|u, r| u.id.eq(r.user_id))
    .filter(|_, r| r.role_name.eq("admin".to_string()))
    .collect()
    .await?;

Three Tables (from3)

let users: Vec<User> = db
    .select::<User>()
    .from3::<User, Role, Permission>()
    .filter(|u, r, p| u.id.eq(r.user_id).and(r.id.eq(p.role_id)))
    .collect()
    .await?;

Four Tables (from4)

let users: Vec<User> = db
    .select::<User>()
    .from4::<User, Role, Permission, Department>()
    .filter(|u, r, p, d| {
        u.id.eq(r.user_id)
            .and(r.id.eq(p.role_id))
            .and(u.department_id.eq(d.id))
    })
    .collect()
    .await?;

Subqueries

IN Subquery

let subquery = db.select::<Role>().map_to(|r| r.user_id);

let users: Vec<User> = db
    .select::<User>()
    .filter(|u| u.id.is_in(subquery))
    .collect()
    .await?;

EXISTS / NOT EXISTS

Use Select::exists() and Select::not_exists() to build subquery expressions:

let users_with_roles: Vec<User> = db
    .select::<User>()
    .filter(|_u| {
        Select::<Role>::new()
            .filter(|r| r.name.eq("admin"))
            .exists()  // or .not_exists()
    })
    .collect()
    .await?;

Can be combined with outer conditions:

.filter(|p| p.age.ge(18).or(
    Select::<Role>::new().filter(|r| r.uid.eq(p.id)).exists()
))

Set Operations

UNION / UNION ALL

// UNION
let sql = Select::<User>::new()
    .filter(|u| u.age.gt(30))
    .union(Select::<User>::new().filter(|u| u.age.lt(18)))
    .to_sql();

// UNION ALL
let sql = Select::<User>::new()
    .filter(|u| u.age.gt(30))
    .union_all(Select::<User>::new().filter(|u| u.age.lt(18)))
    .to_sql();

INTERSECT / EXCEPT

let sql = Select::<User>::new()
    .filter(|u| u.age.gt(18))
    .intersect(Select::<User>::new().filter(|u| u.age.lt(65)))
    .to_sql();

let sql = Select::<User>::new()
    .filter(|u| u.age.gt(18))
    .except(Select::<User>::new().filter(|u| u.name.eq("admin")))
    .to_sql();

Set operations support chained order_by and range:

let sql = Select::<User>::new()
    .filter(|u| u.age.gt(30))
    .order_by(|u| u.name)
    .range(..10)
    .union(
        Select::<User>::new()
            .filter(|u| u.age.lt(18))
            .order_by_desc(|u| u.age)
            .range(..5),
    )
    .to_sql();

Complete Example

use ormer::{Database, DbType, Model};

#[derive(Debug, Model)]
#[table = "users"]
struct User {
    #[primary(auto)]
    id: i32,
    name: String,
    age: i32,
}

#[derive(Debug, Model)]
#[table = "roles"]
struct Role {
    #[primary(auto)]
    id: i32,
    user_id: i32,
    role_name: String,
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let db = Database::connect(DbType::Sqlite, "file:test.db").await?;
    db.create_table::<User>().execute().await?;
    db.create_table::<Role>().execute().await?;
    
    db.insert(&vec![
        User { id: 1, name: "Alice".to_string(), age: 25 },
        User { id: 2, name: "Bob".to_string(), age: 30 },
    ]).await?;
    
    db.insert(&vec![
        Role { id: 1, user_id: 1, role_name: "admin".to_string() },
        Role { id: 2, user_id: 2, role_name: "user".to_string() },
    ]).await?;
    
    // Aggregate
    let count: usize = db.select::<User>().count(|u| u.id).await?;
    let avg_age: Option<f64> = db.select::<User>().avg(|u| u.age).await?;
    
    // LEFT JOIN
    let user_roles: Vec<(User, Option<Role>)> = db
        .select::<User>()
        .left_join::<Role>(|u, r| u.id.eq(r.user_id))
        .collect()
        .await?;
    
    // Multi-table
    let admin_users: Vec<User> = db
        .select::<User>()
        .from::<User, Role>()
        .filter(|u, r| u.id.eq(r.user_id))
        .filter(|_, r| r.role_name.eq("admin".to_string()))
        .collect()
        .await?;
    
    // Subquery
    let users_with_roles: Vec<User> = db
        .select::<User>()
        .filter(|u| u.id.is_in(
            db.select::<Role>().map_to(|r| r.user_id)
        ))
        .collect()
        .await?;
    
    db.drop_table::<Role>().execute().await?;
    db.drop_table::<User>().execute().await?;
    Ok(())
}