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}