sqlx_model/curd/
select.rs

1use super::TableName;
2use super::{DbType, ModelTableField, ModelTableName, TableFields, WhereOption};
3use sqlx::{database::HasArguments, Database, Error, FromRow};
4use sqlx::{Arguments, Executor, IntoArguments};
5use std::vec;
6
7macro_rules! fetch_by_where {
8    ($self_var:ident,$bind_type:ty,$name:ident,$sql:literal,$where_sql:literal,$query_type:ident,$fetch_type:ident,$out_type:ty)=>{
9        impl Select<$bind_type>
10        {
11            /// M 为 Model  类型
12            pub async fn $name<'c,M,E>(
13                &$self_var,
14                where_sql:&WhereOption,
15                executor:E
16            )
17                ->Result<$out_type,Error>
18                where
19                for<'r> M:  FromRow<'r, <$bind_type as Database>::Row>+Send+Unpin+ModelTableField<$bind_type>,
20                for<'n> <$bind_type as HasArguments<'n>>::Arguments:
21                    Arguments<'n>+IntoArguments<'n,$bind_type>,
22                E: Executor<'c, Database = $bind_type>
23            {
24                let sql=match where_sql {
25                    WhereOption::NoWhere(other)=>{
26                        format!(
27                            "{} {}",
28                            format!(
29                                $sql,
30                                $self_var.table_field.to_vec().join(","),
31                                $self_var.table_name.full_name(),
32                            ),
33                            other
34                        )
35                    }
36
37                    WhereOption::Where(wsql)=>{
38                       format!(
39                            $where_sql,
40                            $self_var.table_field.to_vec().join(","),
41                            $self_var.table_name.full_name(),
42                            wsql
43                        )
44                    }
45                    WhereOption::None=>{
46                        format!(
47                            $sql,
48                            $self_var.table_field.to_vec().join(","),
49                            $self_var.table_name.full_name()
50                        )
51                    }
52                };
53                let res=sqlx::$query_type::<$bind_type, M>(sql.as_str());
54                res.$fetch_type(executor).await
55            }
56        }
57    };
58    ($bind_type:ty,$name:ident,$sql:literal,$where_sql:literal,$query_type:ident,$fetch_type:ident,$out_type:ty)=>{
59        fetch_by_where!(self,$bind_type,$name,$sql,$where_sql,$query_type,$fetch_type,$out_type);
60    };
61}
62
63macro_rules! fetch_by_where_scalar {
64    ($self_var:ident,$bind_type:ty,$name:ident,$sql:literal,$where_sql:literal,$fetch_type:ident,$out_type:ty)=>{
65        impl Select<$bind_type>
66        {
67             /// M 为 返回某字段类型
68            pub async fn $name<'c,M,E>(
69                &$self_var,
70                field_name:&str,
71                where_sql:&WhereOption,
72                executor:E
73            )
74                -> Result<$out_type, Error>
75                where
76                        (M,): for<'r> FromRow<'r, <$bind_type as Database>::Row> + Send + Unpin,
77                        M:Send + Unpin,
78                        for<'n> <$bind_type as HasArguments<'n>>::Arguments:
79                            Arguments<'n>+IntoArguments<'n,$bind_type>,
80                        E: Executor<'c, Database = $bind_type>
81                {
82
83
84                    let sql=match where_sql {
85                        WhereOption::NoWhere(other)=>{
86                            format!(
87                                "{} {}",
88                                format!(
89                                    $sql,
90                                    field_name,
91                                    $self_var.table_name.full_name()
92                                ),
93                                other
94                            )
95                        }
96
97                        WhereOption::Where(wsql)=>{
98                            format!(
99                                $where_sql,
100                                field_name,
101                                $self_var.table_name.full_name(),
102                                wsql
103                            )
104                        }
105                        WhereOption::None=>{
106                           format!(
107                                $sql,
108                                field_name,
109                                $self_var.table_name.full_name()
110                            )
111                        }
112                    };
113                let res = sqlx::query_scalar::<$bind_type, M,>(sql.as_str());
114                res.$fetch_type(executor).await
115            }
116        }
117    };
118    ($bind_type:ty,$name:ident,$sql:literal,$where_sql:literal,$fetch_type:ident,$out_type:ty)=>{
119        fetch_by_where_scalar!(self,$bind_type,$name,$sql,$where_sql,$fetch_type,$out_type);
120    };
121}
122
123/// 查询操作
124pub struct Select<DB>
125where
126    DB: Database,
127{
128    pub table_name: TableName,
129    pub table_field: TableFields,
130    pub table_pk: TableFields,
131    _marker: std::marker::PhantomData<DB>,
132}
133impl<DB> Select<DB>
134where
135    DB: Database,
136{
137    pub fn new(table_name: TableName, table_field: TableFields, table_pk: TableFields) -> Self {
138        Select {
139            table_name,
140            table_field,
141            table_pk,
142            _marker: Default::default(),
143        }
144    }
145    pub fn type_new<T>() -> Self
146    where
147        T: ModelTableField<DB> + ModelTableName,
148    {
149        Select {
150            table_name: T::table_name(),
151            table_field: T::table_column(),
152            table_pk: T::table_pk(),
153            _marker: Default::default(),
154        }
155    }
156    /// 非联合主键的表通过主键值查找某记录
157    /// @field_name 需要获取的字段名
158    /// @pk_scalar 主键值
159    /// @executor Executor
160    /// M 为 Model 类型
161    pub async fn fetch_one_by_scalar_pk<'c, M, PT, E>(
162        &self,
163        pk_scalar: PT,
164        executor: E,
165    ) -> Result<M, Error>
166    where
167        for<'q> PT: 'q + Send + sqlx::Encode<'q, DB> + sqlx::Type<DB>,
168        for<'r> M: FromRow<'r, DB::Row> + Send + Unpin + ModelTableField<DB>,
169        for<'n> <DB as HasArguments<'n>>::Arguments: Arguments<'n> + IntoArguments<'n, DB>,
170        E: Executor<'c, Database = DB>,
171    {
172        let where_sql = scalar_pk_where!(DB, self.table_pk, 0);
173        let sql = format!(
174            "SELECT {} FROM {} WHERE {}",
175            self.table_field.to_vec().join(","),
176            self.table_name.full_name(),
177            where_sql
178        );
179        let mut res = sqlx::query_as::<DB, M>(sql.as_str());
180        res = res.bind(pk_scalar);
181        res.fetch_one(executor).await
182    }
183    /// 非联合主键的表通过主键值查找某字段值
184    /// @field_name 需要获取的字段名
185    /// @pk_scalar 主键值
186    /// @executor Executor
187    /// M 为 @field_name 字段类型
188    pub async fn fetch_one_scalar_by_scalar_pk<'c, M, PT, E>(
189        &self,
190        field_name: &str,
191        pk_scalar: PT,
192        executor: E,
193    ) -> Result<M, Error>
194    where
195        for<'q> PT: 'q + Send + sqlx::Encode<'q, DB> + sqlx::Type<DB>,
196        (M,): for<'r> FromRow<'r, DB::Row> + Send + Unpin,
197        M: Send + Unpin,
198        for<'n> <DB as HasArguments<'n>>::Arguments: Arguments<'n> + IntoArguments<'n, DB>,
199        E: Executor<'c, Database = DB>,
200    {
201        let where_sql = scalar_pk_where!(DB, self.table_pk, 0);
202        let sql = format!(
203            "SELECT {} FROM {} WHERE {}",
204            field_name,
205            self.table_name.full_name(),
206            where_sql
207        );
208        let mut res = sqlx::query_scalar::<DB, M>(sql.as_str());
209        res = res.bind(pk_scalar);
210        res.fetch_one(executor).await
211    }
212    /// 从DB中重新加载Model里值
213    /// @val Model 变量
214    /// @executor Executor
215    pub async fn reload<'c, M, E>(&self, val: &M, executor: E) -> Result<M, Error>
216    where
217        M: for<'r> FromRow<'r, DB::Row> + Send + Unpin + ModelTableField<DB>,
218        for<'n> <DB as HasArguments<'n>>::Arguments: Arguments<'n> + IntoArguments<'n, DB>,
219        E: Executor<'c, Database = DB>,
220    {
221        let pkf = M::table_pk();
222        let mut where_sql = vec![];
223        for (pos, val) in pkf.0.iter().enumerate() {
224            let bst = DbType::type_new::<DB>().mark(pos);
225            where_sql.push(format!("{}={}", val.name, bst));
226        }
227        let sql = format!(
228            "SELECT {} FROM {} WHERE {}",
229            self.table_field.to_vec().join(","),
230            self.table_name.full_name(),
231            where_sql.join(" and ")
232        );
233        let mut res = sqlx::query_as::<DB, M>(sql.as_str());
234        for fval in pkf.0.iter() {
235            res = val.query_as_sqlx_bind(fval, res);
236        }
237        res.fetch_one(executor).await
238    }
239}
240
241#[cfg(feature = "sqlx-mysql")]
242fetch_by_where!(
243    sqlx::MySql,
244    fetch_all_by_where,
245    "SELECT {} FROM {} ",
246    "SELECT {} FROM {} WHERE {} ",
247    query_as,
248    fetch_all,
249    Vec<M>
250);
251#[cfg(feature = "sqlx-mysql")]
252fetch_by_where!(
253    sqlx::MySql,
254    fetch_one_by_where,
255    "SELECT {} FROM {} LIMIT 1",
256    "SELECT {} FROM {} WHERE {} LIMIT 1",
257    query_as,
258    fetch_one,
259    M
260);
261#[cfg(feature = "sqlx-sqlite")]
262fetch_by_where!(
263    sqlx::Sqlite,
264    fetch_all_by_where,
265    "SELECT {} FROM {} ",
266    "SELECT {} FROM {} WHERE {} ",
267    query_as,
268    fetch_all,
269    Vec<M>
270);
271#[cfg(feature = "sqlx-sqlite")]
272fetch_by_where!(
273    sqlx::Sqlite,
274    fetch_one_by_where,
275    "SELECT {} FROM {} LIMIT 1",
276    "SELECT {} FROM {} WHERE {} LIMIT 1",
277    query_as,
278    fetch_one,
279    M
280);
281#[cfg(feature = "sqlx-postgres")]
282fetch_by_where!(
283    sqlx::Postgres,
284    fetch_all_by_where,
285    "SELECT {} FROM {} ",
286    "SELECT {} FROM {} WHERE {} ",
287    query_as,
288    fetch_all,
289    Vec<M>
290);
291#[cfg(feature = "sqlx-postgres")]
292fetch_by_where!(
293    sqlx::Postgres,
294    fetch_one_by_where,
295    "SELECT {} FROM {} LIMIT 1",
296    "SELECT {} FROM {} WHERE {} LIMIT 1",
297    query_as,
298    fetch_one,
299    M
300);
301#[cfg(feature = "sqlx-mssql")]
302fetch_by_where!(
303    sqlx::Mssql,
304    fetch_all_by_where,
305    "SELECT {} FROM {} ",
306    "SELECT {} FROM {} WHERE {} ",
307    query_as,
308    fetch_all,
309    Vec<M>
310);
311#[cfg(feature = "sqlx-mssql")]
312fetch_by_where!(
313    sqlx::Mssql,
314    fetch_one_by_where,
315    "SELECT  TOP 1 {} FROM {}",
316    "SELECT  TOP 1 {} FROM {} WHERE {}",
317    query_as,
318    fetch_one,
319    M
320);
321
322#[cfg(feature = "sqlx-mysql")]
323fetch_by_where_scalar!(
324    sqlx::MySql,
325    fetch_all_scalar_by_where,
326    "SELECT {} FROM {} ",
327    "SELECT {} FROM {} WHERE {} ",
328    fetch_all,
329    Vec<M>
330);
331#[cfg(feature = "sqlx-mysql")]
332fetch_by_where_scalar!(
333    sqlx::MySql,
334    fetch_one_scalar_by_where,
335    "SELECT {} FROM {} LIMIT 1",
336    "SELECT {} FROM {} WHERE {} LIMIT 1",
337    fetch_one,
338    M
339);
340#[cfg(feature = "sqlx-sqlite")]
341fetch_by_where_scalar!(
342    sqlx::Sqlite,
343    fetch_all_scalar_by_where,
344    "SELECT {} FROM {} ",
345    "SELECT {} FROM {} WHERE {} ",
346    fetch_all,
347    Vec<M>
348);
349#[cfg(feature = "sqlx-sqlite")]
350fetch_by_where_scalar!(
351    sqlx::Sqlite,
352    fetch_one_scalar_by_where,
353    "SELECT {} FROM {} LIMIT 1",
354    "SELECT {} FROM {} WHERE {} LIMIT 1",
355    fetch_one,
356    M
357);
358#[cfg(feature = "sqlx-postgres")]
359fetch_by_where_scalar!(
360    sqlx::Postgres,
361    fetch_all_scalar_by_where,
362    "SELECT {} FROM {} ",
363    "SELECT {} FROM {} WHERE {} ",
364    fetch_all,
365    Vec<M>
366);
367#[cfg(feature = "sqlx-postgres")]
368fetch_by_where_scalar!(
369    sqlx::Postgres,
370    fetch_one_scalar_by_where,
371    "SELECT {} FROM {} LIMIT 1",
372    "SELECT {} FROM {} WHERE {} LIMIT 1",
373    fetch_one,
374    M
375);
376#[cfg(feature = "sqlx-mssql")]
377fetch_by_where_scalar!(
378    sqlx::Mssql,
379    fetch_all_scalar_by_where,
380    "SELECT {} FROM {} ",
381    "SELECT {} FROM {} WHERE {} ",
382    fetch_all,
383    Vec<M>
384);
385#[cfg(feature = "sqlx-mssql")]
386fetch_by_where_scalar!(
387    sqlx::Mssql,
388    fetch_one_scalar_by_where,
389    "SELECT TOP 1 {} FROM {}",
390    "SELECT TOP 1 {} FROM {} WHERE {}",
391    fetch_one,
392    M
393);
sqlx_model/curd/select.rs

sqlx_model/curd/
select.rs
