sql_query_builder/select/
select.rs

1use crate::{
2  behavior::TransactionQuery,
3  concat::Concat,
4  fmt,
5  structure::{LogicalOperator, Select, SelectClause},
6  utils::push_unique,
7};
8
9impl TransactionQuery for Select {}
10
11impl Select {
12  /// Gets the current state of the [Select] and returns it as string
13  ///
14  /// # Example
15  ///
16  /// ```
17  /// # use sql_query_builder as sql;
18  /// let select_query = sql::Select::new()
19  ///   .select("id")
20  ///   .from("users")
21  ///   .where_clause("login = 'foo'")
22  ///   .as_string();
23  ///
24  /// # let expected = "SELECT id FROM users WHERE login = 'foo'";
25  /// # assert_eq!(select_query, expected);
26  /// ```
27  ///
28  /// Output
29  ///
30  /// ```sql
31  /// SELECT id FROM users WHERE login = 'foo'
32  /// ```
33  pub fn as_string(&self) -> String {
34    let fmts = fmt::one_line();
35    self.concat(&fmts)
36  }
37
38  /// Prints the current state of the [Select] to the standard output in a more ease to read version.
39  /// This method is useful to debug complex queries or just print the generated SQL while you type
40  ///
41  /// # Example
42  ///
43  /// ```
44  /// # use sql_query_builder as sql;
45  /// let select = sql::Select::new()
46  ///   .select("*")
47  ///   .from("users")
48  ///   .where_clause("login = foo")
49  ///   .where_clause("active = true")
50  ///   .debug();
51  /// ```
52  ///
53  /// Prints to the standard output
54  ///
55  /// ```sql
56  /// -- ------------------------------------------------------------------------------
57  /// SELECT *
58  /// FROM users
59  /// WHERE login = foo AND active = true
60  /// -- ------------------------------------------------------------------------------
61  /// ```
62  ///
63  /// You can debug different parts of the select putting it in another position
64  ///
65  /// # Example
66  ///
67  /// ```
68  /// # use sql_query_builder as sql;
69  /// let select_query = sql::Select::new()
70  ///   .select("*")
71  ///   .from("users")
72  ///   .debug()
73  ///   .where_clause("login = foo")
74  ///   .where_clause("active = true")
75  ///   .as_string();
76  /// ```
77  ///
78  /// Prints to the standard output
79  ///
80  /// ```sql
81  /// -- ------------------------------------------------------------------------------
82  /// SELECT *
83  /// FROM users
84  /// -- ------------------------------------------------------------------------------
85  /// ```
86  pub fn debug(self) -> Self {
87    let fmts = fmt::multiline();
88    println!("{}", fmt::format(self.concat(&fmts), &fmts));
89    self
90  }
91
92  /// The `from` clause
93  ///
94  /// # Example
95  ///
96  /// ```
97  /// # use sql_query_builder as sql;
98  /// let select = sql::Select::new()
99  ///   .from("users");
100  ///
101  /// # let expected = "FROM users";
102  /// # assert_eq!(select.as_string(), expected);
103  /// ```
104  pub fn from(mut self, tables: &str) -> Self {
105    push_unique(&mut self._from, tables.trim().to_string());
106    self
107  }
108
109  /// The `group by` clause
110  ///
111  /// # Example
112  ///
113  /// ```
114  /// # use sql_query_builder as sql;
115  /// let select = sql::Select::new()
116  ///   .group_by("id");
117  ///
118  /// # let expected = "GROUP BY id";
119  /// # assert_eq!(select.as_string(), expected);
120  /// ```
121  pub fn group_by(mut self, column: &str) -> Self {
122    push_unique(&mut self._group_by, column.trim().to_string());
123    self
124  }
125
126  /// The `having` clause
127  ///
128  /// # Example
129  ///
130  /// ```
131  /// # use sql_query_builder as sql;
132  /// let select_query = sql::Select::new()
133  ///   .group_by("status")
134  ///   .having("status != 'disabled'")
135  ///   .as_string();
136  ///
137  /// # let expected = "GROUP BY status HAVING status != 'disabled'";
138  /// # assert_eq!(select_query, expected);
139  /// ```
140  ///
141  /// Output
142  ///
143  /// ```sql
144  /// GROUP BY status HAVING status != 'disabled'
145  /// ```
146  pub fn having(mut self, condition: &str) -> Self {
147    push_unique(&mut self._having, condition.trim().to_string());
148    self
149  }
150
151  /// The `cross join` clause
152  ///
153  /// # Example
154  ///
155  /// ```
156  /// # use sql_query_builder as sql;
157  /// let select_query = sql::Select::new()
158  ///   .from("users")
159  ///   .cross_join("addresses")
160  ///   .as_string();
161  ///
162  /// # let expected = "FROM users CROSS JOIN addresses";
163  /// # assert_eq!(select_query, expected);
164  /// ```
165  ///
166  /// Output
167  ///
168  /// ```sql
169  /// FROM users CROSS JOIN addresses
170  /// ```
171  pub fn cross_join(mut self, table: &str) -> Self {
172    let table = table.trim();
173    if table.is_empty() == false {
174      let join = format!("CROSS JOIN {table}");
175      push_unique(&mut self._join, join);
176    }
177    self
178  }
179
180  /// The `inner join` clause
181  ///
182  /// # Example
183  ///
184  /// ```
185  /// # use sql_query_builder as sql;
186  /// let select_query = sql::Select::new()
187  ///   .from("users")
188  ///   .inner_join("addresses on addresses.user_login = users.login")
189  ///   .as_string();
190  ///
191  /// # let expected = "FROM users INNER JOIN addresses on addresses.user_login = users.login";
192  /// # assert_eq!(select_query, expected);
193  /// ```
194  ///
195  /// Output
196  ///
197  /// ```sql
198  /// FROM users INNER JOIN addresses on addresses.user_login = users.login
199  /// ```
200  pub fn inner_join(mut self, table: &str) -> Self {
201    let table = table.trim();
202    if table.is_empty() == false {
203      let join = format!("INNER JOIN {table}");
204      push_unique(&mut self._join, join);
205    }
206    self
207  }
208
209  /// The `left join` clause
210  ///
211  /// # Example
212  ///
213  /// ```
214  /// # use sql_query_builder as sql;
215  /// let select_query = sql::Select::new()
216  ///   .from("users")
217  ///   .left_join("addresses on addresses.user_login = users.login")
218  ///   .as_string();
219  ///
220  /// # let expected = "FROM users LEFT JOIN addresses on addresses.user_login = users.login";
221  /// # assert_eq!(select_query, expected);
222  /// ```
223  ///
224  /// Output
225  ///
226  /// ```sql
227  /// FROM users LEFT JOIN addresses on addresses.user_login = users.login
228  /// ```
229  pub fn left_join(mut self, table: &str) -> Self {
230    let table = table.trim();
231    if table.is_empty() == false {
232      let join = format!("LEFT JOIN {table}");
233      push_unique(&mut self._join, join);
234    }
235    self
236  }
237
238  /// The `right join` clause
239  ///
240  /// # Example
241  ///
242  /// ```
243  /// # use sql_query_builder as sql;
244  /// let select_query = sql::Select::new()
245  ///   .from("users")
246  ///   .right_join("addresses on addresses.user_login = users.login")
247  ///   .as_string();
248  ///
249  /// # let expected = "FROM users RIGHT JOIN addresses on addresses.user_login = users.login";
250  /// # assert_eq!(select_query, expected);
251  /// ```
252  ///
253  /// Output
254  ///
255  /// ```sql
256  /// FROM users RIGHT JOIN addresses on addresses.user_login = users.login
257  /// ```
258  pub fn right_join(mut self, table: &str) -> Self {
259    let table = table.trim();
260    if table.is_empty() == false {
261      let join = format!("RIGHT JOIN {table}");
262      push_unique(&mut self._join, join);
263    }
264    self
265  }
266
267  /// Creates instance of the Select command
268  pub fn new() -> Self {
269    Self::default()
270  }
271
272  /// The `order by` clause
273  ///
274  /// # Example
275  ///
276  /// ```
277  /// # use sql_query_builder as sql;
278  /// let select = sql::Select::new()
279  ///   .select("name, login")
280  ///   .order_by("login asc");
281  ///
282  /// # let expected = "SELECT name, login ORDER BY login asc";
283  /// # assert_eq!(select.as_string(), expected);
284  /// ```
285  pub fn order_by(mut self, column: &str) -> Self {
286    push_unique(&mut self._order_by, column.trim().to_string());
287    self
288  }
289
290  /// Prints the current state of the [Select] to the standard output similar to debug method,
291  /// the difference is that this method prints in one line.
292  pub fn print(self) -> Self {
293    let fmts = fmt::one_line();
294    println!("{}", fmt::format(self.concat(&fmts), &fmts));
295    self
296  }
297
298  /// Adds at the beginning a raw SQL query.
299  ///
300  /// # Example
301  ///
302  /// ```
303  /// # use sql_query_builder as sql;
304  /// let raw_query = "select * from users";
305  ///
306  /// let select_query = sql::Select::new()
307  ///   .raw(raw_query)
308  ///   .where_clause("users.login = 'foo'")
309  ///   .as_string();
310  ///
311  /// # let expected = "select * from users WHERE users.login = 'foo'";
312  /// # assert_eq!(select_query, expected);
313  /// ```
314  ///
315  /// Output
316  ///
317  /// ```sql
318  /// select * from users WHERE users.login = 'foo'
319  /// ```
320  pub fn raw(mut self, raw_sql: &str) -> Self {
321    push_unique(&mut self._raw, raw_sql.trim().to_string());
322    self
323  }
324
325  /// Adds a raw SQL query after a specified clause.
326  ///
327  /// # Example
328  ///
329  /// ```
330  /// # use sql_query_builder as sql;
331  /// let raw_join = "inner join addresses addr on u.login = addr.owner_login";
332  ///
333  /// let select_query = sql::Select::new()
334  ///   .from("users u")
335  ///   .raw_after(sql::SelectClause::From, raw_join)
336  ///   .where_clause("u.login = foo")
337  ///   .as_string();
338  ///
339  /// # let expected = "\
340  /// #   FROM users u \
341  /// #   inner join addresses addr on u.login = addr.owner_login \
342  /// #   WHERE u.login = foo\
343  /// # ";
344  /// # assert_eq!(select_query, expected);
345  /// ```
346  ///
347  /// Output
348  ///
349  /// ```sql
350  /// FROM users u
351  /// inner join addresses addr on u.login = addr.owner_login
352  /// WHERE u.login = foo
353  /// ```
354  pub fn raw_after(mut self, clause: SelectClause, raw_sql: &str) -> Self {
355    self._raw_after.push((clause, raw_sql.trim().to_string()));
356    self
357  }
358
359  /// Adds a raw SQL query before a specified clause.
360  ///
361  /// # Example
362  ///
363  /// ```
364  /// # use sql_query_builder as sql;
365  /// let raw_query = "from users";
366  ///
367  /// let select_query = sql::Select::new()
368  ///   .raw_before(sql::SelectClause::Where, raw_query)
369  ///   .where_clause("users.login = 'foo'")
370  ///   .as_string();
371  ///
372  /// # let expected = "\
373  /// #   from users \
374  /// #   WHERE users.login = 'foo'\
375  /// # ";
376  /// # assert_eq!(select_query, expected);
377  /// ```
378  ///
379  /// Output
380  ///
381  /// ```sql
382  /// from users
383  /// WHERE users.login = 'foo'
384  /// ```
385  pub fn raw_before(mut self, clause: SelectClause, raw_sql: &str) -> Self {
386    self._raw_before.push((clause, raw_sql.trim().to_string()));
387    self
388  }
389
390  /// The `select` clause
391  ///
392  /// # Example
393  ///
394  /// ```
395  /// # use sql_query_builder as sql;
396  /// let select = sql::Select::new()
397  ///   .select("count(id)");
398  ///
399  /// # let expected = "SELECT count(id)";
400  /// # assert_eq!(select.as_string(), expected);
401  /// ```
402  pub fn select(mut self, column: &str) -> Self {
403    push_unique(&mut self._select, column.trim().to_string());
404    self
405  }
406
407  /// The method will concatenate multiples calls using the `and` operator. This method is un alias of `where_clause`.
408  ///
409  /// # Example
410  ///
411  /// ```
412  /// # use sql_query_builder as sql;
413  /// let select_query = sql::Select::new()
414  ///   .from("carts")
415  ///   .where_clause("login = $1")
416  ///   .where_and("session_id = $2")
417  ///   .where_and("created_at >= current_date")
418  ///   .as_string();
419  ///
420  /// # let expected = "FROM carts WHERE login = $1 AND session_id = $2 AND created_at >= current_date";
421  /// # assert_eq!(select_query, expected);
422  /// ```
423  ///
424  /// Outputs
425  ///
426  /// ```sql
427  /// FROM carts
428  /// WHERE
429  ///   login = $1
430  ///   AND session_id = $2
431  ///   AND created_at >= current_date
432  /// ```
433  pub fn where_and(self, condition: &str) -> Self {
434    self.where_clause(condition)
435  }
436
437  /// The `where` clause, this method will concatenate multiples calls using the `and` operator.
438  /// If you intended to use the `or` operator you should use the [where_or](Select::where_or) method
439  ///
440  /// # Example
441  ///
442  /// ```
443  /// # use sql_query_builder as sql;
444  /// let select_query = sql::Select::new()
445  ///   .where_clause("login = $1")
446  ///   .where_clause("status = 'active'")
447  ///   .as_string();
448  ///
449  /// # let expected = "WHERE login = $1 AND status = 'active'";
450  /// # assert_eq!(select_query, expected);
451  /// ```
452  ///
453  /// Outputs
454  ///
455  /// ```sql
456  /// WHERE
457  ///   login = $1
458  ///   AND status = 'active'
459  /// ```
460  pub fn where_clause(mut self, condition: &str) -> Self {
461    push_unique(&mut self._where, (LogicalOperator::And, condition.trim().to_string()));
462    self
463  }
464
465  /// The `where` clause that concatenate multiples calls using the OR operator.
466  /// If you intended to use the `and` operator you should use the [where_clause](Select::where_clause) method
467  ///
468  /// # Example
469  ///
470  /// ```
471  /// # use sql_query_builder as sql;
472  /// let select_query = sql::Select::new()
473  ///   .where_clause("login = 'foo'")
474  ///   .where_or("login = 'bar'")
475  ///   .as_string();
476  ///
477  /// # let expected = "WHERE login = 'foo' OR login = 'bar'";
478  /// # assert_eq!(select_query, expected);
479  /// ```
480  ///
481  /// Output
482  ///
483  /// ```sql
484  /// WHERE
485  ///   login = 'foo'
486  ///   OR login = 'bar'
487  /// ```
488  ///
489  /// # Example
490  ///
491  /// ```
492  /// # use sql_query_builder as sql;
493  /// let select_query = sql::Select::new()
494  ///   .where_clause("login = 'foo'")
495  ///   .where_or("login = 'bar'")
496  ///   .where_clause("login = 'joe'")
497  ///   .as_string();
498  ///
499  /// # let expected = "\
500  /// #   WHERE \
501  /// #     login = 'foo' \
502  /// #     OR login = 'bar' \
503  /// #     AND login = 'joe'\
504  /// # ";
505  /// # assert_eq!(select_query, expected);
506  /// ```
507  /// Output
508  ///
509  /// ```sql
510  /// WHERE
511  ///   login = 'foo'
512  ///   OR login = 'bar'
513  ///   AND login = 'joe'
514  /// ```
515  ///
516  /// # Example
517  ///
518  /// If the `where_or` was the first clause then the operator will be ignored
519  ///
520  /// ```
521  /// # use sql_query_builder as sql;
522  /// let select_query = sql::Select::new()
523  ///   .where_or("login = 'joe'")
524  ///   .where_clause("login = 'foo'")
525  ///   .as_string();
526  ///
527  /// # let expected = "WHERE login = 'joe' AND login = 'foo'";
528  /// # assert_eq!(select_query, expected);
529  /// ```
530  ///
531  /// Output
532  ///
533  /// ```sql
534  /// WHERE
535  ///   login = 'joe'
536  ///   AND login = 'foo'
537  /// ```
538  pub fn where_or(mut self, condition: &str) -> Self {
539    push_unique(&mut self._where, (LogicalOperator::Or, condition.trim().to_string()));
540    self
541  }
542
543  /// The `window` clause
544  ///
545  /// # Example
546  ///
547  /// ```
548  /// # use sql_query_builder as sql;
549  /// let select_query = sql::Select::new()
550  ///   .window("win as (partition by department)")
551  ///   .as_string();
552  ///
553  /// # let expected = "WINDOW win as (partition by department)";
554  /// # assert_eq!(select_query, expected);
555  /// ```
556  ///
557  /// Output
558  ///
559  /// ```sql
560  /// WINDOW win as (partition by department)
561  /// ```
562  pub fn window(mut self, name: &str) -> Self {
563    push_unique(&mut self._window, name.trim().to_string());
564    self
565  }
566}
567
568#[cfg(any(feature = "postgresql", feature = "sqlite"))]
569use crate::behavior::WithQuery;
570
571#[cfg(any(feature = "postgresql", feature = "sqlite"))]
572impl WithQuery for Select {}
573
574#[cfg(any(doc, feature = "postgresql", feature = "sqlite"))]
575#[cfg_attr(docsrs, doc(cfg(feature = "postgresql")))]
576#[cfg_attr(docsrs, doc(cfg(feature = "sqlite")))]
577impl Select {
578  /// The `except` clause
579  ///
580  /// # Example
581  ///
582  /// ```
583  /// # #[cfg(any(feature = "postgresql", feature = "sqlite"))]
584  /// # {
585  /// # use sql_query_builder as sql;
586  /// let select_users = sql::Select::new()
587  ///   .select("login")
588  ///   .from("users");
589  ///
590  /// let select_inactives = sql::Select::new()
591  ///   .select("login")
592  ///   .from("users")
593  ///   .where_clause("status = 'inactive'");
594  ///
595  /// let select_query = select_users.except(select_inactives).as_string();
596  ///
597  /// # let expected = "\
598  /// #   (SELECT login FROM users) \
599  /// #   EXCEPT \
600  /// #   (SELECT login FROM users WHERE status = 'inactive')\
601  /// # ";
602  /// # assert_eq!(select_query, expected);
603  /// # }
604  /// ```
605  ///
606  /// Output
607  ///
608  /// ```sql
609  /// (SELECT login FROM users)
610  /// EXCEPT
611  /// (SELECT login FROM users WHERE status = 'inactive')
612  /// ```
613  pub fn except(mut self, select: Self) -> Self {
614    self._except.push(select);
615    self
616  }
617
618  /// The `intersect` clause
619  ///
620  /// # Example
621  ///
622  /// ```
623  /// # #[cfg(any(feature = "postgresql", feature = "sqlite"))]
624  /// # {
625  /// # use sql_query_builder as sql;
626  /// let select_users = sql::Select::new()
627  ///   .select("login")
628  ///   .from("users");
629  ///
630  /// let select_inactives = sql::Select::new()
631  ///   .select("login")
632  ///   .from("users")
633  ///   .where_clause("status = 'inactive'");
634  ///
635  /// let select_query = select_users.intersect(select_inactives).as_string();
636  ///
637  /// # let expected = "\
638  /// #   (SELECT login FROM users) \
639  /// #   INTERSECT \
640  /// #   (SELECT login FROM users WHERE status = 'inactive')\
641  /// # ";
642  /// # assert_eq!(select_query, expected);
643  /// # }
644  /// ```
645  ///
646  /// Output
647  ///
648  /// ```sql
649  /// (SELECT login FROM users)
650  /// INTERSECT
651  /// (SELECT login FROM users WHERE status = 'inactive')
652  /// ```
653  pub fn intersect(mut self, select: Self) -> Self {
654    self._intersect.push(select);
655    self
656  }
657
658  /// The `limit` clause, this method overrides the previous value
659  ///
660  /// # Example
661  ///
662  /// ```
663  /// # #[cfg(any(feature = "postgresql", feature = "sqlite"))]
664  /// # {
665  /// # use sql_query_builder as sql;
666  /// let select = sql::Select::new()
667  ///   .limit("123");
668  ///
669  /// let select = sql::Select::new()
670  ///   .limit("1000")
671  ///   .limit("123");
672  ///
673  /// # let expected = "LIMIT 123";
674  /// # assert_eq!(select.as_string(), expected);
675  /// # }
676  /// ```
677  pub fn limit(mut self, num: &str) -> Self {
678    self._limit = num.trim().to_string();
679    self
680  }
681
682  /// The `offset` clause, this method overrides the previous value
683  ///
684  /// # Example
685  ///
686  /// ```
687  /// # #[cfg(any(feature = "postgresql", feature = "sqlite"))]
688  /// # {
689  /// # use sql_query_builder as sql;
690  /// let select = sql::Select::new()
691  ///   .offset("1500");
692  ///
693  /// let select = sql::Select::new()
694  ///   .offset("1000")
695  ///   .offset("1500");
696  ///
697  /// # let expected = "OFFSET 1500";
698  /// # assert_eq!(select.as_string(), expected);
699  /// # }
700  /// ```
701  pub fn offset(mut self, num: &str) -> Self {
702    self._offset = num.trim().to_string();
703    self
704  }
705
706  /// The `union` clause
707  ///
708  /// # Example
709  ///
710  /// ```
711  /// # #[cfg(any(feature = "postgresql", feature = "sqlite"))]
712  /// # {
713  /// # use sql_query_builder as sql;
714  /// let select_users = sql::Select::new()
715  ///   .select("login")
716  ///   .from("users");
717  ///
718  /// let select_inactives = sql::Select::new()
719  ///   .select("login")
720  ///   .from("users")
721  ///   .where_clause("status = 'inactive'");
722  ///
723  /// let select_query = select_users.union(select_inactives).as_string();
724  ///
725  /// # let expected = "\
726  /// #   (SELECT login FROM users) \
727  /// #   UNION \
728  /// #   (SELECT login FROM users WHERE status = 'inactive')\
729  /// # ";
730  /// # assert_eq!(select_query, expected);
731  /// # }
732  /// ```
733  ///
734  /// Output
735  ///
736  /// ```sql
737  /// (SELECT login FROM users)
738  /// UNION
739  /// (SELECT login FROM users WHERE status = 'inactive')
740  /// ```
741  pub fn union(mut self, select: Self) -> Self {
742    self._union.push(select);
743    self
744  }
745
746  /// The `with` clause
747  ///
748  /// # Example
749  ///
750  /// ```
751  /// # #[cfg(any(feature = "postgresql", feature = "sqlite"))]
752  /// # {
753  /// # use sql_query_builder as sql;
754  /// let logins = sql::Select::new()
755  ///   .select("login")
756  ///   .from("users")
757  ///   .where_clause("id in ($1)");
758  ///
759  /// let select = sql::Select::new()
760  ///   .with("logins", logins)
761  ///   .select("name, price")
762  ///   .from("orders")
763  ///   .where_clause("owner_login in (select * from logins)")
764  ///   .debug();
765  ///
766  /// # let expected = "\
767  /// #   WITH logins AS (\
768  /// #     SELECT login \
769  /// #     FROM users \
770  /// #     WHERE id in ($1)\
771  /// #   ) \
772  /// #   SELECT name, price \
773  /// #   FROM orders \
774  /// #   WHERE owner_login in (select * from logins)\
775  /// # ";
776  /// # assert_eq!(select.as_string(), expected);
777  /// # }
778  /// ```
779  ///
780  /// Prints to the standard output
781  ///
782  /// ```sql
783  /// -- ------------------------------------------------------------------------------
784  /// WITH
785  /// logins AS (
786  ///   SELECT login
787  ///   FROM users
788  ///   WHERE id in ($1)
789  /// )
790  /// SELECT name, price
791  /// FROM orders
792  /// WHERE owner_login in (select * from logins)
793  /// -- ------------------------------------------------------------------------------
794  /// ```
795  #[cfg(any(feature = "postgresql", feature = "sqlite"))]
796  pub fn with(mut self, name: &str, query: impl WithQuery + Send + Sync + 'static) -> Self {
797    self._with.push((name.trim().to_string(), std::sync::Arc::new(query)));
798    self
799  }
800}
801
802impl std::fmt::Display for Select {
803  fn fmt(&self, f: &mut std::fmt::Formatter) -> std::fmt::Result {
804    write!(f, "{}", self.as_string())
805  }
806}
807
808impl std::fmt::Debug for Select {
809  fn fmt(&self, f: &mut std::fmt::Formatter) -> std::fmt::Result {
810    let fmts = fmt::multiline();
811    write!(f, "{}", fmt::format(self.concat(&fmts), &fmts))
812  }
813}