use std::{borrow::Cow, fmt::Display};

use crate::{
    ColumnRef, ConditionHolder, ConditionalStatement, DynIden, Expr, FunctionCall, IntoColumnRef,
    IntoCondition, IntoIden, IntoTableRef, IntoValueTuple, JoinOn, JoinType, LogicalChainOper,
    NullOrdering, Order, OrderExpr, OrderedStatement, QueryStatement, QueryStatementBuilder,
    QueryStatementWriter, SqlWriter, SubQueryStatement, TableRef, Value, ValueTuple, Values,
    WindowStatement, WithClause, WithQuery, backend::QueryBuilder,
};

/// Select rows from an existing table
///
/// # Examples
///
/// ```
/// use sea_query::{*, tests_cfg::*};
///
/// let query = Query::select()
///     .column(Char::Character)
///     .column((Font::Table, Font::Name))
///     .from(Char::Table)
///     .left_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
///     .and_where(Expr::col(Char::SizeW).is_in([3, 4]))
///     .and_where(Expr::col(Char::Character).like("A%"))
///     .to_owned();
///
/// assert_eq!(
///     query.to_string(MysqlQueryBuilder),
///     r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id` WHERE `size_w` IN (3, 4) AND `character` LIKE 'A%'"#
/// );
/// assert_eq!(
///     query.to_string(PostgresQueryBuilder),
///     r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" WHERE "size_w" IN (3, 4) AND "character" LIKE 'A%'"#
/// );
/// assert_eq!(
///     query.to_string(SqliteQueryBuilder),
///     r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" WHERE "size_w" IN (3, 4) AND "character" LIKE 'A%'"#
/// );
/// ```
#[derive(Default, Debug, Clone, PartialEq)]
pub struct SelectStatement {
    pub(crate) distinct: Option<SelectDistinct>,
    pub(crate) selects: Vec<SelectExpr>,
    pub(crate) into: Option<SelectInto>,
    pub(crate) from: Vec<TableRef>,
    pub(crate) join: Vec<JoinExpr>,
    pub(crate) r#where: ConditionHolder,
    pub(crate) groups: Vec<Expr>,
    pub(crate) having: ConditionHolder,
    pub(crate) unions: Vec<(UnionType, SelectStatement)>,
    pub(crate) orders: Vec<OrderExpr>,
    pub(crate) limit: Option<Value>,
    pub(crate) offset: Option<Value>,
    pub(crate) lock: Option<LockClause>,
    pub(crate) window: Option<(DynIden, WindowStatement)>,
    pub(crate) with: Option<Box<WithClause>>,
    #[cfg(feature = "backend-postgres")]
    pub(crate) table_sample: Option<crate::extension::postgres::TableSample>,
    #[cfg(feature = "backend-mysql")]
    pub(crate) index_hints: std::collections::HashMap<
        index_hint::IndexHintKey,
        Vec<crate::extension::mysql::IndexHint>,
    >,
}

#[cfg(feature = "backend-mysql")]
mod index_hint {
    use crate::*;
    #[derive(Debug, Clone, PartialEq, Eq, Hash)]
    pub(crate) enum IndexHintKey {
        Table(TableName, Option<DynIden>),
        Alias(DynIden),
    }

    impl From<TableRef> for IndexHintKey {
        fn from(value: TableRef) -> Self {
            match value {
                TableRef::Table(table_name, alias) => Self::Table(table_name, alias),
                TableRef::SubQuery(_, alias)
                | TableRef::ValuesList(_, alias)
                | TableRef::FunctionCall(_, alias) => Self::Alias(alias),
            }
        }
    }

    impl From<&TableRef> for IndexHintKey {
        fn from(value: &TableRef) -> Self {
            match value {
                TableRef::Table(table_name, alias) => {
                    Self::Table(table_name.clone(), alias.clone())
                }
                TableRef::SubQuery(_, alias)
                | TableRef::ValuesList(_, alias)
                | TableRef::FunctionCall(_, alias) => Self::Alias(alias.clone()),
            }
        }
    }

    impl PartialEq<&TableRef> for IndexHintKey {
        fn eq(&self, other: &&TableRef) -> bool {
            match (self, other) {
                (Self::Table(table_name, alias), TableRef::Table(table_name2, alias2)) => {
                    table_name == table_name2 && alias == alias2
                }
                (
                    Self::Alias(alias),
                    TableRef::SubQuery(_, alias2)
                    | TableRef::ValuesList(_, alias2)
                    | TableRef::FunctionCall(_, alias2),
                ) => alias == alias2,
                _ => false,
            }
        }
    }
}

/// List of distinct keywords that can be used in select statement
#[derive(Debug, Clone, PartialEq)]
#[non_exhaustive]
pub enum SelectDistinct {
    All,
    Distinct,
    DistinctRow,
    DistinctOn(Vec<ColumnRef>),
}

/// Window type in [`SelectExpr`]
#[derive(Debug, Clone, PartialEq)]
#[non_exhaustive]
pub enum WindowSelectType {
    /// Name in [`SelectStatement`]
    Name(DynIden),
    /// Inline query in [`SelectExpr`]
    Query(WindowStatement),
}

/// Select expression used in select statement
#[derive(Debug, Clone, PartialEq)]
pub struct SelectExpr {
    pub expr: Expr,
    pub alias: Option<DynIden>,
    pub window: Option<WindowSelectType>,
}

/// The table type of target table of SELECT INTO clause
#[derive(Debug, Clone, PartialEq)]
pub enum SelectIntoTableModifier {
    Temporary,
    Unlogged,
}

/// The INTO clause in SELECT statement
#[derive(Debug, Clone, PartialEq)]
pub struct SelectInto {
    pub target_table: DynIden,
    pub target_table_modifier: Option<SelectIntoTableModifier>,
}

/// Join expression used in select statement
#[derive(Debug, Clone, PartialEq)]
pub struct JoinExpr {
    pub join: JoinType,
    pub table: Box<TableRef>,
    pub on: Option<JoinOn>,
    pub lateral: bool,
}

/// List of lock types that can be used in select statement
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
#[non_exhaustive]
pub enum LockType {
    /// Exclusive lock
    Update,
    NoKeyUpdate,
    /// Shared lock
    Share,
    KeyShare,
}

/// List of lock behavior can be used in select statement
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
#[non_exhaustive]
pub enum LockBehavior {
    Nowait,
    SkipLocked,
}

#[derive(Debug, Clone, PartialEq)]
pub struct LockClause {
    pub(crate) r#type: LockType,
    pub(crate) tables: Vec<TableRef>,
    pub(crate) behavior: Option<LockBehavior>,
}

/// List of union types that can be used in union clause
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
#[non_exhaustive]
pub enum UnionType {
    Intersect,
    Distinct,
    Except,
    All,
}

impl<T> From<T> for SelectExpr
where
    T: Into<Expr>,
{
    fn from(expr: T) -> Self {
        SelectExpr {
            expr: expr.into(),
            alias: None,
            window: None,
        }
    }
}

impl Display for SelectIntoTableModifier {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        match self {
            SelectIntoTableModifier::Temporary => f.write_str("TEMPORARY"),
            SelectIntoTableModifier::Unlogged => f.write_str("UNLOGGED"),
        }
    }
}

impl SelectInto {
    /// Set the target table of SELECT INTO clause
    pub fn table<T>(t: T) -> Self
    where
        T: IntoIden,
    {
        Self {
            target_table: t.into_iden(),
            target_table_modifier: None,
        }
    }

