sea_query/query/
delete.rs

1use std::borrow::Cow;
2
3use crate::{
4    ConditionHolder, ConditionalStatement, Expr, IntoColumnRef, IntoCondition, IntoTableRef,
5    LogicalChainOper, NullOrdering, Order, OrderExpr, OrderedStatement, QueryBuilder,
6    QueryStatement, QueryStatementBuilder, QueryStatementWriter, ReturningClause, SqlWriter,
7    SubQueryStatement, TableRef, Value, Values, WithClause, WithQuery,
8};
9
10/// Delete existing rows from the table
11///
12/// # Examples
13///
14/// ```
15/// use sea_query::{tests_cfg::*, *};
16///
17/// let query = Query::delete()
18///     .from_table(Glyph::Table)
19///     .cond_where(any![
20///         Expr::col(Glyph::Id).lt(1),
21///         Expr::col(Glyph::Id).gt(10),
22///     ])
23///     .to_owned();
24///
25/// assert_eq!(
26///     query.to_string(MysqlQueryBuilder),
27///     r#"DELETE FROM `glyph` WHERE `id` < 1 OR `id` > 10"#
28/// );
29/// assert_eq!(
30///     query.to_string(PostgresQueryBuilder),
31///     r#"DELETE FROM "glyph" WHERE "id" < 1 OR "id" > 10"#
32/// );
33/// assert_eq!(
34///     query.to_string(SqliteQueryBuilder),
35///     r#"DELETE FROM "glyph" WHERE "id" < 1 OR "id" > 10"#
36/// );
37/// ```
38#[derive(Default, Debug, Clone, PartialEq)]
39pub struct DeleteStatement {
40    pub(crate) table: Option<Box<TableRef>>,
41    pub(crate) r#where: ConditionHolder,
42    pub(crate) orders: Vec<OrderExpr>,
43    pub(crate) limit: Option<Value>,
44    pub(crate) returning: Option<ReturningClause>,
45    pub(crate) with: Option<WithClause>,
46}
47
48impl DeleteStatement {
49    /// Construct a new [`DeleteStatement`]
50    pub fn new() -> Self {
51        Self::default()
52    }
53
54    pub fn take(&mut self) -> Self {
55        Self {
56            table: self.table.take(),
57            r#where: std::mem::take(&mut self.r#where),
58            orders: std::mem::take(&mut self.orders),
59            limit: self.limit.take(),
60            returning: self.returning.take(),
61            with: self.with.take(),
62        }
63    }
64
65    /// Specify which table to delete from.
66    ///
67    /// # Examples
68    ///
69    /// ```
70    /// use sea_query::{audit::*, tests_cfg::*, *};
71    ///
72    /// let query = Query::delete()
73    ///     .from_table(Glyph::Table)
74    ///     .and_where(Expr::col(Glyph::Id).eq(1))
75    ///     .to_owned();
76    ///
77    /// assert_eq!(
78    ///     query.to_string(MysqlQueryBuilder),
79    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
80    /// );
81    /// assert_eq!(
82    ///     query.to_string(PostgresQueryBuilder),
83    ///     r#"DELETE FROM "glyph" WHERE "id" = 1"#
84    /// );
85    /// assert_eq!(
86    ///     query.to_string(SqliteQueryBuilder),
87    ///     r#"DELETE FROM "glyph" WHERE "id" = 1"#
88    /// );
89    /// assert_eq!(
90    ///     query.audit_unwrap().deleted_tables(),
91    ///     [Glyph::Table.into_iden()]
92    /// );
93    /// assert_eq!(query.audit_unwrap().selected_tables(), []);
94    /// ```
95    #[allow(clippy::wrong_self_convention)]
96    pub fn from_table<T>(&mut self, tbl_ref: T) -> &mut Self
97    where
98        T: IntoTableRef,
99    {
100        self.table = Some(Box::new(tbl_ref.into_table_ref()));
101        self
102    }
103
104    /// Limit number of updated rows.
105    pub fn limit(&mut self, limit: u64) -> &mut Self {
106        self.limit = Some(limit.into());
107        self
108    }
109
110    /// RETURNING expressions.
111    ///
112    /// # Examples
113    ///
114    /// ```
115    /// use sea_query::{audit::*, tests_cfg::*, *};
116    ///
117    /// let query = Query::delete()
118    ///     .from_table(Glyph::Table)
119    ///     .and_where(Expr::col(Glyph::Id).eq(1))
120    ///     .returning(Query::returning().columns([Glyph::Id]))
121    ///     .to_owned();
122    ///
123    /// assert_eq!(
124    ///     query.to_string(MysqlQueryBuilder),
125    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
126    /// );
127    /// assert_eq!(
128    ///     query.to_string(PostgresQueryBuilder),
129    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
130    /// );
131    /// assert_eq!(
132    ///     query.to_string(SqliteQueryBuilder),
133    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
134    /// );
135    /// assert_eq!(
136    ///     query.audit_unwrap().deleted_tables(),
137    ///     [Glyph::Table.into_iden()]
138    /// );
139    /// assert_eq!(
140    ///     query.audit_unwrap().selected_tables(),
141    ///     [Glyph::Table.into_iden()]
142    /// );
143    /// ```
144    pub fn returning(&mut self, returning_cols: ReturningClause) -> &mut Self {
145        self.returning = Some(returning_cols);
146        self
147    }
148
149    /// RETURNING expressions for a column.
150    ///
151    /// # Examples
152    ///
153    /// ```
154    /// use sea_query::{tests_cfg::*, *};
155    ///
156    /// let query = Query::delete()
157    ///     .from_table(Glyph::Table)
158    ///     .and_where(Expr::col(Glyph::Id).eq(1))
159    ///     .returning_col(Glyph::Id)
160    ///     .to_owned();
161    ///
162    /// assert_eq!(
163    ///     query.to_string(MysqlQueryBuilder),
164    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
165    /// );
166    /// assert_eq!(
167    ///     query.to_string(PostgresQueryBuilder),
168    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
169    /// );
170    /// assert_eq!(
171    ///     query.to_string(SqliteQueryBuilder),
172    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
173    /// );
174    /// ```
175    pub fn returning_col<C>(&mut self, col: C) -> &mut Self
176    where
177        C: IntoColumnRef,
178    {
179        self.returning(ReturningClause::Columns(vec![col.into_column_ref()]))
180    }
181
182    /// RETURNING expressions all columns.
183    ///
184    /// # Examples
185    ///
186    /// ```
187    /// use sea_query::{tests_cfg::*, *};
188    ///
189    /// let query = Query::delete()
190    ///     .from_table(Glyph::Table)
191    ///     .and_where(Expr::col(Glyph::Id).eq(1))
192    ///     .returning_all()
193    ///     .to_owned();
194    ///
195    /// assert_eq!(
196    ///     query.to_string(MysqlQueryBuilder),
197    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
198    /// );
199    /// assert_eq!(
200    ///     query.to_string(PostgresQueryBuilder),
201    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING *"#
202    /// );
203    /// assert_eq!(
204    ///     query.to_string(SqliteQueryBuilder),
205    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING *"#
206    /// );
207    /// ```
208    pub fn returning_all(&mut self) -> &mut Self {
209        self.returning(ReturningClause::All)
210    }
211
212    /// Create a [WithQuery] by specifying a [WithClause] to execute this query with.
213    ///
214    /// # Examples
215    ///
216    /// ```
217    /// use sea_query::{IntoCondition, IntoIden, audit::*, tests_cfg::*, *};
218    ///
219    /// let select = SelectStatement::new()
220    ///         .columns([Glyph::Id])
221    ///         .from(Glyph::Table)
222    ///         .and_where(Expr::col(Glyph::Image).like("0%"))
223    ///         .to_owned();
224    ///     let cte = CommonTableExpression::new()
225    ///         .query(select)
226    ///         .column(Glyph::Id)
227    ///         .table_name("cte")
228    ///         .to_owned();
229    ///     let with_clause = WithClause::new().cte(cte).to_owned();
230    ///     let update = DeleteStatement::new()
231    ///         .from_table(Glyph::Table)
232    ///         .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from("cte").to_owned()))
233    ///         .to_owned();
234    ///     let query = update.with(with_clause);
235    ///
236    /// assert_eq!(
237    ///     query.to_string(MysqlQueryBuilder),
238    ///     r#"WITH `cte` (`id`) AS (SELECT `id` FROM `glyph` WHERE `image` LIKE '0%') DELETE FROM `glyph` WHERE `id` IN (SELECT `id` FROM `cte`)"#
239    /// );
240    /// assert_eq!(
241    ///     query.to_string(PostgresQueryBuilder),
242    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
243    /// );
244    /// assert_eq!(
245    ///     query.to_string(SqliteQueryBuilder),
246    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
247    /// );
248    /// assert_eq!(
249    ///     query.audit_unwrap().deleted_tables(),
250    ///     [Glyph::Table.into_iden()]
251    /// );
252    /// assert_eq!(
253    ///     query.audit_unwrap().selected_tables(),
254    ///     [Glyph::Table.into_iden()]
255    /// );
256    /// ```
257    pub fn with(self, clause: WithClause) -> WithQuery {
258        clause.query(self)
259    }
260
261    /// Create a Common Table Expression by specifying a [CommonTableExpression][crate::CommonTableExpression]
262    /// or [WithClause] to execute this query with.
263    ///
264    /// # Examples
265    ///
266    /// ```
267    /// use sea_query::{IntoCondition, IntoIden, audit::*, tests_cfg::*, *};
268    ///
269    /// let select = SelectStatement::new()
270    ///         .columns([Glyph::Id])
271    ///         .from(Glyph::Table)
272    ///         .and_where(Expr::col(Glyph::Image).like("0%"))
273    ///         .to_owned();
274    ///     let cte = CommonTableExpression::new()
275    ///         .query(select)
276    ///         .column(Glyph::Id)
277    ///         .table_name("cte")
278    ///         .to_owned();
279    ///     let with_clause = WithClause::new().cte(cte).to_owned();
280    ///     let query = DeleteStatement::new()
281    ///         .with_cte(with_clause)
282    ///         .from_table(Glyph::Table)
283    ///         .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from("cte").to_owned()))
284    ///         .to_owned();
285    ///
286    /// assert_eq!(
287    ///     query.to_string(MysqlQueryBuilder),
288    ///     r#"WITH `cte` (`id`) AS (SELECT `id` FROM `glyph` WHERE `image` LIKE '0%') DELETE FROM `glyph` WHERE `id` IN (SELECT `id` FROM `cte`)"#
289    /// );
290    /// assert_eq!(
291    ///     query.to_string(PostgresQueryBuilder),
292    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
293    /// );
294    /// assert_eq!(
295    ///     query.to_string(SqliteQueryBuilder),
296    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
297    /// );
298    /// assert_eq!(
299    ///     query.audit_unwrap().deleted_tables(),
300    ///     [Glyph::Table.into_iden()]
301    /// );
302    /// assert_eq!(
303    ///     query.audit_unwrap().selected_tables(),
304    ///     [Glyph::Table.into_iden()]
305    /// );
306    /// ```
307    pub fn with_cte<C: Into<WithClause>>(&mut self, clause: C) -> &mut Self {
308        self.with = Some(clause.into());
309        self
310    }
311}
312
313impl QueryStatementBuilder for DeleteStatement {
314    fn build_collect_any_into(&self, query_builder: &impl QueryBuilder, sql: &mut impl SqlWriter) {
315        query_builder.prepare_delete_statement(self, sql);
316    }
317}
318
319impl DeleteStatement {
320    pub fn build_any(&self, query_builder: &impl QueryBuilder) -> (String, Values) {
321        <Self as QueryStatementBuilder>::build_any(self, query_builder)
322    }
323
324    pub fn build_collect_any(
325        &self,
326        query_builder: &impl QueryBuilder,
327        sql: &mut impl SqlWriter,
328    ) -> String {
329        <Self as QueryStatementBuilder>::build_collect_any(self, query_builder, sql)
330    }
331
332    pub fn build_collect_any_into(
333        &self,
334        query_builder: &impl QueryBuilder,
335        sql: &mut impl SqlWriter,
336    ) {
337        <Self as QueryStatementBuilder>::build_collect_any_into(self, query_builder, sql)
338    }
339}
340
341impl From<DeleteStatement> for QueryStatement {
342    fn from(s: DeleteStatement) -> Self {
343        Self::Delete(s)
344    }
345}
346
347impl From<DeleteStatement> for SubQueryStatement {
348    fn from(s: DeleteStatement) -> Self {
349        Self::DeleteStatement(s)
350    }
351}
352
353impl QueryStatementWriter for DeleteStatement {
354    fn build_collect_into<T: QueryBuilder>(&self, query_builder: T, sql: &mut impl SqlWriter) {
355        query_builder.prepare_delete_statement(self, sql);
356    }
357}
358
359impl DeleteStatement {
360    pub fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String {
361        <Self as QueryStatementWriter>::to_string(self, query_builder)
362    }
363
364    pub fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values) {
365        <Self as QueryStatementWriter>::build(self, query_builder)
366    }
367
368    pub fn build_collect<T: QueryBuilder>(
369        &self,
370        query_builder: T,
371        sql: &mut impl SqlWriter,
372    ) -> String {
373        <Self as QueryStatementWriter>::build_collect(self, query_builder, sql)
374    }
375
376    pub fn build_collect_into<T: QueryBuilder>(&self, query_builder: T, sql: &mut impl SqlWriter) {
377        <Self as QueryStatementWriter>::build_collect_into(self, query_builder, sql);
378    }
379}
380
381impl OrderedStatement for DeleteStatement {
382    fn add_order_by(&mut self, order: OrderExpr) -> &mut Self {
383        self.orders.push(order);
384        self
385    }
386
387    fn clear_order_by(&mut self) -> &mut Self {
388        self.orders = Vec::new();
389        self
390    }
391}
392
393impl DeleteStatement {
394    pub fn add_order_by(&mut self, order: OrderExpr) -> &mut Self {
395        <Self as OrderedStatement>::add_order_by(self, order)
396    }
397
398    pub fn clear_order_by(&mut self) -> &mut Self {
399        <Self as OrderedStatement>::clear_order_by(self)
400    }
401
402    pub fn order_by<T: IntoColumnRef>(&mut self, col: T, order: Order) -> &mut Self {
403        <Self as OrderedStatement>::order_by(self, col, order)
404    }
405
406    pub fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self {
407        <Self as OrderedStatement>::order_by_expr(self, expr, order)
408    }
409
410    pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self
411    where
412        T: Into<Cow<'static, str>>,
413        I: IntoIterator<Item = (T, Order)>,
414    {
415        <Self as OrderedStatement>::order_by_customs(self, cols)
416    }
417
418    pub fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self
419    where
420        T: IntoColumnRef,
421        I: IntoIterator<Item = (T, Order)>,
422    {
423        <Self as OrderedStatement>::order_by_columns(self, cols)
424    }
425
426    pub fn order_by_with_nulls<T: IntoColumnRef>(
427        &mut self,
428        col: T,
429        order: Order,
430        nulls: NullOrdering,
431    ) -> &mut Self {
432        <Self as OrderedStatement>::order_by_with_nulls(self, col, order, nulls)
433    }
434
435    pub fn order_by_expr_with_nulls(
436        &mut self,
437        expr: Expr,
438        order: Order,
439        nulls: NullOrdering,
440    ) -> &mut Self {
441        <Self as OrderedStatement>::order_by_expr_with_nulls(self, expr, order, nulls)
442    }
443
444    pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
445    where
446        T: Into<Cow<'static, str>>,
447        I: IntoIterator<Item = (T, Order, NullOrdering)>,
448    {
449        <Self as OrderedStatement>::order_by_customs_with_nulls(self, cols)
450    }
451
452    pub fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
453    where
454        T: IntoColumnRef,
455        I: IntoIterator<Item = (T, Order, NullOrdering)>,
456    {
457        <Self as OrderedStatement>::order_by_columns_with_nulls(self, cols)
458    }
459}
460
461impl ConditionalStatement for DeleteStatement {
462    fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self {
463        self.r#where.add_and_or(condition);
464        self
465    }
466
467    fn cond_where<C>(&mut self, condition: C) -> &mut Self
468    where
469        C: IntoCondition,
470    {
471        self.r#where.add_condition(condition.into_condition());
472        self
473    }
474}
475
476impl DeleteStatement {
477    pub fn and_where(&mut self, other: Expr) -> &mut Self {
478        <Self as ConditionalStatement>::and_where(self, other)
479    }
480
481    pub fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self {
482        <Self as ConditionalStatement>::and_where_option(self, other)
483    }
484
485    pub fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self {
486        <Self as ConditionalStatement>::and_or_where(self, condition)
487    }
488
489    pub fn cond_where<C: IntoCondition>(&mut self, condition: C) -> &mut Self {
490        <Self as ConditionalStatement>::cond_where(self, condition)
491    }
492}
sea_query/query/delete.rs

sea_query/query/
delete.rs
