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][crate::CommonTableExpression]
263    /// or [WithClause] to execute this query with.
264    ///
265    /// # Examples
266    ///
267    /// ```
268    /// use sea_query::{IntoCondition, IntoIden, audit::*, tests_cfg::*, *};
269    ///
270    /// let select = SelectStatement::new()
271    ///         .columns([Glyph::Id])
272    ///         .from(Glyph::Table)
273    ///         .and_where(Expr::col(Glyph::Image).like("0%"))
274    ///         .to_owned();
275    ///     let cte = CommonTableExpression::new()
276    ///         .query(select)
277    ///         .column(Glyph::Id)
278    ///         .table_name("cte")
279    ///         .to_owned();
280    ///     let with_clause = WithClause::new().cte(cte).to_owned();
281    ///     let query = DeleteStatement::new()
282    ///         .with_cte(with_clause)
283    ///         .from_table(Glyph::Table)
284    ///         .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from("cte").to_owned()))
285    ///         .to_owned();
286    ///
287    /// assert_eq!(
288    ///     query.to_string(MysqlQueryBuilder),
289    ///     r#"WITH `cte` (`id`) AS (SELECT `id` FROM `glyph` WHERE `image` LIKE '0%') DELETE FROM `glyph` WHERE `id` IN (SELECT `id` FROM `cte`)"#
290    /// );
291    /// assert_eq!(
292    ///     query.to_string(PostgresQueryBuilder),
293    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
294    /// );
295    /// assert_eq!(
296    ///     query.to_string(SqliteQueryBuilder),
297    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
298    /// );
299    /// assert_eq!(
300    ///     query.audit_unwrap().deleted_tables(),
301    ///     [Glyph::Table.into_iden()]
302    /// );
303    /// assert_eq!(
304    ///     query.audit_unwrap().selected_tables(),
305    ///     [Glyph::Table.into_iden()]
306    /// );
307    /// ```
308    pub fn with_cte<C: Into<WithClause>>(&mut self, clause: C) -> &mut Self {
309        self.with = Some(clause.into());
310        self
311    }
312}
313
314#[inherent]
315impl QueryStatementBuilder for DeleteStatement {
316    pub fn build_collect_any_into(
317        &self,
318        query_builder: &impl QueryBuilder,
319        sql: &mut impl SqlWriter,
320    ) {
321        query_builder.prepare_delete_statement(self, sql);
322    }
323
324    pub fn build_any(&self, query_builder: &impl QueryBuilder) -> (String, Values);
325    pub fn build_collect_any(
326        &self,
327        query_builder: &impl QueryBuilder,
328        sql: &mut impl SqlWriter,
329    ) -> String;
330}
331
332impl From<DeleteStatement> for QueryStatement {
333    fn from(s: DeleteStatement) -> Self {
334        Self::Delete(s)
335    }
336}
337
338impl From<DeleteStatement> for SubQueryStatement {
339    fn from(s: DeleteStatement) -> Self {
340        Self::DeleteStatement(s)
341    }
342}
343
344#[inherent]
345impl QueryStatementWriter for DeleteStatement {
346    pub fn build_collect_into<T: QueryBuilder>(&self, query_builder: T, sql: &mut impl SqlWriter) {
347        query_builder.prepare_delete_statement(self, sql);
348    }
349
350    pub fn build_collect<T: QueryBuilder>(
351        &self,
352        query_builder: T,
353        sql: &mut impl SqlWriter,
354    ) -> String;
355    pub fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values);
356    pub fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String;
357}
358
359#[inherent]
360impl OrderedStatement for DeleteStatement {
361    pub fn add_order_by(&mut self, order: OrderExpr) -> &mut Self {
362        self.orders.push(order);
363        self
364    }
365
366    pub fn clear_order_by(&mut self) -> &mut Self {
367        self.orders = Vec::new();
368        self
369    }
370
371    pub fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self
372    where
373        T: IntoColumnRef;
374
375    pub fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self;
376    pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self
377    where
378        T: Into<Cow<'static, str>>,
379        I: IntoIterator<Item = (T, Order)>;
380    pub fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self
381    where
382        T: IntoColumnRef,
383        I: IntoIterator<Item = (T, Order)>;
384    pub fn order_by_with_nulls<T>(
385        &mut self,
386        col: T,
387        order: Order,
388        nulls: NullOrdering,
389    ) -> &mut Self
390    where
391        T: IntoColumnRef;
392    pub fn order_by_expr_with_nulls(
393        &mut self,
394        expr: Expr,
395        order: Order,
396        nulls: NullOrdering,
397    ) -> &mut Self;
398    pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
399    where
400        T: Into<Cow<'static, str>>,
401        I: IntoIterator<Item = (T, Order, NullOrdering)>;
402    pub fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
403    where
404        T: IntoColumnRef,
405        I: IntoIterator<Item = (T, Order, NullOrdering)>;
406}
407
408#[inherent]
409impl ConditionalStatement for DeleteStatement {
410    pub fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self {
411        self.r#where.add_and_or(condition);
412        self
413    }
414
415    pub fn cond_where<C>(&mut self, condition: C) -> &mut Self
416    where
417        C: IntoCondition,
418    {
419        self.r#where.add_condition(condition.into_condition());
420        self
421    }
422
423    pub fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self;
424    pub fn and_where(&mut self, other: Expr) -> &mut Self;
425}
sea_query/query/delete.rs

sea_query/query/
delete.rs
