sea_query/query/
delete.rs

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

sea_query/query/
delete.rs
