ormlite_core/query_builder/

select.rs

use crate::error::{Error, Result};
use crate::model::Model;
use crate::query_builder::args::QueryBuilderArgs;
use crate::query_builder::{Placeholder, util};
use sql::{Expr, OrderBy, ToSql};

use crate::join::{JoinDescription, criteria, select_columns};
use sql::{Select, query::Where};
use sqlx::{AssertSqlSafe, Executor};
use std::marker::PhantomData;

// Add additional information to the sqlx::Database
pub trait DatabaseMetadata {
    fn dialect() -> sql::Dialect;
    fn placeholder() -> Placeholder;
}

#[cfg(feature = "postgres")]
impl DatabaseMetadata for sqlx::postgres::Postgres {
    fn dialect() -> sql::Dialect {
        sql::Dialect::Postgres
    }

    fn placeholder() -> Placeholder {
        Placeholder::dollar_sign()
    }
}

#[cfg(feature = "sqlite")]
impl DatabaseMetadata for sqlx::sqlite::Sqlite {
    fn dialect() -> sql::Dialect {
        sql::Dialect::Sqlite
    }

    fn placeholder() -> Placeholder {
        Placeholder::question_mark()
    }
}

pub struct SelectQueryBuilder<DB, Model>
where
    DB: sqlx::Database,
{
    pub query: Select,
    arguments: QueryBuilderArgs<DB>,
    model: PhantomData<Model>,
    placeholder: Placeholder,
}

