gluesql_core/ast_builder/select/join/
root.rs

1use {
2    super::{JoinConstraintData, JoinOperatorType},
3    crate::{
4        ast::{Join, JoinExecutor, JoinOperator, Select, TableAlias, TableFactor},
5        ast_builder::{
6            ExprList, ExprNode, FilterNode, GroupByNode, HashJoinNode, JoinConstraintNode,
7            LimitNode, OffsetNode, OrderByExprList, OrderByNode, ProjectNode, QueryNode,
8            SelectItemList, SelectNode, TableFactorNode, select::Prebuild,
9        },
10        result::Result,
11    },
12};
13
14#[derive(Clone, Debug)]
15pub enum PrevNode<'a> {
16    Select(SelectNode<'a>),
17    Join(Box<JoinNode<'a>>),
18    JoinConstraint(Box<JoinConstraintNode<'a>>),
19    HashJoin(Box<HashJoinNode<'a>>),
20}
21
22impl Prebuild<Select> for PrevNode<'_> {
23    fn prebuild(self) -> Result<Select> {
24        match self {
25            Self::Select(node) => node.prebuild(),
26            Self::Join(node) => node.prebuild(),
27            Self::JoinConstraint(node) => node.prebuild(),
28            Self::HashJoin(node) => node.prebuild(),
29        }
30    }
31}
32
33impl<'a> From<SelectNode<'a>> for PrevNode<'a> {
34    fn from(node: SelectNode<'a>) -> Self {
35        PrevNode::Select(node)
36    }
37}
38
39impl<'a> From<JoinNode<'a>> for PrevNode<'a> {
40    fn from(node: JoinNode<'a>) -> Self {
41        PrevNode::Join(Box::new(node))
42    }
43}
44
45impl<'a> From<JoinConstraintNode<'a>> for PrevNode<'a> {
46    fn from(node: JoinConstraintNode<'a>) -> Self {
47        PrevNode::JoinConstraint(Box::new(node))
48    }
49}
50
51impl<'a> From<HashJoinNode<'a>> for PrevNode<'a> {
52    fn from(node: HashJoinNode<'a>) -> Self {
53        PrevNode::HashJoin(Box::new(node))
54    }
55}
56
57#[derive(Clone, Debug)]
58pub struct JoinNode<'a> {
59    prev_node: PrevNode<'a>,
60    relation: TableFactor,
61    join_operator_type: JoinOperatorType,
62}
63
64impl<'a> JoinNode<'a> {
65    pub fn new<N: Into<PrevNode<'a>>>(
66        prev_node: N,
67        name: String,
68        alias: Option<String>,
69        join_operator_type: JoinOperatorType,
70    ) -> Self {
71        Self {
72            prev_node: prev_node.into(),
73            join_operator_type,
74            relation: match alias {
75                Some(alias) => TableFactor::Table {
76                    name,
77                    alias: Some(TableAlias {
78                        name: alias,
79                        columns: vec![],
80                    }),
81                    index: None,
82                },
83                None => TableFactor::Table {
84                    name,
85                    alias: None,
86                    index: None,
87                },
88            },
89        }
90    }
91
92    pub fn on<T: Into<ExprNode<'a>>>(self, expr: T) -> JoinConstraintNode<'a> {
93        JoinConstraintNode::new(self, expr)
94    }
95
96    #[must_use]
97    pub fn join(self, table_name: &str) -> JoinNode<'a> {
98        JoinNode::new(self, table_name.to_owned(), None, JoinOperatorType::Inner)
99    }
100
101    #[must_use]
102    pub fn join_as(self, table_name: &str, alias: &str) -> JoinNode<'a> {
103        JoinNode::new(
104            self,
105            table_name.to_owned(),
106            Some(alias.to_owned()),
107            JoinOperatorType::Inner,
108        )
109    }
110
111    #[must_use]
112    pub fn left_join(self, table_name: &str) -> JoinNode<'a> {
113        JoinNode::new(self, table_name.to_owned(), None, JoinOperatorType::Left)
114    }
115
116    #[must_use]
117    pub fn left_join_as(self, table_name: &str, alias: &str) -> JoinNode<'a> {
118        JoinNode::new(
119            self,
120            table_name.to_owned(),
121            Some(alias.to_owned()),
122            JoinOperatorType::Left,
123        )
124    }
125
126    pub fn hash_executor<T: Into<ExprNode<'a>>, U: Into<ExprNode<'a>>>(
127        self,
128        key_expr: T,
129        value_expr: U,
130    ) -> HashJoinNode<'a> {
131        HashJoinNode::new(self, key_expr, value_expr)
132    }
133
134    pub fn project<T: Into<SelectItemList<'a>>>(self, select_items: T) -> ProjectNode<'a> {
135        ProjectNode::new(self, select_items)
136    }
137
138    pub fn group_by<T: Into<ExprList<'a>>>(self, expr_list: T) -> GroupByNode<'a> {
139        GroupByNode::new(self, expr_list)
140    }
141
142    pub fn offset<T: Into<ExprNode<'a>>>(self, expr: T) -> OffsetNode<'a> {
143        OffsetNode::new(self, expr)
144    }
145
146    pub fn limit<T: Into<ExprNode<'a>>>(self, expr: T) -> LimitNode<'a> {
147        LimitNode::new(self, expr)
148    }
149
150    pub fn filter<T: Into<ExprNode<'a>>>(self, expr: T) -> FilterNode<'a> {
151        FilterNode::new(self, expr)
152    }
153
154    pub fn order_by<T: Into<OrderByExprList<'a>>>(self, order_by_exprs: T) -> OrderByNode<'a> {
155        OrderByNode::new(self, order_by_exprs)
156    }
157
158    pub fn alias_as(self, table_alias: &'a str) -> TableFactorNode<'a> {
159        QueryNode::JoinNode(self).alias_as(table_alias)
160    }
161
162    pub fn prebuild_for_constraint(self) -> Result<JoinConstraintData> {
163        Ok(JoinConstraintData {
164            select: self.prev_node.prebuild()?,
165            relation: self.relation,
166            operator_type: self.join_operator_type,
167            executor: JoinExecutor::NestedLoop,
168        })
169    }
170
171    pub fn prebuild_for_hash_join(self) -> Result<(Select, TableFactor, JoinOperator)> {
172        let select_data = self.prev_node.prebuild()?;
173        let join_operator = JoinOperator::from(self.join_operator_type);
174
175        Ok((select_data, self.relation, join_operator))
176    }
177}
178
179impl Prebuild<Select> for JoinNode<'_> {
180    fn prebuild(self) -> Result<Select> {
181        let mut select: Select = self.prev_node.prebuild()?;
182
183        select.from.joins.push(Join {
184            relation: self.relation,
185            join_operator: JoinOperator::from(self.join_operator_type),
186            join_executor: JoinExecutor::NestedLoop,
187        });
188
189        Ok(select)
190    }
191}
192
193#[cfg(test)]
194mod tests {
195    use {
196        crate::ast_builder::{Build, table, test},
197        pretty_assertions::assert_eq,
198    };
199
200    #[test]
201    fn inner_join() {
202        // select node -> join node -> join constraint node
203        let actual = table("Item")
204            .select()
205            .join_as("Player", "p")
206            .on("p.id = Item.player_id")
207            .filter("p.id = 1")
208            .build();
209        let expected = "
210        SELECT * FROM Item INNER JOIN Player AS p ON p.id = Item.player_id WHERE p.id = 1;
211        ";
212        test(&actual, expected);
213
214        // select node -> join node ->  join constraint node
215        let actual = table("Item")
216            .select()
217            .join_as("Player", "p")
218            .on("p.id = Item.player_id")
219            .filter("p.id = 1")
220            .project(vec!["p.id", "p.name", "Item.id"])
221            .build();
222        let expected = "
223        SELECT p.id, p.name, Item.id FROM Item INNER JOIN Player AS p ON p.id = Item.player_id WHERE p.id = 1;
224        ";
225        test(&actual, expected);
226
227        // select node -> join node ->  build
228        let actual = table("Item").select().join_as("Player", "p").build();
229        let expected = "
230        SELECT * FROM Item INNER JOIN Player AS p;
231        ";
232        test(&actual, expected);
233
234        // join node -> join constraint node -> join node -> join constraint node
235        let actual = table("students")
236            .select()
237            .join("marks")
238            .on("students.id = marks.id")
239            .join("attendance")
240            .on("marks.id = attendance.id")
241            .filter("attendance.attendance >= 75")
242            .project(vec![
243                "students.id",
244                "students.name",
245                "marks.rank",
246                "attendance.attendance",
247            ])
248            .build();
249        let expected = "
250            SELECT students.id, students.name, marks.rank, attendance.attendance
251            FROM students
252            INNER JOIN marks ON students.id=marks.id
253            INNER JOIN attendance on marks.id=attendance.id
254            WHERE attendance.attendance >= 75;
255        ";
256        test(&actual, expected);
257
258        // select node -> join node -> project node
259        let actual = table("Orders")
260            .select()
261            .join("Customers")
262            .project(vec![
263                "Orders.OrderID",
264                "Customers.CustomerName",
265                "Orders.OrderDate",
266            ])
267            .build();
268        let expected = "
269            SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
270            FROM Orders INNER JOIN Customers
271        ";
272        test(&actual, expected);
273    }
274
275    #[test]
276    fn left_join() {
277        // select node -> left join node -> join constraint node
278        let actual = table("player")
279            .select()
280            .left_join("item")
281            .on("player.id = item.id")
282            .project(vec!["player.id", "item.id"])
283            .build();
284        let expected = "
285            SELECT player.id, item.id
286            FROM player
287            LEFT JOIN item
288            ON player.id = item.id
289        ";
290        test(&actual, expected);
291
292        // select node -> left join node -> join constraint node -> left join node
293        let actual = table("Item")
294            .select()
295            .left_join("Player")
296            .on("Player.id = Item.player_id")
297            .left_join_as("Player", "p1")
298            .on("p1.id = Item.player_id")
299            .left_join_as("Player", "p2")
300            .on("p2.id = Item.player_id")
301            .left_join_as("Player", "p3")
302            .on("p3.id = Item.player_id")
303            .left_join_as("Player", "p4")
304            .on("p4.id = Item.player_id")
305            .left_join_as("Player", "p5")
306            .on("p5.id = Item.player_id")
307            .left_join_as("Player", "p6")
308            .on("p6.id = Item.player_id")
309            .left_join_as("Player", "p7")
310            .on("p7.id = Item.player_id")
311            .left_join_as("Player", "p8")
312            .on("p8.id = Item.player_id")
313            .left_join_as("Player", "p9")
314            .on("p9.id = Item.player_id")
315            .filter("Player.id = 1")
316            .build();
317        let expected = "
318            SELECT * FROM Item
319            LEFT JOIN Player ON Player.id = Item.player_id
320            LEFT JOIN Player p1 ON p1.id = Item.player_id
321            LEFT JOIN Player p2 ON p2.id = Item.player_id
322            LEFT JOIN Player p3 ON p3.id = Item.player_id
323            LEFT JOIN Player p4 ON p4.id = Item.player_id
324            LEFT JOIN Player p5 ON p5.id = Item.player_id
325            LEFT JOIN Player p6 ON p6.id = Item.player_id
326            LEFT JOIN Player p7 ON p7.id = Item.player_id
327            LEFT JOIN Player p8 ON p8.id = Item.player_id
328            LEFT JOIN Player p9 ON p9.id = Item.player_id
329            WHERE Player.id = 1;
330        ";
331        test(&actual, expected);
332
333        // select node -> left join node -> join constraint node -> left join node
334        let actual = table("Item")
335            .select()
336            .left_join("Player")
337            .on("Player.id = Item.player_id")
338            .left_join("Player")
339            .on("p1.id = Item.player_id")
340            .build();
341        let expected = "
342            SELECT * FROM Item
343            LEFT JOIN Player ON Player.id = Item.player_id
344            LEFT JOIN Player ON p1.id = Item.player_id";
345        test(&actual, expected);
346
347        let actual = table("Item")
348            .select()
349            .left_join("Player")
350            .on("Player.id = Item.player_id")
351            .left_join_as("Player", "p1")
352            .on("p1.id = Item.player_id")
353            .left_join_as("Player", "p2")
354            .on("p2.id = Item.player_id")
355            .left_join_as("Player", "p3")
356            .on("p3.id = Item.player_id")
357            .join_as("Player", "p4")
358            .on("p4.id = Item.player_id AND Item.id > 101")
359            .filter("Player.id = 1")
360            .build();
361        let expected = "
362            SELECT * FROM Item
363            LEFT JOIN Player ON Player.id = Item.player_id
364            LEFT JOIN Player p1 ON p1.id = Item.player_id
365            LEFT JOIN Player p2 ON p2.id = Item.player_id
366            LEFT JOIN Player p3 ON p3.id = Item.player_id
367            INNER JOIN Player p4 ON p4.id = Item.player_id AND Item.id > 101
368            WHERE Player.id = 1;
369        ";
370        test(&actual, expected);
371    }
372
373    #[test]
374    fn join_join() {
375        // join - join
376        let actual = table("Foo").select().join("Bar").join("Baz").build();
377        let expected = "
378            SELECT * FROM Foo
379            INNER JOIN Bar
380            INNER JOIN Baz
381            ";
382        test(&actual, expected);
383
384        // join - join as
385        let actual = table("Foo")
386            .select()
387            .join("Bar")
388            .join_as("Baz", "B")
389            .build();
390        let expected = "
391            SELECT * FROM Foo
392            INNER JOIN Bar
393            INNER JOIN Baz B
394            ";
395        test(&actual, expected);
396
397        // join - left join
398        let actual = table("Foo").select().join("Bar").left_join("Baz").build();
399        let expected = "
400            SELECT * FROM Foo
401            INNER JOIN Bar
402            LEFT JOIN Baz
403            ";
404        test(&actual, expected);
405
406        // join - left join as
407        let actual = table("Foo")
408            .select()
409            .join("Bar")
410            .left_join_as("Baz", "B")
411            .build();
412        let expected = "
413            SELECT * FROM Foo
414            INNER JOIN Bar
415            LEFT JOIN Baz B
416            ";
417        test(&actual, expected);
418
419        // join as - join
420        let actual = table("Foo")
421            .select()
422            .join_as("Bar", "B")
423            .join("Baz")
424            .build();
425        let expected = "
426            SELECT * FROM Foo
427            INNER JOIN Bar B
428            INNER JOIN Baz
429            ";
430        test(&actual, expected);
431
432        // join as - join as
433        let actual = table("Foo")
434            .select()
435            .join_as("Bar", "B")
436            .join_as("Baz", "C")
437            .build();
438        let expected = "
439            SELECT * FROM Foo
440            INNER JOIN Bar B
441            INNER JOIN Baz C
442            ";
443        test(&actual, expected);
444
445        // join as - left join
446        let actual = table("Foo")
447            .select()
448            .join_as("Bar", "B")
449            .left_join("Baz")
450            .build();
451        let expected = "
452            SELECT * FROM Foo
453            INNER JOIN Bar B
454            LEFT JOIN Baz
455            ";
456        test(&actual, expected);
457
458        // join as - left join as
459        let actual = table("Foo")
460            .select()
461            .join_as("Bar", "B")
462            .left_join_as("Baz", "C")
463            .build();
464        let expected = "
465            SELECT * FROM Foo
466            INNER JOIN Bar B
467            LEFT JOIN Baz C
468            ";
469        test(&actual, expected);
470
471        // left join - join
472        let actual = table("Foo").select().left_join("Bar").join("Baz").build();
473        let expected = "
474            SELECT * FROM Foo
475            LEFT JOIN Bar
476            INNER JOIN Baz
477            ";
478        test(&actual, expected);
479
480        // left join - join as
481        let actual = table("Foo")
482            .select()
483            .left_join("Bar")
484            .join_as("Baz", "B")
485            .build();
486        let expected = "
487            SELECT * FROM Foo
488            LEFT JOIN Bar
489            INNER JOIN Baz B
490            ";
491        test(&actual, expected);
492
493        // left join - left join
494        let actual = table("Foo")
495            .select()
496            .left_join("Bar")
497            .left_join("Baz")
498            .build();
499        let expected = "
500            SELECT * FROM Foo
501            LEFT JOIN Bar
502            LEFT JOIN Baz
503            ";
504        test(&actual, expected);
505
506        // left join - left join as
507        let actual = table("Foo")
508            .select()
509            .left_join("Bar")
510            .left_join_as("Baz", "B")
511            .build();
512        let expected = "
513            SELECT * FROM Foo
514            LEFT JOIN Bar
515            LEFT JOIN Baz B
516            ";
517        test(&actual, expected);
518
519        // left join as - join
520        let actual = table("Foo")
521            .select()
522            .left_join_as("Bar", "B")
523            .join("Baz")
524            .build();
525        let expected = "
526            SELECT * FROM Foo
527            LEFT JOIN Bar B
528            INNER JOIN Baz
529            ";
530        test(&actual, expected);
531
532        // left join as - join as
533        let actual = table("Foo")
534            .select()
535            .left_join_as("Bar", "B")
536            .join_as("Baz", "C")
537            .build();
538        let expected = "
539            SELECT * FROM Foo
540            LEFT JOIN Bar B
541            INNER JOIN Baz C
542            ";
543        test(&actual, expected);
544
545        // left join as - left join
546        let actual = table("Foo")
547            .select()
548            .left_join_as("Bar", "B")
549            .left_join("Baz")
550            .build();
551        let expected = "
552            SELECT * FROM Foo
553            LEFT JOIN Bar B
554            LEFT JOIN Baz
555            ";
556        test(&actual, expected);
557
558        // left join as - left join as
559        let actual = table("Foo")
560            .select()
561            .left_join_as("Bar", "B")
562            .left_join_as("Baz", "C")
563            .build();
564        let expected = "
565            SELECT * FROM Foo
566            LEFT JOIN Bar B
567            LEFT JOIN Baz C
568            ";
569        test(&actual, expected);
570    }
571
572    #[test]
573    fn hash_join() {
574        use crate::{
575            ast::{
576                Join, JoinConstraint, JoinExecutor, JoinOperator, Query, Select, SetExpr,
577                Statement, TableAlias, TableFactor, TableWithJoins,
578            },
579            ast_builder::{SelectItemList, col},
580        };
581
582        let gen_expected = |other_join| {
583            let join = Join {
584                relation: TableFactor::Table {
585                    name: "PlayerItem".to_owned(),
586                    alias: None,
587                    index: None,
588                },
589                join_operator: JoinOperator::Inner(JoinConstraint::None),
590                join_executor: JoinExecutor::Hash {
591                    key_expr: col("PlayerItem.user_id").try_into().unwrap(),
592                    value_expr: col("Player.id").try_into().unwrap(),
593                    where_clause: None,
594                },
595            };
596            let select = Select {
597                distinct: false,
598                projection: SelectItemList::from("*").try_into().unwrap(),
599                from: TableWithJoins {
600                    relation: TableFactor::Table {
601                        name: "Player".to_owned(),
602                        alias: None,
603                        index: None,
604                    },
605                    joins: vec![join, other_join],
606                },
607                selection: None,
608                group_by: Vec::new(),
609                having: None,
610            };
611
612            Ok(Statement::Query(Query {
613                body: SetExpr::Select(Box::new(select)),
614                order_by: Vec::new(),
615                limit: None,
616                offset: None,
617            }))
618        };
619
620        let actual = table("Player")
621            .select()
622            .join("PlayerItem")
623            .hash_executor("PlayerItem.user_id", "Player.id")
624            .join("OtherItem")
625            .build();
626        let expected = {
627            let other_join = Join {
628                relation: TableFactor::Table {
629                    name: "OtherItem".to_owned(),
630                    alias: None,
631                    index: None,
632                },
633                join_operator: JoinOperator::Inner(JoinConstraint::None),
634                join_executor: JoinExecutor::NestedLoop,
635            };
636
637            gen_expected(other_join)
638        };
639        assert_eq!(actual, expected, "inner join");
640
641        let actual = table("Player")
642            .select()
643            .join("PlayerItem")
644            .hash_executor("PlayerItem.user_id", "Player.id")
645            .join_as("OtherItem", "Ot")
646            .build();
647        let expected = {
648            let other_join = Join {
649                relation: TableFactor::Table {
650                    name: "OtherItem".to_owned(),
651                    alias: Some(TableAlias {
652                        name: "Ot".to_owned(),
653                        columns: Vec::new(),
654                    }),
655                    index: None,
656                },
657                join_operator: JoinOperator::Inner(JoinConstraint::None),
658                join_executor: JoinExecutor::NestedLoop,
659            };
660
661            gen_expected(other_join)
662        };
663        assert_eq!(actual, expected, "inner join with alias");
664
665        let actual = table("Player")
666            .select()
667            .join("PlayerItem")
668            .hash_executor("PlayerItem.user_id", "Player.id")
669            .left_join("OtherItem")
670            .build();
671        let expected = {
672            let other_join = Join {
673                relation: TableFactor::Table {
674                    name: "OtherItem".to_owned(),
675                    alias: None,
676                    index: None,
677                },
678                join_operator: JoinOperator::LeftOuter(JoinConstraint::None),
679                join_executor: JoinExecutor::NestedLoop,
680            };
681
682            gen_expected(other_join)
683        };
684        assert_eq!(actual, expected, "left join");
685
686        let actual = table("Player")
687            .select()
688            .join("PlayerItem")
689            .hash_executor("PlayerItem.user_id", "Player.id")
690            .left_join_as("OtherItem", "Ot")
691            .build();
692        let expected = {
693            let other_join = Join {
694                relation: TableFactor::Table {
695                    name: "OtherItem".to_owned(),
696                    alias: Some(TableAlias {
697                        name: "Ot".to_owned(),
698                        columns: Vec::new(),
699                    }),
700                    index: None,
701                },
702                join_operator: JoinOperator::LeftOuter(JoinConstraint::None),
703                join_executor: JoinExecutor::NestedLoop,
704            };
705
706            gen_expected(other_join)
707        };
708        assert_eq!(actual, expected, "left join with alias");
709
710        let actual = table("App").select().alias_as("Sub").select().build();
711        let expected = "SELECT * FROM (SELECT * FROM App) Sub";
712        test(&actual, expected);
713
714        // join -> derived subquery
715        let actual = table("Foo")
716            .select()
717            .join("Bar")
718            .alias_as("Sub")
719            .select()
720            .build();
721        let expected = "
722            SELECT * FROM (
723                SELECT * FROM Foo
724                INNER JOIN Bar
725            ) Sub
726            ";
727        test(&actual, expected);
728    }
729}
gluesql_core/ast_builder/select/join/root.rs

gluesql_core/ast_builder/select/join/
root.rs
