sea_query/query/
delete.rs

1use crate::{
2    Expr, QueryStatement, QueryStatementBuilder, QueryStatementWriter, ReturningClause,
3    SubQueryStatement, WithClause, WithQuery,
4    backend::QueryBuilder,
5    prepare::*,
6    query::{OrderedStatement, condition::*},
7    types::*,
8    value::*,
9};
10use inherent::inherent;
11
12/// Delete existing rows from the table
13///
14/// # Examples
15///
16/// ```
17/// use sea_query::{tests_cfg::*, *};
18///
19/// let query = Query::delete()
20///     .from_table(Glyph::Table)
21///     .cond_where(any![
22///         Expr::col(Glyph::Id).lt(1),
23///         Expr::col(Glyph::Id).gt(10),
24///     ])
25///     .to_owned();
26///
27/// assert_eq!(
28///     query.to_string(MysqlQueryBuilder),
29///     r#"DELETE FROM `glyph` WHERE `id` < 1 OR `id` > 10"#
30/// );
31/// assert_eq!(
32///     query.to_string(PostgresQueryBuilder),
33///     r#"DELETE FROM "glyph" WHERE "id" < 1 OR "id" > 10"#
34/// );
35/// assert_eq!(
36///     query.to_string(SqliteQueryBuilder),
37///     r#"DELETE FROM "glyph" WHERE "id" < 1 OR "id" > 10"#
38/// );
39/// ```
40#[derive(Default, Debug, Clone, PartialEq)]
41pub struct DeleteStatement {
42    pub(crate) table: Option<Box<TableRef>>,
43    pub(crate) r#where: ConditionHolder,
44    pub(crate) orders: Vec<OrderExpr>,
45    pub(crate) limit: Option<Value>,
46    pub(crate) returning: Option<ReturningClause>,
47    pub(crate) with: Option<WithClause>,
48}
49
50impl DeleteStatement {
51    /// Construct a new [`DeleteStatement`]
52    pub fn new() -> Self {
53        Self::default()
54    }
55
56    pub fn take(&mut self) -> Self {
57        Self {
58            table: self.table.take(),
59            r#where: std::mem::take(&mut self.r#where),
60            orders: std::mem::take(&mut self.orders),
61            limit: self.limit.take(),
62            returning: self.returning.take(),
63            with: self.with.take(),
64        }
65    }
66
67    /// Specify which table to delete from.
68    ///
69    /// # Examples
70    ///
71    /// ```
72    /// use sea_query::{audit::*, tests_cfg::*, *};
73    ///
74    /// let query = Query::delete()
75    ///     .from_table(Glyph::Table)
76    ///     .and_where(Expr::col(Glyph::Id).eq(1))
77    ///     .to_owned();
78    ///
79    /// assert_eq!(
80    ///     query.to_string(MysqlQueryBuilder),
81    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
82    /// );
83    /// assert_eq!(
84    ///     query.to_string(PostgresQueryBuilder),
85    ///     r#"DELETE FROM "glyph" WHERE "id" = 1"#
86    /// );
87    /// assert_eq!(
88    ///     query.to_string(SqliteQueryBuilder),
89    ///     r#"DELETE FROM "glyph" WHERE "id" = 1"#
90    /// );
91    /// assert_eq!(
92    ///     query.audit_unwrap().deleted_tables(),
93    ///     [Glyph::Table.into_iden()]
94    /// );
95    /// assert_eq!(query.audit_unwrap().selected_tables(), []);
96    /// ```
97    #[allow(clippy::wrong_self_convention)]
98    pub fn from_table<T>(&mut self, tbl_ref: T) -> &mut Self
99    where
100        T: IntoTableRef,
101    {
102        self.table = Some(Box::new(tbl_ref.into_table_ref()));
103        self
104    }
105
106    /// Limit number of updated rows.
107    pub fn limit(&mut self, limit: u64) -> &mut Self {
108        self.limit = Some(limit.into());
109        self
110    }
111
112    /// RETURNING expressions.
113    ///
114    /// # Examples
115    ///
116    /// ```
117    /// use sea_query::{audit::*, tests_cfg::*, *};
118    ///
119    /// let query = Query::delete()
120    ///     .from_table(Glyph::Table)
121    ///     .and_where(Expr::col(Glyph::Id).eq(1))
122    ///     .returning(Query::returning().columns([Glyph::Id]))
123    ///     .to_owned();
124    ///
125    /// assert_eq!(
126    ///     query.to_string(MysqlQueryBuilder),
127    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
128    /// );
129    /// assert_eq!(
130    ///     query.to_string(PostgresQueryBuilder),
131    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
132    /// );
133    /// assert_eq!(
134    ///     query.to_string(SqliteQueryBuilder),
135    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
136    /// );
137    /// assert_eq!(
138    ///     query.audit_unwrap().deleted_tables(),
139    ///     [Glyph::Table.into_iden()]
140    /// );
141    /// assert_eq!(
142    ///     query.audit_unwrap().selected_tables(),
143    ///     [Glyph::Table.into_iden()]
144    /// );
145    /// ```
146    pub fn returning(&mut self, returning_cols: ReturningClause) -> &mut Self {
147        self.returning = Some(returning_cols);
148        self
149    }
150
151    /// RETURNING expressions for a column.
152    ///
153    /// # Examples
154    ///
155    /// ```
156    /// use sea_query::{tests_cfg::*, *};
157    ///
158    /// let query = Query::delete()
159    ///     .from_table(Glyph::Table)
160    ///     .and_where(Expr::col(Glyph::Id).eq(1))
161    ///     .returning_col(Glyph::Id)
162    ///     .to_owned();
163    ///
164    /// assert_eq!(
165    ///     query.to_string(MysqlQueryBuilder),
166    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
167    /// );
168    /// assert_eq!(
169    ///     query.to_string(PostgresQueryBuilder),
170    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
171    /// );
172    /// assert_eq!(
173    ///     query.to_string(SqliteQueryBuilder),
174    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
175    /// );
176    /// ```
177    pub fn returning_col<C>(&mut self, col: C) -> &mut Self
178    where
179        C: IntoColumnRef,
180    {
181        self.returning(ReturningClause::Columns(vec![col.into_column_ref()]))
182    }
183
184    /// RETURNING expressions all columns.
185    ///
186    /// # Examples
187    ///
188    /// ```
189    /// use sea_query::{tests_cfg::*, *};
190    ///
191    /// let query = Query::delete()
192    ///     .from_table(Glyph::Table)
193    ///     .and_where(Expr::col(Glyph::Id).eq(1))
194    ///     .returning_all()
195    ///     .to_owned();
196    ///
197    /// assert_eq!(
198    ///     query.to_string(MysqlQueryBuilder),
199    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
200    /// );
201    /// assert_eq!(
202    ///     query.to_string(PostgresQueryBuilder),
203    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING *"#
204    /// );
205    /// assert_eq!(
206    ///     query.to_string(SqliteQueryBuilder),
207    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING *"#
208    /// );
209    /// ```
210    pub fn returning_all(&mut self) -> &mut Self {
211        self.returning(ReturningClause::All)
212    }
213
214    /// Create a [WithQuery] by specifying a [WithClause] to execute this query with.
215    ///
216    /// # Examples
217    ///
218    /// ```
219    /// use sea_query::{IntoCondition, IntoIden, audit::*, tests_cfg::*, *};
220    ///
221    /// let select = SelectStatement::new()
222    ///         .columns([Glyph::Id])
223    ///         .from(Glyph::Table)
224    ///         .and_where(Expr::col(Glyph::Image).like("0%"))
225    ///         .to_owned();
226    ///     let cte = CommonTableExpression::new()
227    ///         .query(select)
228    ///         .column(Glyph::Id)
229    ///         .table_name("cte")
230    ///         .to_owned();
231    ///     let with_clause = WithClause::new().cte(cte).to_owned();
232    ///     let update = DeleteStatement::new()
233    ///         .from_table(Glyph::Table)
234    ///         .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from("cte").to_owned()))
235    ///         .to_owned();
236    ///     let query = update.with(with_clause);
237    ///
238    /// assert_eq!(
239    ///     query.to_string(MysqlQueryBuilder),
240    ///     r#"WITH `cte` (`id`) AS (SELECT `id` FROM `glyph` WHERE `image` LIKE '0%') DELETE FROM `glyph` WHERE `id` IN (SELECT `id` FROM `cte`)"#
241    /// );
242    /// assert_eq!(
243    ///     query.to_string(PostgresQueryBuilder),
244    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
245    /// );
246    /// assert_eq!(
247    ///     query.to_string(SqliteQueryBuilder),
248    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
249    /// );
250    /// assert_eq!(
251    ///     query.audit_unwrap().deleted_tables(),
252    ///     [Glyph::Table.into_iden()]
253    /// );
254    /// assert_eq!(
255    ///     query.audit_unwrap().selected_tables(),
256    ///     [Glyph::Table.into_iden()]
257    /// );
258    /// ```
259    pub fn with(self, clause: WithClause) -> WithQuery {
260        clause.query(self)
261    }
262
263    /// Create a Common Table Expression by specifying a [CommonTableExpression] 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: &dyn QueryBuilder,
319        sql: &mut dyn SqlWriter,
320    ) {
321        query_builder.prepare_delete_statement(self, sql);
322    }
323
324    pub fn build_any(&self, query_builder: &dyn QueryBuilder) -> (String, Values);
325    pub fn build_collect_any(
326        &self,
327        query_builder: &dyn QueryBuilder,
328        sql: &mut dyn 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 dyn 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 dyn 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: ToString,
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: ToString,
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
