ngb_sqlbuilder/
select.rs

1use crate::*;
2use tokio_postgres::types::ToSql;
3
4pub struct Where;
5pub struct Order;
6pub struct Group;
7pub struct SelectCondition<'q> {
8    pub(crate) sql: String,
9    pub(crate) params: Vec<&'q (dyn ToSql + Sync)>,
10}
11
12impl<'q, T> SqlClause<'q> for Clause<'q, T> {
13    fn unwrap(self) -> (String, Vec<&'q (dyn ToSql + Sync)>) {
14        (self.sql, self.params)
15    }
16
17    fn unwrap_ref(&self) -> (&str, &[&'q (dyn ToSql + Sync)]) {
18        (&self.sql, &self.params)
19    }
20}
21fn __group_by<'q, T1, T2, T3>(clause: Clause<'q, T1>, group: Clause<'_, T2>) -> Clause<'q, T3> {
22    let (mut sql, params) = clause.unwrap();
23    sql.push_str(" GROUP BY (");
24    let (cols, _) = group.unwrap();
25    sql.push_str(&cols);
26    sql.push(')');
27    Clause::new(sql, params)
28}
29impl<'q> Clause<'q, Where> {
30    pub fn group_by_cols(self, columns: &[&str]) -> Clause<'q, Group> {
31        __group_by(self, cols(columns))
32    }
33    pub fn limit(self, limit: &'q i64) -> Clause<'q, Paging> {
34        __limit(self, limit)
35    }
36    pub fn offset(self, offset: &'q i64) -> Clause<'q, Paging> {
37        __offset(self, offset)
38    }
39}
40
41impl<'q> Clause<'q, Paging> {
42    pub fn limit(self, limit: &'q i64) -> Clause<'q, ()> {
43        __limit(self, limit)
44    }
45    pub fn offset(self, offset: &'q i64) -> Clause<'q, ()> {
46        __offset(self, offset)
47    }
48}
49impl<'q> Clause<'q, Group> {
50    pub fn order_by_cols(self, columns: &[&str]) -> Clause<'q, Order> {
51        __order_by_cols(self, columns)
52    }
53    pub fn limit(self, limit: &'q i64) -> Clause<'q, Paging> {
54        __limit(self, limit)
55    }
56    pub fn offset(self, offset: &'q i64) -> Clause<'q, Paging> {
57        __offset(self, offset)
58    }
59}
60
61impl<'q> Clause<'q, Order> {
62    pub fn limit(self, limit: &'q i64) -> Clause<'q, Paging> {
63        __limit(self, limit)
64    }
65    pub fn offset(self, offset: &'q i64) -> Clause<'q, Paging> {
66        __offset(self, offset)
67    }
68}
69
70impl<'q> Clause<'q, Select> {
71    /// Add more column in `SELECT` statement
72    ///
73    /// # Arguments
74    ///
75    /// * `clause`:
76    ///
77    /// returns: Clause<Select>
78    ///
79    /// # Examples
80    ///
81    /// ```
82    /// use ngb_sqlbuilder::select;
83    ///
84    /// .select(col("t1.ColName as Alias"))
85    /// ```
86    /// ```
87    ///  , t1."ColName" as "Alias"
88    /// ```
89    pub fn select<T>(self, clause: Clause<'q, T>) -> Clause<'q, Select> {
90        let mut sql = self.sql;
91        let mut params = self.params;
92        sql.push_str(", ");
93        sql.push_str(&clause.sql);
94        params.extend_from_slice(&clause.params);
95        Clause::new(sql, params)
96    }
97    pub fn from(self, table: &str) -> Clause<'q, From> {
98        let mut sql = self.sql;
99        sql.push_str(" FROM ");
100        // sql.push_str(&format_prim(table));
101        append_prim(&mut sql, table);
102        Clause::new(sql, self.params)
103    }
104}
105
106fn __join<'q, T>(
107    clause: Clause<'q, T>,
108    join: &str,
109    table: &str,
110    join_method: Clause<'q, Join>,
111) -> Clause<'q, From> {
112    let (mut sql, params) = clause.unwrap();
113    // sql.push(' ');
114    sql.push_str(&join);
115    sql.push_str(" JOIN ");
116    // sql.push_str(&format!(" {join}JOIN "));
117    // sql.push_str(&format_prim(table));
118    append_prim(&mut sql, table);
119    let (join_sql, _) = join_method.unwrap_ref();
120    sql.push_str(&join_sql);
121    Clause::new(sql, params)
122}
123impl<'q> Clause<'q, From> {
124    pub fn join(self, table: &str, join_method: Clause<'q, Join>) -> Clause<'q, From> {
125        __join(self, "", table, join_method)
126    }
127    pub fn left_join(self, table: &str, join_method: Clause<'q, Join>) -> Clause<'q, From> {
128        __join(self, " LEFT", table, join_method)
129    }
130
131    pub fn left_outer_join(self, table: &str, join_method: Clause<'q, Join>) -> Clause<'q, From> {
132        __join(self, " LEFT OUTER", table, join_method)
133    }
134
135    pub fn right_join(self, table: &str, join_method: Clause<'q, Join>) -> Clause<'q, From> {
136        __join(self, " RIGHT", table, join_method)
137    }
138
139    pub fn right_outer_join(self, table: &str, join_method: Clause<'q, Join>) -> Clause<'q, From> {
140        __join(self, " RIGHT OUTER", table, join_method)
141    }
142
143    pub fn where_col(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
144        __where(self, name, condition)
145    }
146    pub fn where_cond(self, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
147        let (mut sql, params) = self.unwrap();
148        sql.push_str(" WHERE ");
149        __condition("", Clause::<From>::new(sql, params), condition)
150    }
151
152    pub fn limit(self, limit: &'q i64) -> Clause<'q, ()> {
153        __limit(self, limit)
154    }
155    pub fn offset(self, offset: &'q i64) -> Clause<'q, ()> {
156        __offset(self, offset)
157    }
158}
159fn __limit<'q, T, C>(clause: Clause<'q, T>, limit: &'q i64) -> Clause<'q, C> {
160    let (mut sql, mut params) = clause.unwrap();
161    sql.push_str(" LIMIT $");
162    params.push(limit);
163    Clause::new(sql, params)
164}
165fn __offset<'q, T, C>(clause: Clause<'q, T>, offset: &'q i64) -> Clause<'q, C> {
166    let (mut sql, mut params) = clause.unwrap();
167    sql.push_str(" OFFSET $");
168    params.push(offset);
169    Clause::new(sql, params)
170}
171
172impl<'q> Clause<'q, Join> {
173    // pub fn using(self, cols: Clause<Column>) -> Clause<'q, From> {
174    //     let (mut sql, params) = self.unwrap();
175    //     let (col_sql, _) = cols.unwrap();
176    //     sql.push_str(" using(");
177    //     sql.push_str(&col_sql);
178    //     sql.push(')');
179    //     Clause::new(sql, params)
180    // }
181    // pub fn using_col(self, col_name: &str) -> Clause<'q, From> {
182    //     let (mut sql, params) = self.unwrap();
183    //     sql.push_str(" using(");
184    //     sql.push_str(&format_col(col_name));
185    //     sql.push(')');
186    //     Clause::new(sql, params)
187    // }
188    // pub fn using_cols(self, col_names: &[&str]) -> Clause<'q, From> {
189    //     let (mut sql, params) = self.unwrap();
190    //     sql.push_str(" using(");
191    //     __cols(&mut sql, col_names);
192    //     sql.push(')');
193    //     Clause::new(sql, params)
194    // }
195    // pub fn on(self, col1: &str, col2: &str) -> Clause<'q, From> {
196    //     let (mut sql, params) = self.unwrap();
197    //     sql.push_str(" ON ");
198    //     sql.push_str(&format_col(col1));
199    //     sql.push_str(" = ");
200    //     sql.push_str(&format_col(col2));
201    //     Clause::new(sql, params)
202    // }
203    // pub fn on_wrap(self, condition: Clause<'q, Condition>) -> Clause<'q, From> {
204    //     let (mut sql, params) = self.unwrap();
205    //     let (cond_sql, _) = condition.unwrap();
206    //     sql.push_str(" ON (");
207    //     sql.push_str(&cond_sql);
208    //     sql.push(')');
209    //     Clause::new(sql, params)
210    // }
211}
212
213pub fn using<'q>(cols: Clause<Column>) -> Clause<'q, Join> {
214    // let (mut sql, params) = self.unwrap();
215    let mut sql = String::new();
216    let (col_sql, _) = cols.unwrap();
217    sql.push_str(" USING (");
218    sql.push_str(&col_sql);
219    sql.push(')');
220    Clause::new(sql, vec![])
221}
222///
223///
224/// # Arguments
225///
226/// * `col_name`: common column name
227///
228/// returns: `Clause<Join>`
229///
230/// # Examples
231///
232/// ```
233/// use ngb_sqlbuilder::using_col;
234/// using_col("Col1")
235/// // USING ("Col1")
236/// ```
237pub fn using_col<'q>(col_name: &str) -> Clause<'q, Join> {
238    // let (mut sql, params) = self.unwrap();
239    let mut sql = String::new();
240    sql.push_str(" USING (");
241    // sql.push_str(&format_col(col_name));
242    append_col(&mut sql, col_name);
243    sql.push(')');
244    Clause::new(sql, vec![])
245}
246///
247///
248/// # Arguments
249///
250/// * `col_names`: Array of column names
251///
252/// returns: `Clause<Join>`
253///
254/// # Examples
255///
256/// ```
257/// use ngb_sqlbuilder::using_col;
258///
259/// using_col(&["Col1", "Col2", ...])
260/// // USING ("Col1", "Col2", ...)
261/// ```
262pub fn using_cols<'q>(col_names: &[&str]) -> Clause<'q, Join> {
263    // let (mut sql, params) = self.unwrap();
264    let mut sql = String::new();
265    sql.push_str(" USING (");
266    __cols(&mut sql, col_names);
267    sql.push(')');
268    Clause::new(sql, vec![])
269}
270///
271///
272/// # Arguments
273///
274/// * `col1`:
275/// * `col2`:
276///
277/// returns: `Clause<Join>`
278///
279/// # Examples
280///
281/// ```
282/// use ngb_sqlbuilder::on_col_pair;
283///
284/// on_col_pair("t1.Col1","t2.Col1")
285/// // ON t1."Col1" = t2."Col1"
286/// ```
287pub fn on_col_pair<'q>(col1: &str, col2: &str) -> Clause<'q, Join> {
288    // let (mut sql, params) = self.unwrap();
289    let mut sql = String::new();
290    sql.push_str(" ON ");
291    // sql.push_str(&format_col(col1));
292    append_col(&mut sql, col1);
293    // sql.push_str(" = ");
294    sql.push('=');
295    // sql.push_str(&format_col(col2));
296    append_col(&mut sql, col2);
297    Clause::new(sql, vec![])
298}
299///
300///
301/// # Arguments
302///
303/// * `col_pairs`:
304///
305/// returns: `Clause<Join>`
306///
307/// # Examples
308///
309/// ```
310/// use ngb_sqlbuilder::on_col_pairs;
311///
312/// on_col_pairs(&[("t1.Col1","t2.Col1"),("t1.Col2","t2.Col2")])
313/// // ON (t1."Col1" = t2."Col1" AND t1."Col2" = t2."Col2")
314/// ```
315pub fn on_col_pairs<'q>(col_pairs: &[(&str, &str)]) -> Clause<'q, Join> {
316    let mut sql = String::new();
317    sql.push_str(" ON (");
318    let mut not_first = false;
319    for (col1, col2) in col_pairs {
320        if not_first {
321            sql.push_str(" AND ");
322        } else {
323            not_first = true;
324        }
325        sql.push_str(&format_col(col1));
326        // sql.push_str(" = ");
327        sql.push('=');
328        sql.push_str(&format_col(col2));
329    }
330    sql.push(')');
331    Clause::new(sql, vec![])
332}
333pub fn on_wrap(condition: Clause<Condition>) -> Clause<Join> {
334    // let (mut sql, params) = self.unwrap();
335    let mut sql = String::new();
336    let (cond_sql, _) = condition.unwrap();
337    sql.push_str(" ON (");
338    sql.push_str(&cond_sql);
339    sql.push(')');
340    Clause::new(sql, vec![])
341}
342
343pub(crate) fn __where<'q, T, C>(
344    clause: Clause<'q, T>,
345    name: &str,
346    condition: Clause<'q, Condition>,
347) -> Clause<'q, C> {
348    let mut sql = clause.sql;
349    let mut params = clause.params;
350    sql.push_str(" WHERE ");
351    sql.push_str(&format_col(name));
352    let (cond_sql, cond_params) = condition.unwrap();
353    sql.push_str(cond_sql.as_str());
354    params.extend(cond_params);
355    Clause::new(sql, params)
356}
357
358impl<'q> Clause<'q, Where> {
359    pub fn and(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
360        __and_col(self, name, condition)
361    }
362    pub fn or(self, name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
363        __or_col(self, name, condition)
364    }
365
366    pub fn and_nest(self, clause: Clause<'q, Condition>) -> Clause<'q, Where> {
367        __and_nest(self, clause)
368    }
369
370    pub fn or_nest(self, clause: Clause<'q, Condition>) -> Clause<'q, Where> {
371        __or_nest(self, clause)
372    }
373
374    pub fn order_by_cols(self, columns: &[&str]) -> Clause<'q, Order> {
375        __order_by_cols(self, columns)
376        // let (mut sql, params) = self.unwrap();
377        // let mut not_first = false;
378        // sql.push_str(" order by ");
379        // for col in columns {
380        //     if not_first {
381        //         sql.push_str(", ");
382        //     } else {
383        //         not_first = true;
384        //     }
385        //     sql.push_str(&__order(col));
386        // }
387        // Clause::new(sql, params)
388    }
389}
390fn __order_by_cols<'q, T>(clause: Clause<'q, T>, columns: &[&str]) -> Clause<'q, Order> {
391    let (mut sql, params) = clause.unwrap();
392    let mut not_first = false;
393    sql.push_str(" ORDER BY ");
394    for col in columns {
395        if not_first {
396            sql.push_str(", ");
397        } else {
398            not_first = true;
399        }
400        sql.push_str(&__order(col));
401    }
402    Clause::new(sql, params)
403}
404fn __order(col: &str) -> String {
405    let (col_name, dir) = {
406        let mut split_as = col.split(" ");
407        let col_name = split_as.next().unwrap_or_default();
408        let alias = split_as.next();
409        (wrap(col_name), alias)
410    };
411    if let Some(dir) = dir
412        && dir.eq_ignore_ascii_case("desc")
413    {
414        format!("{} {}", col_name, dir)
415    } else {
416        format!("{}", col_name)
417    }
418}
419
420// pub trait FromClause {}
421
422/// Begin `SELECT` statement
423///
424/// # Arguments
425///
426/// * `clause`:
427///
428/// returns: `Clause<Select>`
429///
430/// # Examples
431///
432/// ```
433/// use ngb_sqlbuilder::{cols, select};
434///
435/// select(cols(&["t.Id","t.Name","t.Value"]))
436/// // SQL: SELECT t."Id", t."Name", t."Value"
437/// ```
438pub fn select(clause: Clause<Column>) -> Clause<Select> {
439    Clause::new(format!("SELECT {}", clause.sql), clause.params)
440}
441
442pub fn select_all<'q>() -> Clause<'q, Select> {
443    Clause::new("SELECT *".to_string(), Vec::new())
444}
445
446/// Create column clause
447///
448/// # Arguments
449///
450/// * `expr`: column expression
451///
452/// returns: `Clause<Column>`
453///
454/// # Examples
455///
456/// ```
457/// use ngb_sqlbuilder::{col, select};
458///
459/// // Simple name
460/// select(col("col_name"))
461/// // SQL: SELECT "col_name"
462///
463/// // With table source
464/// select(col("t.ColName"))
465/// // SQL: SELECT t."ColName"
466///
467/// // With cast
468/// select(col("ColName::text"))
469/// // SQL: SELECT "ColName"::text
470///
471/// // With alias
472/// select(col("ColName as Alias"))
473/// // SQL: SELECT "ColName" as "Alias"
474///
475/// // Complete
476/// select(col("t.ColName::text as Alias"))
477/// // SQL: SELECT t."ColName"::text as "Alias"
478/// ```
479pub fn col<'q>(expr: &str) -> Clause<'q, Column> {
480    Clause::new(format_prim(expr), vec![])
481}
482
483/// Add value bind
484///
485/// # Arguments
486///
487/// * `value`: value, will be parameterized to `$n`
488///
489/// returns: `Clause<S>`
490///
491/// # Examples
492///
493/// ```
494/// use ngb_sqlbuilder::{select, val};
495///
496/// select(val(&123))
497/// // SQL: SELECT $1
498/// // Param: [123]
499/// ```
500pub fn val<'q, T: ToSql + Sync + 'q, S>(value: &'q T) -> Clause<'q, S> {
501    Clause::new("$".to_string(), vec![value])
502}
503pub fn where_col<'q>(name: &str, condition: Clause<'q, Condition>) -> Clause<'q, Where> {
504    let mut sql = String::new();
505    let (cond_sql, cond_params) = condition.unwrap();
506    sql.push(' ');
507    sql.push_str(&format_col(name));
508    sql.push_str(cond_sql.as_str());
509    Clause::new(sql, cond_params)
510}
511
512#[cfg(test)]
513mod tests {
514    use super::*;
515
516    #[test]
517    fn example() {
518        let user_id: i32 = 123;
519        let limit: i64 = 10;
520        let offset: i64 = 50;
521        let (sql, params) = select(cols(&[
522            "u.UserId as id",
523            "u.Name as name",
524            "p.Title as title",
525            "p.Content::text as body",
526        ]))
527        .from("User as u")
528        .left_join("Post as p", using_col("UserId"))
529        .where_col("u.UserId", eq(&user_id).cast("bigint"))
530        .order_by_cols(&["CreatedAt desc"])
531        .limit(&limit)
532        .offset(&offset)
533        .build();
534
535        println!("SQL: {}", sql);
536        println!("Params: {:?}", params);
537        // let rows = client.query(&sql, &params).await.unwrap();
538    }
539    #[test]
540    fn select_test() {
541        let user_id: i32 = 123;
542        let (sql, params) = select(cols(&["u.UserId as Id", "u.Name", "p.Title", "p.Content"]))
543            .from("User as u")
544            .left_join("Post as p", using_col("UserId"))
545            .left_join("Metadata as m", on_col_pair("p.Id", "m.PostId"))
546            .left_join(
547                "History as h",
548                on_col_pairs(&[("p.Id", "m.PostId"), ("p.Id", "h.PostId")]),
549            )
550            .where_col("u.UserId", eq(&user_id))
551            .and("t.Value", lt(&1000))
552            .and_nest(col("t.Asd").eq(&true).or_col("Xwe").eq(&false))
553            .order_by_cols(&["CreatedAt desc"])
554            .limit(&10)
555            .offset(&100)
556            .build();
557        // let column = col("a.foo as dfg");
558        println!("{}", sql);
559        println!("{:?}", params);
560    }
561
562    #[test]
563    fn nested() {
564        let q = select(cols(&["u.UserId", "u.Name"]))
565            .select(avg_col("t.Score").alias("AvgScore"))
566            .select(extract_col("epoch", "Timestamp").cast("int").alias("LogId"))
567            .from("User as u")
568            .where_cond(ands(&[
569                col("u.UserId").eq(&123),
570                ors(&[col("u.UserId").eq(&456), col_op("u.Id", ">=", &789)]),
571            ]))
572            .build();
573        println!("{}", q.0);
574    }
575
576    #[test]
577    fn branched() {
578        let opt1 = Some(1);
579        let mut query = select(cols(&["u.UserId", "u.Name", "p.Title", "p.Content"]))
580            .from("User as u")
581            .where_col("u.UserId", eq(&opt1)).debug();
582        println!("Display: {}", query);
583        println!("Debug: {:?}", query);
584        if let Some(val) = &opt1 {
585            query = query.and("u.PostId", eq(val));
586        }
587        query.and("u.Title", eq(&1));
588    }
589}
ngb_sqlbuilder/select.rs

ngb_sqlbuilder/
select.rs