    /// Set a modifier for the target table like Temporary or unlogged
    pub fn modifier(mut self, modifier: SelectIntoTableModifier) -> Self {
        self.target_table_modifier = Some(modifier);
        self
    }
}

impl From<WindowStatement> for WindowSelectType {
    fn from(stmt: WindowStatement) -> Self {
        Self::Query(stmt)
    }
}

impl<T: IntoIden> From<T> for WindowSelectType {
    fn from(iden: T) -> Self {
        Self::Name(iden.into_iden())
    }
}

/// Extension methods for building a [`SelectExpr`] from an expression.
///
/// This makes it ergonomic to attach select-specific modifiers (like `AS` and `OVER`) and pass the
/// result into [`SelectStatement::expr`].
///
/// # Examples
///
/// ```
/// use sea_query::{tests_cfg::*, *};
///
/// let query = Query::select()
///     .from(Char::Table)
///     .expr(
///         Expr::col(Char::Character)
///             .max()
///             .over(WindowStatement::partition_by(Char::FontSize))
///             .alias("C"),
///     )
///     .to_owned();
///
/// assert_eq!(
///     query.to_string(MysqlQueryBuilder),
///     r#"SELECT MAX(`character`) OVER ( PARTITION BY `font_size` ) AS `C` FROM `character`"#
/// );
/// ```
pub trait SelectExprTrait: Sized {
    fn alias<A>(self, alias: A) -> SelectExpr
    where
        A: IntoIden;

    fn over(self, over_expr: impl Into<WindowSelectType>) -> SelectExpr;
}

impl SelectExprTrait for SelectExpr {
    fn alias<A>(mut self, alias: A) -> SelectExpr
    where
        A: IntoIden,
    {
        self.alias = Some(alias.into_iden());
        self
    }

    fn over(mut self, over_expr: impl Into<WindowSelectType>) -> SelectExpr {
        self.window = Some(over_expr.into());
        self
    }
}

impl<T> SelectExprTrait for T
where
    T: Into<Expr>,
{
    fn alias<A>(self, alias: A) -> SelectExpr
    where
        A: IntoIden,
    {
        SelectExpr::from(self).alias(alias)
    }

    fn over(self, over_expr: impl Into<WindowSelectType>) -> SelectExpr {
        SelectExpr::from(self).over(over_expr)
    }
}
impl SelectStatement {
    /// Construct a new [`SelectStatement`]
    pub fn new() -> Self {
        Self::default()
    }

    /// Take the ownership of data in the current [`SelectStatement`]
    pub fn take(&mut self) -> Self {
        Self {
            distinct: self.distinct.take(),
            selects: std::mem::take(&mut self.selects),
            into: std::mem::take(&mut self.into),
            from: std::mem::take(&mut self.from),
            join: std::mem::take(&mut self.join),
            r#where: std::mem::replace(&mut self.r#where, ConditionHolder::new()),
            groups: std::mem::take(&mut self.groups),
            having: std::mem::replace(&mut self.having, ConditionHolder::new()),
            unions: std::mem::take(&mut self.unions),
            orders: std::mem::take(&mut self.orders),
            limit: self.limit.take(),
            offset: self.offset.take(),
            lock: self.lock.take(),
            window: self.window.take(),
            with: self.with.take(),
            #[cfg(feature = "backend-postgres")]
            table_sample: std::mem::take(&mut self.table_sample),
            #[cfg(feature = "backend-mysql")]
            index_hints: std::mem::take(&mut self.index_hints),
        }
    }

    /// A shorthand to express if ... else ... when constructing the select statement.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .conditions(
    ///         true,
    ///         |x| {
    ///             x.and_where(Expr::col(Char::FontId).eq(5));
    ///         },
    ///         |x| {
    ///             x.and_where(Expr::col(Char::FontId).eq(10));
    ///         },
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
    /// );
    /// ```
    pub fn conditions<T, F>(&mut self, b: bool, if_true: T, if_false: F) -> &mut Self
    where
        T: FnOnce(&mut Self),
        F: FnOnce(&mut Self),
    {
        if b {
            if_true(self)
        } else {
            if_false(self)
        }
        self
    }

    /// A shorthand to express if ... else ... when constructing the select statement.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .apply_if(Some(5), |q, v| {
    ///         q.and_where(Expr::col(Char::FontId).eq(v));
    ///     })
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
    /// );
    /// ```
    pub fn apply_if<T, F>(&mut self, val: Option<T>, if_some: F) -> &mut Self
    where
        Self: Sized,
        F: FnOnce(&mut Self, T),
    {
        if let Some(val) = val {
            if_some(self, val);
        }
        self
    }

    /// Construct part of the select statement in another function.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let common_expr = |q: &mut SelectStatement| {
    ///     q.and_where(Expr::col(Char::FontId).eq(5));
    /// };
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .apply(common_expr)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5"#
    /// );
    /// ```
    pub fn apply<F>(&mut self, func: F) -> &mut Self
    where
        F: FnOnce(&mut Self),
    {
        func(self);
        self
    }

    /// Clear the select list
    pub fn clear_selects(&mut self) -> &mut Self {
        self.selects = Vec::new();
        self
    }

    /// Add an expression to the select expression list.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .expr(Expr::val(42))
    ///     .expr(Expr::col(Char::Id).max())
    ///     .expr((1..10_i32).fold(Expr::value(0), |expr, i| expr.add(i)))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT 42, MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT 42, MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
    /// );
    /// ```
    pub fn expr<T>(&mut self, expr: T) -> &mut Self
    where
        T: Into<SelectExpr>,
    {
        self.selects.push(expr.into());
        self
    }

