postgres_querybuilder/
select_builder.rs

1use crate::bucket::Bucket;
2use crate::prelude::*;
3use postgres_types::ToSql;
4
5pub struct SelectBuilder {
6    with_queries: Vec<(String, String)>,
7    columns: Vec<String>,
8    from_table: String,
9    conditions: Vec<String>,
10    joins: Vec<Join>,
11    groups: Vec<String>,
12    order: Vec<Order>,
13    limit: Option<String>,
14    offset: Option<String>,
15    params: Bucket,
16}
17
18impl SelectBuilder {
19    /// Create a new select query for a given table
20    ///
21    /// # Examples
22    ///
23    /// ```
24    /// use postgres_querybuilder::SelectBuilder;
25    ///
26    /// let mut builder = SelectBuilder::new("users");
27    /// ```
28    pub fn new(from: &str) -> Self {
29        SelectBuilder {
30            with_queries: vec![],
31            columns: vec![],
32            from_table: from.into(),
33            conditions: vec![],
34            joins: vec![],
35            groups: vec![],
36            order: vec![],
37            limit: None,
38            offset: None,
39            params: Bucket::new(),
40        }
41    }
42
43    /// Add a column to select
44    ///
45    /// # Examples
46    ///
47    /// ```
48    /// use postgres_querybuilder::SelectBuilder;
49    /// use postgres_querybuilder::prelude::QueryBuilder;
50    ///
51    /// let mut builder = SelectBuilder::new("users");
52    /// builder.select("id");
53    /// builder.select("email");
54    ///
55    /// assert_eq!(builder.get_query(), "SELECT id, email FROM users");
56    /// ```
57    pub fn select(&mut self, column: &str) -> &mut Self {
58        self.columns.push(column.to_string());
59        self
60    }
61
62    /// Add a raw where condition
63    ///
64    /// # Examples
65    ///
66    /// ```
67    /// use postgres_querybuilder::SelectBuilder;
68    /// use postgres_querybuilder::prelude::QueryBuilder;
69    ///
70    /// let mut builder = SelectBuilder::new("users");
71    /// builder.add_where_raw("something IS NULL".into());
72    ///
73    /// assert_eq!(builder.get_query(), "SELECT * FROM users WHERE something IS NULL");
74    /// ```
75    pub fn add_where_raw(&mut self, raw: String) -> &mut Self {
76        self.conditions.push(raw);
77        self
78    }
79}
80
81impl SelectBuilder {
82    fn with_queries_to_query(&self) -> Option<String> {
83        if self.with_queries.len() > 0 {
84            let result: Vec<String> = self
85                .with_queries
86                .iter()
87                .map(|item| format!("{} AS ({})", item.0, item.1))
88                .collect();
89            Some(format!("WITH {}", result.join(", ")))
90        } else {
91            None
92        }
93    }
94
95    fn select_to_query(&self) -> String {
96        let columns = if self.columns.len() == 0 {
97            "*".to_string()
98        } else {
99            self.columns.join(", ")
100        };
101        format!("SELECT {}", columns)
102    }
103
104    fn from_to_query(&self) -> String {
105        format!("FROM {}", self.from_table)
106    }
107
108    fn where_to_query(&self) -> Option<String> {
109        if self.conditions.len() > 0 {
110            let result = self.conditions.join(" AND ");
111            Some(format!("WHERE {}", result))
112        } else {
113            None
114        }
115    }
116
117    fn group_by_to_query(&self) -> Option<String> {
118        if self.groups.len() > 0 {
119            let result = self.groups.join(", ");
120            Some(format!("GROUP BY {}", result))
121        } else {
122            None
123        }
124    }
125
126    fn order_by_to_query(&self) -> Option<String> {
127        if self.order.len() > 0 {
128            let result: Vec<String> = self.order.iter().map(|order| order.to_string()).collect();
129            Some(format!("ORDER BY {}", result.join(", ")))
130        } else {
131            None
132        }
133    }
134
135    fn limit_to_query(&self) -> Option<String> {
136        match self.limit.as_ref() {
137            Some(limit) => Some(format!("LIMIT {}", limit)),
138            None => None,
139        }
140    }
141
142    fn offset_to_query(&self) -> Option<String> {
143        match self.offset.as_ref() {
144            Some(offset) => Some(format!("OFFSET {}", offset)),
145            None => None,
146        }
147    }
148}
149
150impl QueryBuilder for SelectBuilder {
151    fn add_param<T: 'static + ToSql + Sync + Clone>(&mut self, value: T) -> usize {
152        self.params.push(value)
153    }
154
155    fn get_query(&self) -> String {
156        let mut sections: Vec<String> = vec![];
157        match self.with_queries_to_query() {
158            Some(value) => sections.push(value),
159            None => (),
160        };
161        sections.push(self.select_to_query());
162        sections.push(self.from_to_query());
163        match self.where_to_query() {
164            Some(value) => sections.push(value),
165            None => (),
166        };
167        match self.group_by_to_query() {
168            Some(value) => sections.push(value),
169            None => (),
170        };
171        match self.order_by_to_query() {
172            Some(value) => sections.push(value),
173            None => (),
174        };
175        match self.limit_to_query() {
176            Some(value) => sections.push(value),
177            None => (),
178        };
179        match self.offset_to_query() {
180            Some(value) => sections.push(value),
181            None => (),
182        };
183        sections.join(" ")
184    }
185
186    fn get_ref_params(self) -> Vec<&'static (dyn ToSql + Sync)> {
187        self.params.get_refs()
188    }
189}
190
191impl QueryBuilderWithWhere for SelectBuilder {
192    fn where_condition(&mut self, raw: &str) -> &mut Self {
193        self.conditions.push(raw.to_string());
194        self
195    }
196}
197
198impl QueryBuilderWithLimit for SelectBuilder {
199    fn limit(&mut self, limit: i64) -> &mut Self {
200        let index = self.params.push(limit);
201        self.limit = Some(format!("${}", index));
202        self
203    }
204}
205
206impl QueryBuilderWithOffset for SelectBuilder {
207    fn offset(&mut self, offset: i64) -> &mut Self {
208        let index = self.params.push(offset);
209        self.offset = Some(format!("${}", index));
210        self
211    }
212}
213
214impl QueryBuilderWithJoin for SelectBuilder {
215    fn inner_join(&mut self, table_name: &str, relation: &str) -> &mut Self {
216        self.joins
217            .push(Join::Inner(table_name.to_string(), relation.to_string()));
218        self
219    }
220
221    fn left_join(&mut self, table_name: &str, relation: &str) -> &mut Self {
222        self.joins.push(Join::LeftOuter(
223            table_name.to_string(),
224            relation.to_string(),
225        ));
226        self
227    }
228
229    fn left_outer_join(&mut self, table_name: &str, relation: &str) -> &mut Self {
230        self.joins
231            .push(Join::Left(table_name.to_string(), relation.to_string()));
232        self
233    }
234}
235
236impl QueryBuilderWithGroupBy for SelectBuilder {
237    fn group_by(&mut self, field: &str) -> &mut Self {
238        self.groups.push(field.to_string());
239        self
240    }
241}
242
243impl QueryBuilderWithOrder for SelectBuilder {
244    /// Add order attribute to request
245    ///
246    /// # Examples
247    ///
248    /// ```
249    /// use postgres_querybuilder::SelectBuilder;
250    /// use postgres_querybuilder::prelude::Order;
251    /// use postgres_querybuilder::prelude::QueryBuilder;
252    /// use postgres_querybuilder::prelude::QueryBuilderWithOrder;
253    ///
254    /// let mut builder = SelectBuilder::new("users");
255    /// builder.order_by(Order::Asc("name".into()));
256    ///
257    /// assert_eq!(builder.get_query(), "SELECT * FROM users ORDER BY name ASC");
258    /// ```
259    fn order_by(&mut self, field: Order) {
260        self.order.push(field);
261    }
262}
263
264impl QueryBuilderWithQueries for SelectBuilder {
265    fn with_query(&mut self, name: &str, query: &str) -> &mut Self {
266        self.with_queries.push((name.into(), query.into()));
267        self
268    }
269}
270
271#[cfg(test)]
272pub mod test {
273    use super::*;
274
275    #[test]
276    fn from_scratch() {
277        let builder = SelectBuilder::new("publishers");
278        assert_eq!(builder.get_query(), "SELECT * FROM publishers");
279    }
280
281    #[test]
282    fn with_columns() {
283        let mut builder = SelectBuilder::new("publishers");
284        builder.select("id");
285        builder.select("name");
286        assert_eq!(builder.get_query(), "SELECT id, name FROM publishers");
287    }
288
289    #[test]
290    fn with_limit() {
291        let mut builder = SelectBuilder::new("publishers");
292        builder.select("id");
293        builder.limit(10);
294        assert_eq!(builder.get_query(), "SELECT id FROM publishers LIMIT $1");
295    }
296
297    #[test]
298    fn with_limit_offset() {
299        let mut builder = SelectBuilder::new("publishers");
300        builder.select("id");
301        builder.limit(10);
302        builder.offset(5);
303        assert_eq!(
304            builder.get_query(),
305            "SELECT id FROM publishers LIMIT $1 OFFSET $2"
306        );
307    }
308
309    #[test]
310    fn with_where_eq() {
311        let mut builder = SelectBuilder::new("publishers");
312        builder.select("id");
313        builder.select("name");
314        builder.where_eq("trololo", 42);
315        builder.where_eq("tralala", true);
316        builder.where_ne("trululu", "trololo");
317        assert_eq!(
318            builder.get_query(),
319            "SELECT id, name FROM publishers WHERE trololo = $1 AND tralala = $2 AND trululu <> $3"
320        );
321    }
322
323    #[test]
324    fn with_order() {
325        let mut builder = SelectBuilder::new("publishers");
326        builder.select("id");
327        builder.order_by(Order::Asc("id".into()));
328        builder.order_by(Order::Desc("name".into()));
329        assert_eq!(
330            builder.get_query(),
331            "SELECT id FROM publishers ORDER BY id ASC, name DESC"
332        );
333    }
334
335    #[test]
336    fn with_subquery() {
337        let mut builder = SelectBuilder::new("publishers_view");
338        builder.with_query(
339            "publishers_count",
340            "SELECT publisher_id, count(*) FROM articles GROUP BY publisher_id",
341        );
342        builder.with_query("publishers_subquery", "SELECT * FROM publishers");
343        assert_eq!(
344      builder.get_query(),
345      "WITH publishers_count AS (SELECT publisher_id, count(*) FROM articles GROUP BY publisher_id), publishers_subquery AS (SELECT * FROM publishers) SELECT * FROM publishers_view"
346    );
347    }
348}
postgres_querybuilder/select_builder.rs

postgres_querybuilder/
select_builder.rs