impl<DB, M> SelectQueryBuilder<DB, M>
where
    M: Sized + Send + Sync + Unpin + for<'r> sqlx::FromRow<'r, DB::Row> + 'static + Model<DB>,
    DB: sqlx::Database + DatabaseMetadata,
{
    pub async fn fetch_all<'executor, E>(self, db: E) -> Result<Vec<M>>
    where
        E: Executor<'executor, Database = DB>,
    {
        let (text, args) = self.into_query_and_args()?;
        sqlx::query_as_with::<DB, M, _>(AssertSqlSafe(text), args)
            .fetch_all(db)
            .await
            .map_err(Error::from)
    }

    pub async fn fetch_one<'executor, E>(mut self, db: E) -> Result<M>
    where
        E: Executor<'executor, Database = DB>,
    {
        if self.query.limit.is_none() {
            self.query.limit = Some(1);
        }
        let (text, args) = self.into_query_and_args()?;
        sqlx::query_as_with::<DB, M, _>(AssertSqlSafe(text), args)
            .fetch_one(db)
            .await
            .map_err(Error::from)
    }

    pub async fn fetch_optional<'executor, E>(mut self, db: E) -> Result<Option<M>>
    where
        E: Executor<'executor, Database = DB>,
    {
        if self.query.limit.is_none() {
            self.query.limit = Some(1);
        }
        let (text, args) = self.into_query_and_args()?;
        sqlx::query_as_with::<DB, M, _>(AssertSqlSafe(text), args)
            .fetch_optional(db)
            .await
            .map_err(Error::from)
    }

    pub fn with(mut self, name: &str, query: &str) -> Self {
        self.query = self.query.with_raw(name, query);
        self
    }

    /// Add a column to the query. Note you typically don't need this, as creating a query from
    /// `Model::select` will automatically add that model's columns.
    ///
    /// # Arguments
    /// * `column` - The column to add. Examples: "id", "name", "person.*"
    pub fn select(mut self, column: impl Into<String>) -> Self {
        self.query = self.query.select_raw(column.into());
        self
    }

    /// Add a WHERE clause to the query.
    /// Do not use format! to add parameters. Instead, use `?` as the placeholder, and add
    /// parameters with [`bind`](Self::bind).
    ///
    /// Postgres users: You can (and should) use `?` as the placeholder. You might not have a defined
    /// numerical order for your parameters, preventing $<N> syntax. Upon execution, the query
    /// builder replaces `?` with `$<N>`. If you need the same parameter multiple times, you should
    /// bind it multiple times. Arguments aren't moved, so this doesn't incur a memory cost. If you
    /// still want to re-use parameters, you can use $<N> placeholders. However, don't mix `?` and
    /// `$<N>` placeholders, as they will conflict.
    ///
    /// # Arguments
    /// * `clause` - The clause to add. Examples: "id = ?", "name = ?", "person.id = ?"
    pub fn where_(mut self, clause: &'static str) -> Self {
        self.query = self.query.where_raw(clause);
        self
    }

    /// Convenience method to add a `WHERE` and bind a value in one call.
    pub fn where_bind<'t, T>(mut self, clause: &'static str, value: T) -> Self
    where
        T: Send + sqlx::Type<DB> + sqlx::Encode<'t, DB>,
    {
        self.query = self.query.where_raw(clause);
        self.arguments.add(value);
        self
    }
    /// Dangerous because it takes a string that could be user crafted. You should prefer `.where_` which
    /// takes a &'static str, and pass arguments with `.bind()`.
    pub fn dangerous_where(mut self, clause: &str) -> Self {
        self.query = self.query.where_raw(clause);
        self
    }

    pub fn join(mut self, join_description: JoinDescription) -> Self {
        match &join_description {
            JoinDescription::ManyToOne {
                columns,
                foreign_table,
                field,
                foreign_key,
                local_column,
            } => {
                let join = sql::query::Join {
                    typ: sql::query::JoinType::Left,
                    table: sql::query::JoinTable::Table {
                        schema: None,
                        table: foreign_table.to_string(),
                    },
                    alias: Some(field.to_string()),
                    criteria: criteria(M::table_name(), local_column, field, foreign_key),
                };
                self.query.join.push(join);
                self.query.columns.extend(select_columns(columns, field))
            }
        }
        self
    }

    #[doc(hidden)]
    #[deprecated(note = "Please use `where_` instead")]
    pub fn filter(self, clause: &'static str) -> Self {
        self.where_(clause)
    }

    /// Add a HAVING clause to the query.
    pub fn having(mut self, clause: &str) -> Self {
        self.query = self.query.having(Where::Expr(Expr::Raw(clause.to_string())));
        self
    }

    /// Add a GROUP BY clause to the query.
    ///
    /// # Arguments:
    /// * `clause`: The GROUP BY clause to add. Examples: "id", "id, date", "1, 2, ROLLUP(3)"
    pub fn group_by(mut self, clause: &str) -> Self {
        self.query = self.query.group_by(clause);
        self
    }

    /// Add an ORDER BY clause to the query.
    ///
    /// # Arguments:
    /// * `clause`: The ORDER BY clause to add. "created_at DESC", "id ASC NULLS FIRST"
    /// * `direction`: Direction::Asc or Direction::Desc
    pub fn order_by(mut self, order: OrderBy) -> Self {
        self.query = self.query.order_by(order);
        self
    }

    pub fn order_asc(mut self, clause: &str) -> Self {
        self.query = self.query.order_asc(clause);
        self
    }

    pub fn order_desc(mut self, clause: &str) -> Self {
        self.query = self.query.order_desc(clause);
        self
    }

    /// Add a limit to the query.
    pub fn limit(mut self, limit: usize) -> Self {
        self.query = self.query.limit(limit);
        self
    }

    /// Add an offset to the query.
    pub fn offset(mut self, offset: usize) -> Self {
        self.query = self.query.offset(offset);
        self
    }

    /// Bind an argument to the query.
    pub fn bind<'t, T>(mut self, value: T) -> Self
    where
        T: Send + sqlx::Type<DB> + sqlx::Encode<'t, DB>,
    {
        self.arguments.add(value);
        self
    }

    pub fn into_query_and_args(mut self) -> Result<(String, QueryBuilderArgs<DB>)> {
        let q = self.query.to_sql(DB::dialect());
        let args = self.arguments;
        let (q, placeholder_count) = util::replace_placeholders(&q, &mut self.placeholder)?;
        if placeholder_count != args.len() {
            return Err(Error::OrmliteError(format!(
                "Failing to build query. {} placeholders were found in the query, but \
                {} arguments were provided.",
                placeholder_count,
                args.len(),
            )));
        }
        Ok((q, args))
    }
}

impl<DB: sqlx::Database + DatabaseMetadata, M: Model<DB>> Default for SelectQueryBuilder<DB, M> {
    fn default() -> Self {
        Self {
            query: Select::default().from(M::table_name()),
            arguments: QueryBuilderArgs::default(),
            model: PhantomData,
            placeholder: DB::placeholder(),
        }
    }
}