sea_query/query/
delete.rs

1use crate::{
2    backend::QueryBuilder,
3    prepare::*,
4    query::{condition::*, OrderedStatement},
5    types::*,
6    value::*,
7    QueryStatementBuilder, QueryStatementWriter, ReturningClause, SimpleExpr, SubQueryStatement,
8    WithClause, WithQuery,
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    /// Specify which table to delete from.
57    ///
58    /// # Examples
59    ///
60    /// ```
61    /// use sea_query::{tests_cfg::*, *};
62    ///
63    /// let query = Query::delete()
64    ///     .from_table(Glyph::Table)
65    ///     .and_where(Expr::col(Glyph::Id).eq(1))
66    ///     .to_owned();
67    ///
68    /// assert_eq!(
69    ///     query.to_string(MysqlQueryBuilder),
70    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
71    /// );
72    /// assert_eq!(
73    ///     query.to_string(PostgresQueryBuilder),
74    ///     r#"DELETE FROM "glyph" WHERE "id" = 1"#
75    /// );
76    /// assert_eq!(
77    ///     query.to_string(SqliteQueryBuilder),
78    ///     r#"DELETE FROM "glyph" WHERE "id" = 1"#
79    /// );
80    /// ```
81    #[allow(clippy::wrong_self_convention)]
82    pub fn from_table<T>(&mut self, tbl_ref: T) -> &mut Self
83    where
84        T: IntoTableRef,
85    {
86        self.table = Some(Box::new(tbl_ref.into_table_ref()));
87        self
88    }
89
90    /// Limit number of updated rows.
91    pub fn limit(&mut self, limit: u64) -> &mut Self {
92        self.limit = Some(limit.into());
93        self
94    }
95
96    /// RETURNING expressions.
97    ///
98    /// # Examples
99    ///
100    /// ```
101    /// use sea_query::{tests_cfg::*, *};
102    ///
103    /// let query = Query::delete()
104    ///     .from_table(Glyph::Table)
105    ///     .and_where(Expr::col(Glyph::Id).eq(1))
106    ///     .returning(Query::returning().columns([Glyph::Id]))
107    ///     .to_owned();
108    ///
109    /// assert_eq!(
110    ///     query.to_string(MysqlQueryBuilder),
111    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
112    /// );
113    /// assert_eq!(
114    ///     query.to_string(PostgresQueryBuilder),
115    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
116    /// );
117    /// assert_eq!(
118    ///     query.to_string(SqliteQueryBuilder),
119    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
120    /// );
121    /// ```
122    pub fn returning(&mut self, returning_cols: ReturningClause) -> &mut Self {
123        self.returning = Some(returning_cols);
124        self
125    }
126
127    /// RETURNING expressions for a column.
128    ///
129    /// # Examples
130    ///
131    /// ```
132    /// use sea_query::{tests_cfg::*, *};
133    ///
134    /// let query = Query::delete()
135    ///     .from_table(Glyph::Table)
136    ///     .and_where(Expr::col(Glyph::Id).eq(1))
137    ///     .returning_col(Glyph::Id)
138    ///     .to_owned();
139    ///
140    /// assert_eq!(
141    ///     query.to_string(MysqlQueryBuilder),
142    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
143    /// );
144    /// assert_eq!(
145    ///     query.to_string(PostgresQueryBuilder),
146    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
147    /// );
148    /// assert_eq!(
149    ///     query.to_string(SqliteQueryBuilder),
150    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING "id""#
151    /// );
152    /// ```
153    pub fn returning_col<C>(&mut self, col: C) -> &mut Self
154    where
155        C: IntoColumnRef,
156    {
157        self.returning(ReturningClause::Columns(vec![col.into_column_ref()]))
158    }
159
160    /// RETURNING expressions all columns.
161    ///
162    /// # Examples
163    ///
164    /// ```
165    /// use sea_query::{tests_cfg::*, *};
166    ///
167    /// let query = Query::delete()
168    ///     .from_table(Glyph::Table)
169    ///     .and_where(Expr::col(Glyph::Id).eq(1))
170    ///     .returning_all()
171    ///     .to_owned();
172    ///
173    /// assert_eq!(
174    ///     query.to_string(MysqlQueryBuilder),
175    ///     r#"DELETE FROM `glyph` WHERE `id` = 1"#
176    /// );
177    /// assert_eq!(
178    ///     query.to_string(PostgresQueryBuilder),
179    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING *"#
180    /// );
181    /// assert_eq!(
182    ///     query.to_string(SqliteQueryBuilder),
183    ///     r#"DELETE FROM "glyph" WHERE "id" = 1 RETURNING *"#
184    /// );
185    /// ```
186    pub fn returning_all(&mut self) -> &mut Self {
187        self.returning(ReturningClause::All)
188    }
189
190    /// Create a [WithQuery] by specifying a [WithClause] to execute this query with.
191    ///
192    /// # Examples
193    ///
194    /// ```
195    /// use sea_query::{*, IntoCondition, IntoIden, tests_cfg::*};
196    ///
197    /// let select = SelectStatement::new()
198    ///         .columns([Glyph::Id])
199    ///         .from(Glyph::Table)
200    ///         .and_where(Expr::col(Glyph::Image).like("0%"))
201    ///         .to_owned();
202    ///     let cte = CommonTableExpression::new()
203    ///         .query(select)
204    ///         .column(Glyph::Id)
205    ///         .table_name(Alias::new("cte"))
206    ///         .to_owned();
207    ///     let with_clause = WithClause::new().cte(cte).to_owned();
208    ///     let update = DeleteStatement::new()
209    ///         .from_table(Glyph::Table)
210    ///         .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from(Alias::new("cte")).to_owned()))
211    ///         .to_owned();
212    ///     let query = update.with(with_clause);
213    ///
214    /// assert_eq!(
215    ///     query.to_string(MysqlQueryBuilder),
216    ///     r#"WITH `cte` (`id`) AS (SELECT `id` FROM `glyph` WHERE `image` LIKE '0%') DELETE FROM `glyph` WHERE `id` IN (SELECT `id` FROM `cte`)"#
217    /// );
218    /// assert_eq!(
219    ///     query.to_string(PostgresQueryBuilder),
220    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
221    /// );
222    /// assert_eq!(
223    ///     query.to_string(SqliteQueryBuilder),
224    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
225    /// );
226    /// ```
227    pub fn with(self, clause: WithClause) -> WithQuery {
228        clause.query(self)
229    }
230
231    /// Create a Common Table Expression by specifying a [CommonTableExpression] or [WithClause] to execute this query with.
232    ///
233    /// # Examples
234    ///
235    /// ```
236    /// use sea_query::{*, IntoCondition, IntoIden, tests_cfg::*};
237    ///
238    /// let select = SelectStatement::new()
239    ///         .columns([Glyph::Id])
240    ///         .from(Glyph::Table)
241    ///         .and_where(Expr::col(Glyph::Image).like("0%"))
242    ///         .to_owned();
243    ///     let cte = CommonTableExpression::new()
244    ///         .query(select)
245    ///         .column(Glyph::Id)
246    ///         .table_name(Alias::new("cte"))
247    ///         .to_owned();
248    ///     let with_clause = WithClause::new().cte(cte).to_owned();
249    ///     let query = DeleteStatement::new()
250    ///         .with_cte(with_clause)
251    ///         .from_table(Glyph::Table)
252    ///         .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from(Alias::new("cte")).to_owned()))
253    ///         .to_owned();
254    ///
255    /// assert_eq!(
256    ///     query.to_string(MysqlQueryBuilder),
257    ///     r#"WITH `cte` (`id`) AS (SELECT `id` FROM `glyph` WHERE `image` LIKE '0%') DELETE FROM `glyph` WHERE `id` IN (SELECT `id` FROM `cte`)"#
258    /// );
259    /// assert_eq!(
260    ///     query.to_string(PostgresQueryBuilder),
261    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
262    /// );
263    /// assert_eq!(
264    ///     query.to_string(SqliteQueryBuilder),
265    ///     r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') DELETE FROM "glyph" WHERE "id" IN (SELECT "id" FROM "cte")"#
266    /// );
267    /// ```
268    pub fn with_cte<C: Into<WithClause>>(&mut self, clause: C) -> &mut Self {
269        self.with = Some(clause.into());
270        self
271    }
272}
273
274#[inherent]
275impl QueryStatementBuilder for DeleteStatement {
276    pub fn build_collect_any_into(
277        &self,
278        query_builder: &dyn QueryBuilder,
279        sql: &mut dyn SqlWriter,
280    ) {
281        query_builder.prepare_delete_statement(self, sql);
282    }
283
284    pub fn into_sub_query_statement(self) -> SubQueryStatement {
285        SubQueryStatement::DeleteStatement(self)
286    }
287
288    pub fn build_any(&self, query_builder: &dyn QueryBuilder) -> (String, Values);
289    pub fn build_collect_any(
290        &self,
291        query_builder: &dyn QueryBuilder,
292        sql: &mut dyn SqlWriter,
293    ) -> String;
294}
295
296#[inherent]
297impl QueryStatementWriter for DeleteStatement {
298    pub fn build_collect_into<T: QueryBuilder>(&self, query_builder: T, sql: &mut dyn SqlWriter) {
299        query_builder.prepare_delete_statement(self, sql);
300    }
301
302    pub fn build_collect<T: QueryBuilder>(
303        &self,
304        query_builder: T,
305        sql: &mut dyn SqlWriter,
306    ) -> String;
307    pub fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values);
308    pub fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String;
309}
310
311#[inherent]
312impl OrderedStatement for DeleteStatement {
313    pub fn add_order_by(&mut self, order: OrderExpr) -> &mut Self {
314        self.orders.push(order);
315        self
316    }
317
318    pub fn clear_order_by(&mut self) -> &mut Self {
319        self.orders = Vec::new();
320        self
321    }
322
323    pub fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self
324    where
325        T: IntoColumnRef;
326
327    pub fn order_by_expr(&mut self, expr: SimpleExpr, order: Order) -> &mut Self;
328    pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self
329    where
330        T: ToString,
331        I: IntoIterator<Item = (T, Order)>;
332    pub fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self
333    where
334        T: IntoColumnRef,
335        I: IntoIterator<Item = (T, Order)>;
336    pub fn order_by_with_nulls<T>(
337        &mut self,
338        col: T,
339        order: Order,
340        nulls: NullOrdering,
341    ) -> &mut Self
342    where
343        T: IntoColumnRef;
344    pub fn order_by_expr_with_nulls(
345        &mut self,
346        expr: SimpleExpr,
347        order: Order,
348        nulls: NullOrdering,
349    ) -> &mut Self;
350    pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
351    where
352        T: ToString,
353        I: IntoIterator<Item = (T, Order, NullOrdering)>;
354    pub fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
355    where
356        T: IntoColumnRef,
357        I: IntoIterator<Item = (T, Order, NullOrdering)>;
358}
359
360#[inherent]
361impl ConditionalStatement for DeleteStatement {
362    pub fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self {
363        self.r#where.add_and_or(condition);
364        self
365    }
366
367    pub fn cond_where<C>(&mut self, condition: C) -> &mut Self
368    where
369        C: IntoCondition,
370    {
371        self.r#where.add_condition(condition.into_condition());
372        self
373    }
374
375    pub fn and_where_option(&mut self, other: Option<SimpleExpr>) -> &mut Self;
376    pub fn and_where(&mut self, other: SimpleExpr) -> &mut Self;
377}
sea_query/query/delete.rs

sea_query/query/
delete.rs
