sea_query/query/
delete.rs

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

sea_query/query/
delete.rs