    /// Add select expressions from vector of [`SelectExpr`].
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .exprs([
    ///         Expr::col(Char::Id).max(),
    ///         (1..10_i32).fold(Expr::value(0), |expr, i| expr.add(i)),
    ///     ])
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT MAX(`id`), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT MAX("id"), 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 FROM "character""#
    /// );
    /// ```
    pub fn exprs<T, I>(&mut self, exprs: I) -> &mut Self
    where
        T: Into<SelectExpr>,
        I: IntoIterator<Item = T>,
    {
        self.selects
            .append(&mut exprs.into_iter().map(|c| c.into()).collect());
        self
    }

    pub fn exprs_mut_for_each<F>(&mut self, func: F)
    where
        F: FnMut(&mut SelectExpr),
    {
        self.selects.iter_mut().for_each(func);
    }

    /// Select distinct
    pub fn distinct(&mut self) -> &mut Self {
        self.distinct = Some(SelectDistinct::Distinct);
        self
    }

    /// Select distinct on for *POSTGRES ONLY*
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .distinct_on([Char::Character])
    ///     .column(Char::Character)
    ///     .column(Char::SizeW)
    ///     .column(Char::SizeH)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT DISTINCT ON ("character") "character", "size_w", "size_h" FROM "character""#
    /// )
    /// ```
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .distinct_on(vec![(Char::Table, Char::Character)])
    ///     .column(Char::Character)
    ///     .column(Char::SizeW)
    ///     .column(Char::SizeH)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT DISTINCT ON ("character"."character") "character", "size_w", "size_h" FROM "character""#
    /// )
    /// ```
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let distinct_cols: Vec<Character> = vec![];
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .distinct_on(distinct_cols)
    ///     .column(Char::Character)
    ///     .column(Char::SizeW)
    ///     .column(Char::SizeH)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "size_w", "size_h" FROM "character""#
    /// )
    /// ```
    pub fn distinct_on<T, I>(&mut self, cols: I) -> &mut Self
    where
        T: IntoColumnRef,
        I: IntoIterator<Item = T>,
    {
        let cols = cols
            .into_iter()
            .map(|col| col.into_column_ref())
            .collect::<Vec<ColumnRef>>();
        self.distinct = if !cols.is_empty() {
            Some(SelectDistinct::DistinctOn(cols))
        } else {
            None
        };
        self
    }

    /// Add a column to the select expression list.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .column(Char::Character)
    ///     .column(Char::SizeW)
    ///     .column(Char::SizeH)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "size_w", "size_h" FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "size_w", "size_h" FROM "character""#
    /// );
    /// ```
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .column((Char::Table, Char::Character))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`.`character` FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character"."character" FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character"."character" FROM "character""#
    /// );
    /// ```
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .column(("schema", Char::Table, Char::Character))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `schema`.`character`.`character` FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "schema"."character"."character" FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "schema"."character"."character" FROM "character""#
    /// );
    /// ```
    pub fn column<C>(&mut self, col: C) -> &mut Self
    where
        C: IntoColumnRef,
    {
        self.expr(Expr::Column(col.into_column_ref()))
    }

    /// Select columns.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .columns([Char::Character, Char::SizeW, Char::SizeH])
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `size_w`, `size_h` FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "size_w", "size_h" FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "size_w", "size_h" FROM "character""#
    /// );
    /// ```
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .columns([
    ///         (Char::Table, Char::Character),
    ///         (Char::Table, Char::SizeW),
    ///         (Char::Table, Char::SizeH),
    ///     ])
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`.`character`, `character`.`size_w`, `character`.`size_h` FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character"."character", "character"."size_w", "character"."size_h" FROM "character""#
    /// );
    /// ```
    pub fn columns<T, I>(&mut self, cols: I) -> &mut Self
    where
        T: IntoColumnRef,
        I: IntoIterator<Item = T>,
    {
        self.exprs(cols.into_iter().map(|c| Expr::Column(c.into_column_ref())))
    }

    /// Select column.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .expr_as(Expr::col(Char::Character), "C")
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` AS `C` FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" AS "C" FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" AS "C" FROM "character""#
    /// );
    /// ```
    pub fn expr_as<T, A>(&mut self, expr: T, alias: A) -> &mut Self
    where
        T: Into<Expr>,
        A: IntoIden,
    {
        self.expr(expr.alias(alias));
        self
    }

    /// Select column with window function.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .expr_window(
    ///         Expr::col(Char::Character).max(),
    ///         WindowStatement::partition_by(Char::FontSize),
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT MAX(`character`) OVER ( PARTITION BY `font_size` ) FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT MAX("character") OVER ( PARTITION BY "font_size" ) FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT MAX("character") OVER ( PARTITION BY "font_size" ) FROM "character""#
    /// );
    /// ```
    pub fn expr_window<T>(&mut self, expr: T, window: WindowStatement) -> &mut Self
    where
        T: Into<Expr>,
    {
        self.expr(expr.over(window));
        self
    }

    /// Select column with window function and label.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .expr_window_as(
    ///         Expr::col(Char::Character).max(),
    ///         WindowStatement::partition_by(Char::FontSize),
    ///         "C",
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT MAX(`character`) OVER ( PARTITION BY `font_size` ) AS `C` FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT MAX("character") OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT MAX("character") OVER ( PARTITION BY "font_size" ) AS "C" FROM "character""#
    /// );
    /// ```
    pub fn expr_window_as<T, A>(&mut self, expr: T, window: WindowStatement, alias: A) -> &mut Self
    where
        T: Into<Expr>,
        A: IntoIden,
    {
        self.expr(expr.over(window).alias(alias));
        self
    }

    /// Select column with window name.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .expr_window_name(Expr::col(Char::Character).max(), "w")
    ///     .window("w", WindowStatement::partition_by(Char::FontSize))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT MAX(`character`) OVER `w` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT MAX("character") OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT MAX("character") OVER "w" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
    /// );
    /// ```
    pub fn expr_window_name<T, W>(&mut self, expr: T, window: W) -> &mut Self
    where
        T: Into<Expr>,
        W: IntoIden,
    {
        self.expr(expr.over(window));
        self
    }

    /// Select column with window name and label.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .expr_window_name_as(Expr::col(Char::Character).max(), "w", "C")
    ///     .window("w", WindowStatement::partition_by(Char::FontSize))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT MAX(`character`) OVER `w` AS `C` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT MAX("character") OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT MAX("character") OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
    /// );
    /// ```
    pub fn expr_window_name_as<T, W, A>(&mut self, expr: T, window: W, alias: A) -> &mut Self
    where
        T: Into<Expr>,
        A: IntoIden,
        W: IntoIden,
    {
        self.expr(expr.over(window).alias(alias));
        self
    }

    /// Target table for SELECT INTO clause
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .column(Char::Character)
    ///     .into_table(SelectInto::table("character_copy").modifier(SelectIntoTableModifier::Unlogged))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" INTO UNLOGGED TABLE "character_copy" FROM "character""#
    /// );
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .column(Char::Character)
    ///     .into_table(
    ///         SelectInto::table("character_temp").modifier(SelectIntoTableModifier::Temporary),
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" INTO TEMPORARY TABLE "character_temp" FROM "character""#
    /// );
    /// ```
    pub fn into_table(&mut self, into_table: SelectInto) -> &mut Self {
        self.into = Some(into_table);
        self
    }

    /// From table.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::FontSize)
    ///     .from(Char::Table)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `font_size` FROM `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "font_size" FROM "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "font_size" FROM "character""#
    /// );
    /// ```
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::FontSize)
    ///     .from((Char::Table, Glyph::Table))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `font_size` FROM `character`.`glyph`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "font_size" FROM "character"."glyph""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "font_size" FROM "character"."glyph""#
    /// );
    /// ```
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::FontSize)
    ///     .from(("database", Char::Table, Glyph::Table))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `font_size` FROM `database`.`character`.`glyph`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "font_size" FROM "database"."character"."glyph""#
    /// );
    /// // NOTE: sqlite does not support database.schema.table references
    /// ```
    ///
    /// If you specify `from` multiple times, the resulting query will have multiple from clauses.
    /// You can perform an 'old-school' join this way.
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .expr(Expr::asterisk())
    ///     .from(Char::Table)
    ///     .from(Font::Table)
    ///     .and_where(Expr::col((Font::Table, Font::Id)).equals((Char::Table, Char::FontId)))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT * FROM `character`, `font` WHERE `font`.`id` = `character`.`font_id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT * FROM "character", "font" WHERE "font"."id" = "character"."font_id""#
    /// );
    /// ```
    pub fn from<R>(&mut self, tbl_ref: R) -> &mut Self
    where
        R: IntoTableRef,
    {
        self.from_from(tbl_ref.into_table_ref())
    }

    /// Shorthand for selecting from a constant value list.
    ///
    /// # Panics
    ///
    /// Panics on an empty values list.
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .expr(Expr::asterisk())
    ///     .from_values([(1, "hello"), (2, "world")], "x")
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT * FROM (VALUES ROW(1, 'hello'), ROW(2, 'world')) AS `x`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT * FROM (VALUES (1, 'hello'), (2, 'world')) AS "x""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT * FROM (VALUES (1, 'hello'), (2, 'world')) AS "x""#
    /// );
    /// ```
    pub fn from_values<I, V, A>(&mut self, value_tuples: I, alias: A) -> &mut Self
    where
        I: IntoIterator<Item = V>,
        V: IntoValueTuple,
        A: IntoIden,
    {
        let value_tuples: Vec<ValueTuple> = value_tuples
            .into_iter()
            .map(|vt| vt.into_value_tuple())
            .collect();
        assert!(!value_tuples.is_empty());
        self.from_from(TableRef::ValuesList(value_tuples, alias.into_iden()))
    }

    /// From table with alias.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from_as(Char::Table, "char")
    ///     .column(("char", Char::Character))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `char`.`character` FROM `character` AS `char`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "char"."character" FROM "character" AS "char""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "char"."character" FROM "character" AS "char""#
    /// );
    /// ```
    ///
    /// ```
    /// use sea_query::{audit::*, tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from_as((Font::Table, Char::Table), "alias")
    ///     .column(("alias", Char::Character))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `alias`.`character` FROM `font`.`character` AS `alias`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "alias"."character" FROM "font"."character" AS "alias""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "alias"."character" FROM "font"."character" AS "alias""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selects(),
    ///     [TableName(Some(Font::Table.into()), Char::Table.into_iden())]
    /// );
    /// ```
    pub fn from_as<R, A>(&mut self, tbl_ref: R, alias: A) -> &mut Self
    where
        R: IntoTableRef,
        A: IntoIden,
    {
        self.from_from(tbl_ref.into_table_ref().alias(alias.into_iden()))
    }

    /// From sub-query.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{audit::*, tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .columns([Glyph::Image])
    ///     .from_subquery(
    ///         Query::select()
    ///             .columns([Glyph::Image, Glyph::Aspect])
    ///             .from(Glyph::Table)
    ///             .take(),
    ///         "subglyph",
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `image` FROM (SELECT `image`, `aspect` FROM `glyph`) AS `subglyph`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "image" FROM (SELECT "image", "aspect" FROM "glyph") AS "subglyph""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "image" FROM (SELECT "image", "aspect" FROM "glyph") AS "subglyph""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Glyph::Table.into_iden()]
    /// );
    /// ```
    pub fn from_subquery<T>(&mut self, query: SelectStatement, alias: T) -> &mut Self
    where
        T: IntoIden,
    {
        self.from_from(TableRef::SubQuery(query.into(), alias.into_iden()))
    }

    /// From function call.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Asterisk)
    ///     .from_function(Func::random(), "func")
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT * FROM RAND() AS `func`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT * FROM RANDOM() AS "func""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT * FROM RANDOM() AS "func""#
    /// );
    /// ```
    pub fn from_function<T>(&mut self, func: FunctionCall, alias: T) -> &mut Self
    where
        T: IntoIden,
    {
        self.from_from(TableRef::FunctionCall(func, alias.into_iden()))
    }

    /// Clears all current from clauses.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Asterisk)
    ///     .from(Char::Table)
    ///     .from_clear()
    ///     .from(Font::Table)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT * FROM `font`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT * FROM "font""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT * FROM "font""#
    /// );
    /// ```
    pub fn from_clear(&mut self) -> &mut Self {
        self.from.clear();
        self
    }

    #[allow(clippy::wrong_self_convention)]
    fn from_from(&mut self, select: TableRef) -> &mut Self {
        self.from.push(select);
        self
    }

    /// Cross join.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{audit::*, tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .cross_join(Font::Table)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` CROSS JOIN `font`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" CROSS JOIN "font""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" CROSS JOIN "font""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Char::Table.into_iden(), Font::Table.into_iden()]
    /// );
    /// ```
    pub fn cross_join<R>(&mut self, tbl_ref: R) -> &mut Self
    where
        R: IntoTableRef,
    {
        self.push_join(JoinType::CrossJoin, tbl_ref.into_table_ref(), None, false);
        self
    }

    /// Left join.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{audit::*, tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .left_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Char::Table.into_iden(), Font::Table.into_iden()]
    /// );
    ///
    /// // Constructing chained join conditions
    /// let query = Query::select()
    ///         .column(Char::Character)
    ///         .column((Font::Table, Font::Name))
    ///         .from(Char::Table)
    ///         .left_join(
    ///             Font::Table,
    ///             all![
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///             ]
    ///         )
    ///         .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` LEFT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" LEFT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// ```
    pub fn left_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
    where
        R: IntoTableRef,
        C: IntoCondition,
    {
        self.join(JoinType::LeftJoin, tbl_ref, condition)
    }

    /// Right join.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .right_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    ///
    /// // Constructing chained join conditions
    /// let query = Query::select()
    ///         .column(Char::Character)
    ///         .column((Font::Table, Font::Name))
    ///         .from(Char::Table)
    ///         .right_join(
    ///             Font::Table,
    ///             all![
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///             ]
    ///         )
    ///         .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// ```
    pub fn right_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
    where
        R: IntoTableRef,
        C: IntoCondition,
    {
        self.join(JoinType::RightJoin, tbl_ref, condition)
    }

    /// Inner join.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .inner_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` INNER JOIN `font` ON `character`.`font_id` = `font`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    ///
    /// // Constructing chained join conditions
    /// let query = Query::select()
    ///         .column(Char::Character)
    ///         .column((Font::Table, Font::Name))
    ///         .from(Char::Table)
    ///         .inner_join(
    ///             Font::Table,
    ///             all![
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///             ]
    ///         )
    ///         .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` INNER JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" INNER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// ```
    pub fn inner_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
    where
        R: IntoTableRef,
        C: IntoCondition,
    {
        self.join(JoinType::InnerJoin, tbl_ref, condition)
    }

    /// Full outer join.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .full_outer_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    ///
    /// // Constructing chained join conditions
    /// let query = Query::select()
    ///         .column(Char::Character)
    ///         .column((Font::Table, Font::Name))
    ///         .from(Char::Table)
    ///         .full_outer_join(
    ///             Font::Table,
    ///             all![
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///             ]
    ///         )
    ///         .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" FULL OUTER JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// ```
    pub fn full_outer_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
    where
        R: IntoTableRef,
        C: IntoCondition,
    {
        self.join(JoinType::FullOuterJoin, tbl_ref, condition)
    }

    /// Straight join. MySQL only.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .straight_join(Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` STRAIGHT_JOIN `font` ON `character`.`font_id` = `font`.`id`"#
    /// );
    ///
    /// // Constructing chained join conditions
    /// let query = Query::select()
    ///         .column(Char::Character)
    ///         .column((Font::Table, Font::Name))
    ///         .from(Char::Table)
    ///         .straight_join(
    ///             Font::Table,
    ///             all![
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///             ]
    ///         )
    ///         .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` STRAIGHT_JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
    /// );
    /// ```
    pub fn straight_join<R, C>(&mut self, tbl_ref: R, condition: C) -> &mut Self
    where
        R: IntoTableRef,
        C: IntoCondition,
    {
        self.join(JoinType::StraightJoin, tbl_ref, condition)
    }

    /// Join with other table by [`JoinType`].
    ///
    /// If [`JoinType`] is [`CrossJoin`](JoinType::CrossJoin), the condition will be ignored.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{audit::*, tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Char::Table.into_iden(), Font::Table.into_iden()]
    /// );
    ///
    /// // Constructing chained join conditions
    /// let query = Query::select()
    ///         .column(Char::Character)
    ///         .column((Font::Table, Font::Name))
    ///         .from(Char::Table)
    ///         .join(
    ///             JoinType::RightJoin,
    ///             Font::Table,
    ///             all![
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///                 Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)),
    ///             ]
    ///         )
    ///         .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` AND `character`.`font_id` = `font`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" AND "character"."font_id" = "font"."id""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Char::Table.into_iden(), Font::Table.into_iden()]
    /// );
    /// ```
    pub fn join<R, C>(&mut self, join: JoinType, tbl_ref: R, condition: C) -> &mut Self
    where
        R: IntoTableRef,
        C: IntoCondition,
    {
        let on = match join {
            JoinType::CrossJoin => None,
            _ => Some(JoinOn::Condition(Box::new(
                ConditionHolder::new_with_condition(condition.into_condition()),
            ))),
        };
        self.push_join(join, tbl_ref.into_table_ref(), on, false)
    }

    /// Join with other table by [`JoinType`], assigning an alias to the joined table.
    ///
    /// If [`JoinType`] is [`CrossJoin`](JoinType::CrossJoin), the condition will be ignored.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column(("f", Font::Name))
    ///     .from(Char::Table)
    ///     .join_as(
    ///         JoinType::RightJoin,
    ///         Font::Table,
    ///         "f",
    ///         Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id))
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `f`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `f`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "f"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "f"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "f"."name" FROM "character" RIGHT JOIN "font" AS "f" ON "character"."font_id" = "f"."id""#
    /// );
    ///
    /// // Constructing chained join conditions
    /// assert_eq!(
    ///     Query::select()
    ///         .column(Char::Character)
    ///         .column(("f", Font::Name))
    ///         .from(Char::Table)
    ///         .join_as(
    ///             JoinType::RightJoin,
    ///             Font::Table,
    ///             "f",
    ///             Condition::all()
    ///                 .add(Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id)))
    ///                 .add(Expr::col((Char::Table, Char::FontId)).equals(("f", Font::Id)))
    ///         )
    ///         .to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `f`.`name` FROM `character` RIGHT JOIN `font` AS `f` ON `character`.`font_id` = `f`.`id` AND `character`.`font_id` = `f`.`id`"#
    /// );
    /// ```
    pub fn join_as<R, A, C>(
        &mut self,
        join: JoinType,
        tbl_ref: R,
        alias: A,
        condition: C,
    ) -> &mut Self
    where
        R: IntoTableRef,
        A: IntoIden,
        C: IntoCondition,
    {
        let on = match join {
            JoinType::CrossJoin => None,
            _ => Some(JoinOn::Condition(Box::new(
                ConditionHolder::new_with_condition(condition.into_condition()),
            ))),
        };
        self.push_join(
            join,
            tbl_ref.into_table_ref().alias(alias.into_iden()),
            on,
            false,
        )
    }

    /// Join with sub-query.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, audit::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Font::Name)
    ///     .from(Font::Table)
    ///     .join_subquery(
    ///         JoinType::LeftJoin,
    ///         Query::select().column(Glyph::Id).from(Glyph::Table).take(),
    ///         "sub_glyph",
    ///         Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id))
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `name` FROM `font` LEFT JOIN (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "name" FROM "font" LEFT JOIN (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "name" FROM "font" LEFT JOIN (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Font::Table.into_iden(), Glyph::Table.into_iden()]
    /// );
    ///
    /// // Constructing chained join conditions
    /// assert_eq!(
    ///     Query::select()
    ///         .column(Font::Name)
    ///         .from(Font::Table)
    ///         .join_subquery(
    ///             JoinType::LeftJoin,
    ///             Query::select().column(Glyph::Id).from(Glyph::Table).take(),
    ///             "sub_glyph",
    ///             Condition::all()
    ///                 .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
    ///                 .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
    ///         )
    ///         .to_string(MysqlQueryBuilder),
    ///     r#"SELECT `name` FROM `font` LEFT JOIN (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id` AND `font`.`id` = `sub_glyph`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Font::Table.into_iden(), Glyph::Table.into_iden()]
    /// );
    /// ```
    pub fn join_subquery<T, C>(
        &mut self,
        join: JoinType,
        query: SelectStatement,
        alias: T,
        condition: C,
    ) -> &mut Self
    where
        T: IntoIden,
        C: IntoCondition,
    {
        self.push_join(
            join,
            TableRef::SubQuery(query.into(), alias.into_iden()),
            JoinOn::Condition(Box::new(ConditionHolder::new_with_condition(
                condition.into_condition(),
            ))),
            false,
        )
    }

    /// Join Lateral with sub-query. Not supported by SQLite.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{audit::*, tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Font::Name)
    ///     .from(Font::Table)
    ///     .join_lateral(
    ///         JoinType::LeftJoin,
    ///         Query::select().column(Glyph::Id).from(Glyph::Table).take(),
    ///         "sub_glyph",
    ///         Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id))
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `name` FROM `font` LEFT JOIN LATERAL (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "name" FROM "font" LEFT JOIN LATERAL (SELECT "id" FROM "glyph") AS "sub_glyph" ON "font"."id" = "sub_glyph"."id""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Font::Table.into_iden(), Glyph::Table.into_iden()]
    /// );
    ///
    /// // Constructing chained join conditions
    /// assert_eq!(
    ///     Query::select()
    ///         .column(Font::Name)
    ///         .from(Font::Table)
    ///         .join_lateral(
    ///             JoinType::LeftJoin,
    ///             Query::select().column(Glyph::Id).from(Glyph::Table).take(),
    ///             "sub_glyph",
    ///             Condition::all()
    ///                 .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
    ///                 .add(Expr::col((Font::Table, Font::Id)).equals(("sub_glyph", Glyph::Id)))
    ///         )
    ///         .to_string(MysqlQueryBuilder),
    ///     r#"SELECT `name` FROM `font` LEFT JOIN LATERAL (SELECT `id` FROM `glyph`) AS `sub_glyph` ON `font`.`id` = `sub_glyph`.`id` AND `font`.`id` = `sub_glyph`.`id`"#
    /// );
    /// ```
    pub fn join_lateral<T, C>(
        &mut self,
        join: JoinType,
        query: SelectStatement,
        alias: T,
        condition: C,
    ) -> &mut Self
    where
        T: IntoIden,
        C: IntoCondition,
    {
        self.push_join(
            join,
            TableRef::SubQuery(query.into(), alias.into_iden()),
            JoinOn::Condition(Box::new(ConditionHolder::new_with_condition(
                condition.into_condition(),
            ))),
            true,
        )
    }

    fn push_join(
        &mut self,
        join: JoinType,
        table: TableRef,
        on: impl Into<Option<JoinOn>>,
        lateral: bool,
    ) -> &mut Self {
        self.join.push(JoinExpr {
            join,
            table: Box::new(table),
            on: on.into(),
            lateral,
        });
        self
    }

    /// Group by columns.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    ///     .group_by_columns([
    ///         Char::Character,
    ///     ])
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character""#
    /// );
    /// ```
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    ///     .group_by_columns([
    ///         (Char::Table, Char::Character),
    ///     ])
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`.`character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
    /// );
    /// ```
    pub fn group_by_columns<T, I>(&mut self, cols: I) -> &mut Self
    where
        T: IntoColumnRef,
        I: IntoIterator<Item = T>,
    {
        self.add_group_by(cols.into_iter().map(|c| Expr::Column(c.into_column_ref())))
    }

    /// Add a group by column.
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .column((Font::Table, Font::Name))
    ///     .from(Char::Table)
    ///     .join(JoinType::RightJoin, Font::Table, Expr::col((Char::Table, Char::FontId)).equals((Font::Table, Font::Id)))
    ///     .group_by_col((Char::Table, Char::Character))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character`, `font`.`name` FROM `character` RIGHT JOIN `font` ON `character`.`font_id` = `font`.`id` GROUP BY `character`.`character`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character", "font"."name" FROM "character" RIGHT JOIN "font" ON "character"."font_id" = "font"."id" GROUP BY "character"."character""#
    /// );
    /// ```
    pub fn group_by_col<T>(&mut self, col: T) -> &mut Self
    where
        T: IntoColumnRef,
    {
        self.group_by_columns([col])
    }

    /// Add group by expressions from vector of [`SelectExpr`].
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .column(Char::Character)
    ///     .add_group_by([Expr::col(Char::SizeW).into(), Expr::col(Char::SizeH).into()])
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` GROUP BY `size_w`, `size_h`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" GROUP BY "size_w", "size_h""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" GROUP BY "size_w", "size_h""#
    /// );
    /// ```
    pub fn add_group_by<I>(&mut self, expr: I) -> &mut Self
    where
        I: IntoIterator<Item = Expr>,
    {
        self.groups.append(&mut expr.into_iter().collect());
        self
    }

    /// Having condition, expressed with [`any!`](crate::any) and [`all!`](crate::all).
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Glyph::Aspect)
    ///     .expr(Expr::col(Glyph::Image).max())
    ///     .from(Glyph::Table)
    ///     .group_by_columns([
    ///         Glyph::Aspect,
    ///     ])
    ///     .cond_having(
    ///         all![
    ///             Expr::col((Glyph::Table, Glyph::Aspect)).is_in([3, 4]),
    ///             any![
    ///                 Expr::col((Glyph::Table, Glyph::Image)).like("A%"),
    ///                 Expr::col((Glyph::Table, Glyph::Image)).like("B%")
    ///             ]
    ///         ]
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `aspect`, MAX(`image`) FROM `glyph` GROUP BY `aspect` HAVING `glyph`.`aspect` IN (3, 4) AND (`glyph`.`image` LIKE 'A%' OR `glyph`.`image` LIKE 'B%')"#
    /// );
    /// ```
    pub fn cond_having<C>(&mut self, condition: C) -> &mut Self
    where
        C: IntoCondition,
    {
        self.having.add_condition(condition.into_condition());
        self
    }

    /// And having condition.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, tests_cfg::*};
    ///
    /// let query = Query::select()
    ///     .column(Glyph::Aspect)
    ///     .expr(Expr::col(Glyph::Image).max())
    ///     .from(Glyph::Table)
    ///     .group_by_columns([
    ///         Glyph::Aspect,
    ///     ])
    ///     .and_having(Expr::col(Glyph::Aspect).gt(2))
    ///     .cond_having(Expr::col(Glyph::Aspect).lt(8))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `aspect`, MAX(`image`) FROM `glyph` GROUP BY `aspect` HAVING `aspect` > 2 AND `aspect` < 8"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 AND "aspect" < 8"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "aspect", MAX("image") FROM "glyph" GROUP BY "aspect" HAVING "aspect" > 2 AND "aspect" < 8"#
    /// );
    /// ```
    pub fn and_having(&mut self, other: Expr) -> &mut Self {
        self.cond_having(other)
    }

    /// Limit the number of returned rows.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Glyph::Aspect)
    ///     .from(Glyph::Table)
    ///     .limit(10)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `aspect` FROM `glyph` LIMIT 10"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "aspect" FROM "glyph" LIMIT 10"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "aspect" FROM "glyph" LIMIT 10"#
    /// );
    /// ```
    pub fn limit(&mut self, limit: u64) -> &mut Self {
        self.limit = Some(limit.into());
        self
    }

    /// Reset limit
    pub fn reset_limit(&mut self) -> &mut Self {
        self.limit = None;
        self
    }

    /// Offset number of returned rows.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Glyph::Aspect)
    ///     .from(Glyph::Table)
    ///     .limit(10)
    ///     .offset(10)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `aspect` FROM `glyph` LIMIT 10 OFFSET 10"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "aspect" FROM "glyph" LIMIT 10 OFFSET 10"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "aspect" FROM "glyph" LIMIT 10 OFFSET 10"#
    /// );
    /// ```
    pub fn offset(&mut self, offset: u64) -> &mut Self {
        self.offset = Some(offset.into());
        self
    }

    /// Reset offset
    pub fn reset_offset(&mut self) -> &mut Self {
        self.offset = None;
        self
    }

    /// Row locking (if supported).
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .and_where(Expr::col(Char::FontId).eq(5))
    ///     .lock(LockType::Update)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
    /// );
    /// ```
    pub fn lock(&mut self, r#type: LockType) -> &mut Self {
        self.lock = Some(LockClause {
            r#type,
            tables: Vec::new(),
            behavior: None,
        });
        self
    }

    /// Row locking with tables (if supported).
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .and_where(Expr::col(Char::FontId).eq(5))
    ///     .lock_with_tables(LockType::Update, [Glyph::Table])
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE OF `glyph`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE OF "glyph""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
    /// );
    /// ```
    pub fn lock_with_tables<T, I>(&mut self, r#type: LockType, tables: I) -> &mut Self
    where
        T: IntoTableRef,
        I: IntoIterator<Item = T>,
    {
        self.lock = Some(LockClause {
            r#type,
            tables: tables.into_iter().map(|t| t.into_table_ref()).collect(),
            behavior: None,
        });
        self
    }

    /// Row locking with behavior (if supported).
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .and_where(Expr::col(Char::FontId).eq(5))
    ///     .lock_with_behavior(LockType::Update, LockBehavior::Nowait)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE NOWAIT"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE NOWAIT"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
    /// );
    /// ```
    pub fn lock_with_behavior(&mut self, r#type: LockType, behavior: LockBehavior) -> &mut Self {
        self.lock = Some(LockClause {
            r#type,
            tables: Vec::new(),
            behavior: Some(behavior),
        });
        self
    }

    /// Row locking with tables and behavior (if supported).
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .and_where(Expr::col(Char::FontId).eq(5))
    ///     .lock_with_tables_behavior(LockType::Update, [Glyph::Table], LockBehavior::Nowait)
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE OF `glyph` NOWAIT"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE OF "glyph" NOWAIT"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
    /// );
    /// ```
    pub fn lock_with_tables_behavior<T, I>(
        &mut self,
        r#type: LockType,
        tables: I,
        behavior: LockBehavior,
    ) -> &mut Self
    where
        T: IntoTableRef,
        I: IntoIterator<Item = T>,
    {
        self.lock = Some(LockClause {
            r#type,
            tables: tables.into_iter().map(|t| t.into_table_ref()).collect(),
            behavior: Some(behavior),
        });
        self
    }

    /// Shared row locking (if supported).
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .and_where(Expr::col(Char::FontId).eq(5))
    ///     .lock_shared()
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5 LOCK IN SHARE MODE"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR SHARE"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
    /// );
    /// ```
    pub fn lock_shared(&mut self) -> &mut Self {
        self.lock(LockType::Share)
    }

    /// Exclusive row locking (if supported).
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .and_where(Expr::col(Char::FontId).eq(5))
    ///     .lock_exclusive()
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5 FOR UPDATE"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 FOR UPDATE"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 "#
    /// );
    /// ```
    pub fn lock_exclusive(&mut self) -> &mut Self {
        self.lock(LockType::Update)
    }

    /// Union with another SelectStatement that must have the same selected fields.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{audit::*, tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .and_where(Expr::col(Char::FontId).eq(5))
    ///     .union(UnionType::All, Query::select()
    ///         .column(Char::Character)
    ///         .from(Char::Table)
    ///         .and_where(Expr::col(Char::FontId).eq(4))
    ///         .to_owned()
    ///     )
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5 UNION ALL (SELECT `character` FROM `character` WHERE `font_id` = 4)"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL (SELECT "character" FROM "character" WHERE "font_id" = 4)"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL SELECT "character" FROM "character" WHERE "font_id" = 4"#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Char::Table.into_iden()]
    /// );
    /// ```
    pub fn union(&mut self, union_type: UnionType, query: SelectStatement) -> &mut Self {
        self.unions.push((union_type, query));
        self
    }

    /// Union with multiple SelectStatement that must have the same selected fields.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{audit::*, tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .column(Char::Character)
    ///     .from(Char::Table)
    ///     .and_where(Expr::col(Char::FontId).eq(5))
    ///     .unions([
    ///         (UnionType::All, Query::select()
    ///             .column(Char::Character)
    ///             .from(Char::Table)
    ///             .and_where(Expr::col(Char::FontId).eq(4))
    ///             .to_owned()),
    ///         (UnionType::Distinct, Query::select()
    ///             .column(Glyph::Image)
    ///             .from(Glyph::Table)
    ///             .to_owned()),
    ///     ])
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT `character` FROM `character` WHERE `font_id` = 5 UNION ALL (SELECT `character` FROM `character` WHERE `font_id` = 4) UNION (SELECT `image` FROM `glyph`)"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL (SELECT "character" FROM "character" WHERE "font_id" = 4) UNION (SELECT "image" FROM "glyph")"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT "character" FROM "character" WHERE "font_id" = 5 UNION ALL SELECT "character" FROM "character" WHERE "font_id" = 4 UNION SELECT "image" FROM "glyph""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Char::Table.into_iden(), Glyph::Table.into_iden()]
    /// );
    /// ```
    pub fn unions<T: IntoIterator<Item = (UnionType, SelectStatement)>>(
        &mut self,
        unions: T,
    ) -> &mut Self {
        self.unions.extend(unions);
        self
    }

    /// Create a [WithQuery] by specifying a [WithClause] to execute this query with.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, IntoCondition, IntoIden, audit::AuditTrait, tests_cfg::*};
    ///
    /// let base_query = SelectStatement::new()
    ///                     .column("id")
    ///                     .expr(1i32)
    ///                     .column("next")
    ///                     .column("value")
    ///                     .from(Task::Table)
    ///                     .to_owned();
    ///
    /// let cte_referencing = SelectStatement::new()
    ///                             .column("id")
    ///                             .expr(Expr::col("depth").add(1i32))
    ///                             .column("next")
    ///                             .column("value")
    ///                             .from(Task::Table)
    ///                             .join(
    ///                                 JoinType::InnerJoin,
    ///                                 "cte_traversal",
    ///                                 Expr::col(("cte_traversal", "next")).equals((Task::Table, "id"))
    ///                             )
    ///                             .to_owned();
    ///
    /// let common_table_expression = CommonTableExpression::new()
    ///             .query(
    ///                 base_query.clone().union(UnionType::All, cte_referencing).to_owned()
    ///             )
    ///             .columns(["id", "depth", "next", "value"])
    ///             .table_name("cte_traversal")
    ///             .to_owned();
    ///
    /// let select = SelectStatement::new()
    ///         .column(Asterisk)
    ///         .from("cte_traversal")
    ///         .to_owned();
    ///
    /// let with_clause = WithClause::new()
    ///         .recursive(true)
    ///         .cte(common_table_expression)
    ///         .cycle(Cycle::new_from_expr_set_using(Expr::Column("id".into_column_ref()), "looped", "traversal_path"))
    ///         .to_owned();
    ///
    /// let query = select.with(with_clause).to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"WITH RECURSIVE `cte_traversal` (`id`, `depth`, `next`, `value`) AS (SELECT `id`, 1, `next`, `value` FROM `task` UNION ALL (SELECT `id`, `depth` + 1, `next`, `value` FROM `task` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `task`.`id`)) SELECT * FROM `cte_traversal`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL (SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id")) CYCLE "id" SET "looped" USING "traversal_path" SELECT * FROM "cte_traversal""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id") SELECT * FROM "cte_traversal""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Task::Table.into_iden()]
    /// );
    /// ```
    pub fn with(self, clause: WithClause) -> WithQuery {
        clause.query(self)
    }

    /// Create a Common Table Expression by specifying a [CommonTableExpression][crate::CommonTableExpression]
    /// or [WithClause] to execute this query with.
    ///
    /// # Examples
    ///
    /// ```
    /// use sea_query::{*, IntoCondition, IntoIden, audit::AuditTrait, tests_cfg::*};
    ///
    /// let base_query = SelectStatement::new()
    ///                     .column("id")
    ///                     .expr(1i32)
    ///                     .column("next")
    ///                     .column("value")
    ///                     .from(Task::Table)
    ///                     .to_owned();
    ///
    /// let cte_referencing = SelectStatement::new()
    ///                             .column("id")
    ///                             .expr(Expr::col("depth").add(1i32))
    ///                             .column("next")
    ///                             .column("value")
    ///                             .from(Task::Table)
    ///                             .join(
    ///                                 JoinType::InnerJoin,
    ///                                 "cte_traversal",
    ///                                 Expr::col(("cte_traversal", "next")).equals((Task::Table, "id"))
    ///                             )
    ///                             .to_owned();
    ///
    /// let common_table_expression = CommonTableExpression::new()
    ///             .query(
    ///                 base_query.clone().union(UnionType::All, cte_referencing).to_owned()
    ///             )
    ///             .columns(["id", "depth", "next", "value"])
    ///             .table_name("cte_traversal")
    ///             .to_owned();
    ///
    /// let with_clause = WithClause::new()
    ///         .recursive(true)
    ///         .cte(common_table_expression)
    ///         .cycle(Cycle::new_from_expr_set_using(Expr::Column("id".into_column_ref()), "looped", "traversal_path"))
    ///         .to_owned();
    ///
    /// let query = SelectStatement::new()
    ///         .column(Asterisk)
    ///         .from("cte_traversal")
    ///         .with_cte(with_clause)
    ///         .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"WITH RECURSIVE `cte_traversal` (`id`, `depth`, `next`, `value`) AS (SELECT `id`, 1, `next`, `value` FROM `task` UNION ALL (SELECT `id`, `depth` + 1, `next`, `value` FROM `task` INNER JOIN `cte_traversal` ON `cte_traversal`.`next` = `task`.`id`)) SELECT * FROM `cte_traversal`"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL (SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id")) CYCLE "id" SET "looped" USING "traversal_path" SELECT * FROM "cte_traversal""#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"WITH RECURSIVE "cte_traversal" ("id", "depth", "next", "value") AS (SELECT "id", 1, "next", "value" FROM "task" UNION ALL SELECT "id", "depth" + 1, "next", "value" FROM "task" INNER JOIN "cte_traversal" ON "cte_traversal"."next" = "task"."id") SELECT * FROM "cte_traversal""#
    /// );
    /// assert_eq!(
    ///     query.audit().unwrap().selected_tables(),
    ///     [Task::Table.into_iden()]
    /// );
    /// ```
    pub fn with_cte<C: Into<WithClause>>(&mut self, clause: C) -> &mut Self {
        self.with = Some(Box::new(clause.into()));
        self
    }

    /// WINDOW
    ///
    /// # Examples:
    ///
    /// ```
    /// use sea_query::{tests_cfg::*, *};
    ///
    /// let query = Query::select()
    ///     .from(Char::Table)
    ///     .expr_window_name_as(Expr::col(Char::Character).max(), "w", "C")
    ///     .window("w", WindowStatement::partition_by(Char::FontSize))
    ///     .to_owned();
    ///
    /// assert_eq!(
    ///     query.to_string(MysqlQueryBuilder),
    ///     r#"SELECT MAX(`character`) OVER `w` AS `C` FROM `character` WINDOW `w` AS (PARTITION BY `font_size`)"#
    /// );
    /// assert_eq!(
    ///     query.to_string(PostgresQueryBuilder),
    ///     r#"SELECT MAX("character") OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
    /// );
    /// assert_eq!(
    ///     query.to_string(SqliteQueryBuilder),
    ///     r#"SELECT MAX("character") OVER "w" AS "C" FROM "character" WINDOW "w" AS (PARTITION BY "font_size")"#
    /// );
    /// ```
    pub fn window<A>(&mut self, name: A, window: WindowStatement) -> &mut Self
    where
        A: IntoIden,
    {
        self.window = Some((name.into_iden(), window));
        self
    }
}

impl QueryStatementBuilder for SelectStatement {
    fn build_collect_any_into(&self, query_builder: &impl QueryBuilder, sql: &mut impl SqlWriter) {
        query_builder.prepare_select_statement(self, sql);
    }
}

impl SelectStatement {
    pub fn build_any(&self, query_builder: &impl QueryBuilder) -> (String, Values) {
        <Self as QueryStatementBuilder>::build_any(self, query_builder)
    }

    pub fn build_collect_any(
        &self,
        query_builder: &impl QueryBuilder,
        sql: &mut impl SqlWriter,
    ) -> String {
        <Self as QueryStatementBuilder>::build_collect_any(self, query_builder, sql)
    }

    pub fn build_collect_any_into(
        &self,
        query_builder: &impl QueryBuilder,
        sql: &mut impl SqlWriter,
    ) {
        <Self as QueryStatementBuilder>::build_collect_any_into(self, query_builder, sql)
    }
}

impl From<SelectStatement> for QueryStatement {
    fn from(s: SelectStatement) -> Self {
        Self::Select(s)
    }
}

impl From<SelectStatement> for SubQueryStatement {
    fn from(s: SelectStatement) -> Self {
        Self::SelectStatement(s)
    }
}

impl QueryStatementWriter for SelectStatement {
    fn build_collect_into<T: QueryBuilder>(&self, query_builder: T, sql: &mut impl SqlWriter) {
        query_builder.prepare_select_statement(self, sql);
    }
}

impl SelectStatement {
    pub fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String {
        <Self as QueryStatementWriter>::to_string(self, query_builder)
    }

    pub fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values) {
        <Self as QueryStatementWriter>::build(self, query_builder)
    }

    pub fn build_collect<T: QueryBuilder>(
        &self,
        query_builder: T,
        sql: &mut impl SqlWriter,
    ) -> String {
        <Self as QueryStatementWriter>::build_collect(self, query_builder, sql)
    }

    pub fn build_collect_into<T: QueryBuilder>(&self, query_builder: T, sql: &mut impl SqlWriter) {
        <Self as QueryStatementWriter>::build_collect_into(self, query_builder, sql);
    }
}

impl OrderedStatement for SelectStatement {
    fn add_order_by(&mut self, order: OrderExpr) -> &mut Self {
        self.orders.push(order);
        self
    }

    fn clear_order_by(&mut self) -> &mut Self {
        self.orders = Vec::new();
        self
    }
}

impl SelectStatement {
    pub fn add_order_by(&mut self, order: OrderExpr) -> &mut Self {
        <Self as OrderedStatement>::add_order_by(self, order)
    }

    pub fn clear_order_by(&mut self) -> &mut Self {
        <Self as OrderedStatement>::clear_order_by(self)
    }

    pub fn order_by<T: IntoColumnRef>(&mut self, col: T, order: Order) -> &mut Self {
        <Self as OrderedStatement>::order_by(self, col, order)
    }

    pub fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self {
        <Self as OrderedStatement>::order_by_expr(self, expr, order)
    }

    pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self
    where
        T: Into<Cow<'static, str>>,
        I: IntoIterator<Item = (T, Order)>,
    {
        <Self as OrderedStatement>::order_by_customs(self, cols)
    }

    pub fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self
    where
        T: IntoColumnRef,
        I: IntoIterator<Item = (T, Order)>,
    {
        <Self as OrderedStatement>::order_by_columns(self, cols)
    }

    pub fn order_by_with_nulls<T: IntoColumnRef>(
        &mut self,
        col: T,
        order: Order,
        nulls: NullOrdering,
    ) -> &mut Self {
        <Self as OrderedStatement>::order_by_with_nulls(self, col, order, nulls)
    }

    pub fn order_by_expr_with_nulls(
        &mut self,
        expr: Expr,
        order: Order,
        nulls: NullOrdering,
    ) -> &mut Self {
        <Self as OrderedStatement>::order_by_expr_with_nulls(self, expr, order, nulls)
    }

    pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
    where
        T: Into<Cow<'static, str>>,
        I: IntoIterator<Item = (T, Order, NullOrdering)>,
    {
        <Self as OrderedStatement>::order_by_customs_with_nulls(self, cols)
    }

    pub fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
    where
        T: IntoColumnRef,
        I: IntoIterator<Item = (T, Order, NullOrdering)>,
    {
        <Self as OrderedStatement>::order_by_columns_with_nulls(self, cols)
    }
}

impl ConditionalStatement for SelectStatement {
    fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self {
        self.r#where.add_and_or(condition);
        self
    }

    fn cond_where<C>(&mut self, condition: C) -> &mut Self
    where
        C: IntoCondition,
    {
        self.r#where.add_condition(condition.into_condition());
        self
    }
}

impl SelectStatement {
    pub fn and_where(&mut self, other: Expr) -> &mut Self {
        <Self as ConditionalStatement>::and_where(self, other)
    }

    pub fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self {
        <Self as ConditionalStatement>::and_where_option(self, other)
    }

    pub fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self {
        <Self as ConditionalStatement>::and_or_where(self, condition)
    }

    pub fn cond_where<C: IntoCondition>(&mut self, condition: C) -> &mut Self {
        <Self as ConditionalStatement>::cond_where(self, condition)
    }
}
