sea_orm/query/
join.rs

1use crate::{
2    ColumnTrait, EntityTrait, IdenStatic, Iterable, Linked, QueryFilter, QuerySelect, QueryTrait,
3    Related, Select, SelectA, SelectB, SelectThree, SelectTwo, SelectTwoMany, SelectTwoRequired,
4    TopologyChain, TopologyStar, find_linked_recursive, join_tbl_on_condition,
5};
6pub use sea_query::JoinType;
7use sea_query::{Condition, ConditionType, Expr, IntoCondition, IntoIden, SelectExpr};
8
9impl<E> Select<E>
10where
11    E: EntityTrait,
12{
13    /// Left Join with a Related Entity.
14    pub fn left_join<R>(self, _: R) -> Self
15    where
16        R: EntityTrait,
17        E: Related<R>,
18    {
19        self.join_join(JoinType::LeftJoin, E::to(), E::via())
20    }
21
22    /// Right Join with a Related Entity.
23    pub fn right_join<R>(self, _: R) -> Self
24    where
25        R: EntityTrait,
26        E: Related<R>,
27    {
28        self.join_join(JoinType::RightJoin, E::to(), E::via())
29    }
30
31    /// Inner Join with a Related Entity.
32    pub fn inner_join<R>(self, _: R) -> Self
33    where
34        R: EntityTrait,
35        E: Related<R>,
36    {
37        self.join_join(JoinType::InnerJoin, E::to(), E::via())
38    }
39
40    /// Join with an Entity Related to me.
41    pub fn reverse_join<R>(self, _: R) -> Self
42    where
43        R: EntityTrait + Related<E>,
44    {
45        self.join_rev(JoinType::InnerJoin, R::to())
46    }
47
48    /// Left Join with a Related Entity and select both Entity.
49    pub fn find_also<R>(self, _: E, r: R) -> SelectTwo<E, R>
50    where
51        R: EntityTrait,
52        E: Related<R>,
53    {
54        self.left_join(r).select_also(r)
55    }
56
57    /// Left Join with a Related Entity and select both Entity.
58    pub fn find_also_related<R>(self, r: R) -> SelectTwo<E, R>
59    where
60        R: EntityTrait,
61        E: Related<R>,
62    {
63        self.left_join(r).select_also(r)
64    }
65
66    /// Inner Join with a Related Entity and select both Entity.
67    pub fn find_both_related<R>(self, r: R) -> SelectTwoRequired<E, R>
68    where
69        R: EntityTrait,
70        E: Related<R>,
71    {
72        self.inner_join(r).select_two_required(r)
73    }
74
75    /// Left Join with a Related Entity and select the related Entity as a `Vec`
76    pub fn find_with_related<R>(self, r: R) -> SelectTwoMany<E, R>
77    where
78        R: EntityTrait,
79        E: Related<R>,
80    {
81        self.left_join(r).select_with(r)
82    }
83
84    /// Left Join with a Linked Entity and select both Entity.
85    pub fn find_also_linked<L, T>(self, l: L) -> SelectTwo<E, T>
86    where
87        L: Linked<FromEntity = E, ToEntity = T>,
88        T: EntityTrait,
89    {
90        SelectTwo::new_without_prepare(self.left_join_linked(l).into_query())
91    }
92
93    /// Left Join with a Linked Entity and select Entity as a `Vec`.
94    pub fn find_with_linked<L, T>(self, l: L) -> SelectTwoMany<E, T>
95    where
96        L: Linked<FromEntity = E, ToEntity = T>,
97        T: EntityTrait,
98    {
99        SelectTwoMany::new_without_prepare(self.left_join_linked(l).into_query())
100    }
101
102    /// Left Join with a Linked Entity.
103    pub fn left_join_linked<L, T>(mut self, l: L) -> Self
104    where
105        L: Linked<FromEntity = E, ToEntity = T>,
106        T: EntityTrait,
107    {
108        for (i, mut rel) in l.link().into_iter().enumerate() {
109            let r = self.linked_index;
110            self.linked_index += 1;
111            let to_tbl = format!("r{r}").into_iden();
112            let from_tbl = if i > 0 {
113                format!("r{}", r - 1).into_iden()
114            } else {
115                rel.from_tbl.sea_orm_table().clone()
116            };
117            let table_ref = rel.to_tbl;
118
119            let mut condition = match rel.condition_type {
120                ConditionType::All => Condition::all(),
121                ConditionType::Any => Condition::any(),
122            };
123            condition = condition.add(join_tbl_on_condition(
124                from_tbl.clone(),
125                to_tbl.clone(),
126                rel.from_col,
127                rel.to_col,
128            ));
129            if let Some(f) = rel.on_condition.take() {
130                condition = condition.add(f(from_tbl.clone(), to_tbl.clone()));
131            }
132
133            self.query
134                .join_as(JoinType::LeftJoin, table_ref, to_tbl, condition);
135        }
136        self = self.apply_alias(SelectA.as_str());
137        for col in <T::Column as Iterable>::iter() {
138            let alias = format!("{}{}", SelectB.as_str(), col.as_str());
139            let expr = Expr::col((
140                format!("r{}", self.linked_index - 1).into_iden(),
141                col.into_iden(),
142            ));
143            self.query.expr(SelectExpr {
144                expr: col.select_as(expr),
145                alias: Some(alias.into_iden()),
146                window: None,
147            });
148        }
149        self
150    }
151
152    /// Filter by condition on the related Entity. Uses `EXISTS` SQL statement under the hood.
153    /// ```
154    /// # use sea_orm::{DbBackend, entity::*, query::*, tests_cfg::{cake, fruit, filling}};
155    /// assert_eq!(
156    ///     cake::Entity::find()
157    ///         .has_related(fruit::Entity, fruit::Column::Name.eq("Mango"))
158    ///         .build(DbBackend::Sqlite)
159    ///         .to_string(),
160    ///     [
161    ///         r#"SELECT "cake"."id", "cake"."name" FROM "cake""#,
162    ///         r#"WHERE EXISTS(SELECT 1 FROM "fruit""#,
163    ///         r#"WHERE "fruit"."name" = 'Mango'"#,
164    ///         r#"AND "cake"."id" = "fruit"."cake_id")"#,
165    ///     ]
166    ///     .join(" ")
167    /// );
168    ///
169    /// assert_eq!(
170    ///     cake::Entity::find()
171    ///         .has_related(filling::Entity, filling::Column::Name.eq("Marmalade"))
172    ///         .build(DbBackend::Sqlite)
173    ///         .to_string(),
174    ///     [
175    ///         r#"SELECT "cake"."id", "cake"."name" FROM "cake""#,
176    ///         r#"WHERE EXISTS(SELECT 1 FROM "filling""#,
177    ///         r#"INNER JOIN "cake_filling" ON "cake_filling"."filling_id" = "filling"."id""#,
178    ///         r#"WHERE "filling"."name" = 'Marmalade'"#,
179    ///         r#"AND "cake"."id" = "cake_filling"."cake_id")"#,
180    ///     ]
181    ///     .join(" ")
182    /// );
183    /// ```
184    pub fn has_related<R, C>(mut self, _: R, condition: C) -> Self
185    where
186        R: EntityTrait,
187        E: Related<R>,
188        C: IntoCondition,
189    {
190        let mut to = None;
191        let mut condition = Condition::all().add(condition.into_condition());
192        condition = condition.add(if let Some(via) = E::via() {
193            to = Some(E::to());
194            via
195        } else {
196            E::to()
197        });
198        let mut subquery = R::find()
199            .select_only()
200            .expr(Expr::cust("1"))
201            .filter(condition)
202            .into_query();
203        if let Some(to) = to {
204            // join the junction table
205            subquery.inner_join(to.from_tbl.clone(), to);
206        }
207        self.query.cond_where(Expr::exists(subquery));
208        self
209    }
210
211    #[doc(hidden)]
212    /// Recursive self-join with CTE
213    pub fn find_with_linked_recursive<L>(self, l: L) -> Select<E>
214    where
215        L: Linked<FromEntity = E, ToEntity = E>,
216    {
217        find_linked_recursive(self, l.link())
218    }
219}
220
221impl<E, F> SelectTwo<E, F>
222where
223    E: EntityTrait,
224    F: EntityTrait,
225{
226    /// Only used by Entity loader
227    #[doc(hidden)]
228    pub fn select_also_fake<R>(self, _: R) -> SelectThree<E, F, R, TopologyStar>
229    where
230        R: EntityTrait,
231    {
232        // select also but without join
233        SelectThree::new_without_prepare(self.into_query())
234    }
235
236    /// Left Join with a Related Entity and select both Entity.
237    pub fn find_also<G, R>(self, _: G, _: R) -> SelectThree<E, F, R, TopologyStar>
238    where
239        R: EntityTrait,
240        G: EntityTrait + Related<R>,
241    {
242        SelectThree::new(
243            self.join_join(JoinType::LeftJoin, G::to(), G::via())
244                .into_query(),
245        )
246    }
247
248    /// Left Join with an Entity Related to the first Entity
249    pub fn find_also_related<R>(self, _: R) -> SelectThree<E, F, R, TopologyStar>
250    where
251        R: EntityTrait,
252        E: Related<R>,
253    {
254        SelectThree::new(
255            self.join_join(JoinType::LeftJoin, E::to(), E::via())
256                .into_query(),
257        )
258    }
259
260    /// Left Join with an Entity Related to the second Entity
261    pub fn and_also_related<R>(self, _: R) -> SelectThree<E, F, R, TopologyChain>
262    where
263        R: EntityTrait,
264        F: Related<R>,
265    {
266        SelectThree::new(
267            self.join_join(JoinType::LeftJoin, F::to(), F::via())
268                .into_query(),
269        )
270    }
271}
272
273#[cfg(test)]
274mod tests {
275    use crate::tests_cfg::{
276        cake, cake_compact, cake_filling, cake_filling_price, entity_linked, filling, fruit,
277    };
278    use crate::{
279        ColumnTrait, DbBackend, EntityTrait, ModelTrait, QueryFilter, QuerySelect, QueryTrait,
280        RelationTrait,
281    };
282    use pretty_assertions::assert_eq;
283    use sea_query::{ConditionType, Expr, ExprTrait, IntoCondition, JoinType};
284
285    #[test]
286    fn join_1() {
287        assert_eq!(
288            cake::Entity::find()
289                .left_join(fruit::Entity)
290                .build(DbBackend::MySql)
291                .to_string(),
292            [
293                "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
294                "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id`",
295            ]
296            .join(" ")
297        );
298    }
299
300    #[test]
301    fn join_2() {
302        assert_eq!(
303            cake::Entity::find()
304                .inner_join(fruit::Entity)
305                .filter(fruit::Column::Name.contains("cherry"))
306                .build(DbBackend::MySql)
307                .to_string(),
308            [
309                "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
310                "INNER JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id`",
311                "WHERE `fruit`.`name` LIKE \'%cherry%\'"
312            ]
313            .join(" ")
314        );
315    }
316
317    #[test]
318    fn join_3() {
319        assert_eq!(
320            fruit::Entity::find()
321                .reverse_join(cake::Entity)
322                .build(DbBackend::MySql)
323                .to_string(),
324            [
325                "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
326                "INNER JOIN `cake` ON `cake`.`id` = `fruit`.`cake_id`",
327            ]
328            .join(" ")
329        );
330    }
331
332    #[test]
333    fn join_4() {
334        use crate::{Related, Select};
335
336        let find_fruit: Select<fruit::Entity> = cake::Entity::find_related();
337        assert_eq!(
338            find_fruit
339                .filter(cake::Column::Id.eq(11))
340                .build(DbBackend::MySql)
341                .to_string(),
342            [
343                "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
344                "INNER JOIN `cake` ON `cake`.`id` = `fruit`.`cake_id`",
345                "WHERE `cake`.`id` = 11",
346            ]
347            .join(" ")
348        );
349
350        let find_fruit: Select<fruit::Entity> = cake::Entity::find_related_rev();
351        assert_eq!(
352            find_fruit
353                .filter(cake::Column::Id.eq(11))
354                .build(DbBackend::MySql)
355                .to_string(),
356            [
357                "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
358                "INNER JOIN `cake` ON `fruit`.`cake_id` = `cake`.`id`",
359                "WHERE `cake`.`id` = 11",
360            ]
361            .join(" ")
362        );
363    }
364
365    #[test]
366    fn join_5() {
367        let cake_model = cake::Model {
368            id: 12,
369            name: "".to_owned(),
370        };
371
372        assert_eq!(
373            cake_model
374                .find_related(fruit::Entity)
375                .build(DbBackend::MySql)
376                .to_string(),
377            [
378                "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
379                "INNER JOIN `cake` ON `cake`.`id` = `fruit`.`cake_id`",
380                "WHERE `cake`.`id` = 12",
381            ]
382            .join(" ")
383        );
384    }
385
386    #[test]
387    fn join_6() {
388        assert_eq!(
389            cake::Entity::find()
390                .left_join(filling::Entity)
391                .build(DbBackend::MySql)
392                .to_string(),
393            [
394                "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
395                "LEFT JOIN `cake_filling` ON `cake`.`id` = `cake_filling`.`cake_id`",
396                "LEFT JOIN `filling` ON `cake_filling`.`filling_id` = `filling`.`id`",
397            ]
398            .join(" ")
399        );
400    }
401
402    #[test]
403    fn join_7() {
404        use crate::{Related, Select};
405
406        let find_filling: Select<filling::Entity> = cake::Entity::find_related();
407        assert_eq!(
408            find_filling.build(DbBackend::MySql).to_string(),
409            [
410                "SELECT `filling`.`id`, `filling`.`name`, `filling`.`vendor_id` FROM `filling`",
411                "INNER JOIN `cake_filling` ON `cake_filling`.`filling_id` = `filling`.`id`",
412                "INNER JOIN `cake` ON `cake`.`id` = `cake_filling`.`cake_id`",
413            ]
414            .join(" ")
415        );
416
417        let find_filling: Select<filling::Entity> = cake::Entity::find_related_rev();
418        assert_eq!(
419            find_filling.build(DbBackend::MySql).to_string(),
420            [
421                "SELECT `filling`.`id`, `filling`.`name`, `filling`.`vendor_id` FROM `filling`",
422                "INNER JOIN `cake_filling` ON `filling`.`id` = `cake_filling`.`filling_id`",
423                "INNER JOIN `cake` ON `cake_filling`.`cake_id` = `cake`.`id`",
424            ]
425            .join(" ")
426        );
427    }
428
429    #[test]
430    fn join_8() {
431        use crate::{Related, Select};
432
433        let find_cake_filling_price: Select<cake_filling_price::Entity> =
434            cake_filling::Entity::find_related();
435        assert_eq!(
436            find_cake_filling_price.build(DbBackend::Postgres).to_string(),
437            [
438                r#"SELECT "cake_filling_price"."cake_id", "cake_filling_price"."filling_id", "cake_filling_price"."price""#,
439                r#"FROM "public"."cake_filling_price""#,
440                r#"INNER JOIN "cake_filling" ON"#,
441                r#""cake_filling"."cake_id" = "cake_filling_price"."cake_id" AND"#,
442                r#""cake_filling"."filling_id" = "cake_filling_price"."filling_id""#,
443            ]
444            .join(" ")
445        );
446    }
447
448    #[test]
449    fn join_9() {
450        use crate::{Related, Select};
451
452        let find_cake_filling: Select<cake_filling::Entity> =
453            cake_filling_price::Entity::find_related();
454        assert_eq!(
455            find_cake_filling.build(DbBackend::Postgres).to_string(),
456            [
457                r#"SELECT "cake_filling"."cake_id", "cake_filling"."filling_id""#,
458                r#"FROM "cake_filling""#,
459                r#"INNER JOIN "public"."cake_filling_price" ON"#,
460                r#""cake_filling_price"."cake_id" = "cake_filling"."cake_id" AND"#,
461                r#""cake_filling_price"."filling_id" = "cake_filling"."filling_id""#,
462            ]
463            .join(" ")
464        );
465    }
466
467    #[test]
468    fn join_10() {
469        let cake_model = cake::Model {
470            id: 12,
471            name: "".to_owned(),
472        };
473
474        assert_eq!(
475            cake_model
476                .find_linked(entity_linked::CakeToFilling)
477                .build(DbBackend::MySql)
478                .to_string(),
479            [
480                r"SELECT `filling`.`id`, `filling`.`name`, `filling`.`vendor_id`",
481                r"FROM `filling`",
482                r"INNER JOIN `cake_filling` AS `r0` ON `r0`.`filling_id` = `filling`.`id`",
483                r"INNER JOIN `cake` AS `r1` ON `r1`.`id` = `r0`.`cake_id`",
484                r"WHERE `r1`.`id` = 12",
485            ]
486            .join(" ")
487        );
488    }
489
490    #[test]
491    fn join_11() {
492        let cake_model = cake::Model {
493            id: 18,
494            name: "".to_owned(),
495        };
496
497        assert_eq!(
498            cake_model
499                .find_linked(entity_linked::CakeToFillingVendor)
500                .build(DbBackend::MySql)
501                .to_string(),
502            [
503                r"SELECT `vendor`.`id`, `vendor`.`name`",
504                r"FROM `vendor`",
505                r"INNER JOIN `filling` AS `r0` ON `r0`.`vendor_id` = `vendor`.`id`",
506                r"INNER JOIN `cake_filling` AS `r1` ON `r1`.`filling_id` = `r0`.`id`",
507                r"INNER JOIN `cake` AS `r2` ON `r2`.`id` = `r1`.`cake_id`",
508                r"WHERE `r2`.`id` = 18",
509            ]
510            .join(" ")
511        );
512    }
513
514    #[test]
515    fn join_12() {
516        assert_eq!(
517            cake::Entity::find()
518                .find_also_linked(entity_linked::CakeToFilling)
519                .build(DbBackend::MySql)
520                .to_string(),
521            [
522                r"SELECT `cake`.`id` AS `A_id`, `cake`.`name` AS `A_name`,",
523                r"`r1`.`id` AS `B_id`, `r1`.`name` AS `B_name`, `r1`.`vendor_id` AS `B_vendor_id`",
524                r"FROM `cake`",
525                r"LEFT JOIN `cake_filling` AS `r0` ON `cake`.`id` = `r0`.`cake_id`",
526                r"LEFT JOIN `filling` AS `r1` ON `r0`.`filling_id` = `r1`.`id`",
527            ]
528            .join(" ")
529        );
530    }
531
532    #[test]
533    fn join_13() {
534        assert_eq!(
535            cake::Entity::find()
536                .find_also_linked(entity_linked::CakeToFillingVendor)
537                .build(DbBackend::MySql)
538                .to_string(),
539            [
540                r"SELECT `cake`.`id` AS `A_id`, `cake`.`name` AS `A_name`,",
541                r"`r2`.`id` AS `B_id`, `r2`.`name` AS `B_name`",
542                r"FROM `cake`",
543                r"LEFT JOIN `cake_filling` AS `r0` ON `cake`.`id` = `r0`.`cake_id`",
544                r"LEFT JOIN `filling` AS `r1` ON `r0`.`filling_id` = `r1`.`id`",
545                r"LEFT JOIN `vendor` AS `r2` ON `r1`.`vendor_id` = `r2`.`id`",
546            ]
547            .join(" ")
548        );
549    }
550
551    #[test]
552    fn join_14() {
553        assert_eq!(
554            cake_compact::Entity::find()
555                .join(JoinType::LeftJoin, cake_compact::Relation::TropicalFruit.def())
556                .build(DbBackend::MySql)
557                .to_string(),
558            [
559                "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
560                "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id` AND `fruit`.`name` LIKE '%tropical%'",
561            ]
562            .join(" ")
563        );
564    }
565
566    #[test]
567    fn join_15() {
568        let cake_model = cake::Model {
569            id: 18,
570            name: "".to_owned(),
571        };
572
573        assert_eq!(
574            cake_model
575                .find_linked(entity_linked::CheeseCakeToFillingVendor)
576                .build(DbBackend::MySql)
577                .to_string(),
578            [
579                r"SELECT `vendor`.`id`, `vendor`.`name`",
580                r"FROM `vendor`",
581                r"INNER JOIN `filling` AS `r0` ON `r0`.`vendor_id` = `vendor`.`id`",
582                r"INNER JOIN `cake_filling` AS `r1` ON `r1`.`filling_id` = `r0`.`id`",
583                r"INNER JOIN `cake` AS `r2` ON `r2`.`id` = `r1`.`cake_id` AND `r2`.`name` LIKE '%cheese%'",
584                r"WHERE `r2`.`id` = 18",
585            ]
586            .join(" ")
587        );
588    }
589
590    #[test]
591    fn join_16() {
592        // removed wrong test case
593    }
594
595    #[test]
596    fn join_17() {
597        assert_eq!(
598            cake::Entity::find()
599                .find_also_linked(entity_linked::CheeseCakeToFillingVendor)
600                .build(DbBackend::MySql)
601                .to_string(),
602            [
603                r"SELECT `cake`.`id` AS `A_id`, `cake`.`name` AS `A_name`,",
604                r"`r2`.`id` AS `B_id`, `r2`.`name` AS `B_name`",
605                r"FROM `cake`",
606                r"LEFT JOIN `cake_filling` AS `r0` ON `cake`.`id` = `r0`.`cake_id` AND `cake`.`name` LIKE '%cheese%'",
607                r"LEFT JOIN `filling` AS `r1` ON `r0`.`filling_id` = `r1`.`id`",
608                r"LEFT JOIN `vendor` AS `r2` ON `r1`.`vendor_id` = `r2`.`id`",
609            ]
610            .join(" ")
611        );
612    }
613
614    #[test]
615    fn join_18() {
616        // removed wrong test case
617    }
618
619    #[test]
620    fn join_19() {
621        assert_eq!(
622            cake_compact::Entity::find()
623                .join(JoinType::LeftJoin, cake_compact::Relation::TropicalFruit.def())
624                .join(
625                    JoinType::LeftJoin,
626                    cake_filling::Relation::Cake
627                        .def()
628                        .rev()
629                        .on_condition(|_left, right| {
630                            Expr::col((right, cake_filling::Column::CakeId))
631                                .gt(10)
632                                .into_condition()
633                        })
634                )
635                .join(
636                    JoinType::LeftJoin,
637                    cake_filling::Relation::Filling
638                        .def()
639                        .on_condition(|_left, right| {
640                            Expr::col((right, filling::Column::Name))
641                                .like("%lemon%")
642                                .into_condition()
643                        })
644                )
645                .join(JoinType::LeftJoin, filling::Relation::Vendor.def())
646                .build(DbBackend::MySql)
647                .to_string(),
648            [
649                "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
650                "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id` AND `fruit`.`name` LIKE '%tropical%'",
651                "LEFT JOIN `cake_filling` ON `cake`.`id` = `cake_filling`.`cake_id` AND `cake_filling`.`cake_id` > 10",
652                "LEFT JOIN `filling` ON `cake_filling`.`filling_id` = `filling`.`id` AND `filling`.`name` LIKE '%lemon%'",
653                "LEFT JOIN `vendor` ON `filling`.`vendor_id` = `vendor`.`id`",
654            ]
655            .join(" ")
656        );
657    }
658
659    #[test]
660    fn join_20() {
661        assert_eq!(
662            cake::Entity::find()
663                .column_as(
664                    Expr::col(("fruit_alias", fruit::Column::Name)),
665                    "fruit_name"
666                )
667                .join_as(
668                    JoinType::LeftJoin,
669                    cake::Relation::Fruit
670                        .def()
671                        .on_condition(|_left, right| {
672                            Expr::col((right, fruit::Column::Name))
673                                .like("%tropical%")
674                                .into_condition()
675                        }),
676                    "fruit_alias"
677                )
678                .build(DbBackend::MySql)
679                .to_string(),
680            [
681                "SELECT `cake`.`id`, `cake`.`name`, `fruit_alias`.`name` AS `fruit_name` FROM `cake`",
682                "LEFT JOIN `fruit` AS `fruit_alias` ON `cake`.`id` = `fruit_alias`.`cake_id` AND `fruit_alias`.`name` LIKE '%tropical%'",
683            ]
684            .join(" ")
685        );
686    }
687
688    #[test]
689    fn join_21() {
690        assert_eq!(
691            cake_compact::Entity::find()
692                .column_as(
693                    Expr::col(("cake_filling_alias", cake_filling::Column::CakeId)),
694                    "cake_filling_cake_id"
695                )
696                .join(JoinType::LeftJoin, cake_compact::Relation::TropicalFruit.def())
697                .join_as_rev(
698                    JoinType::LeftJoin,
699                    cake_filling::Relation::Cake
700                        .def()
701                        .on_condition(|left, _right| {
702                            Expr::col((left, cake_filling::Column::CakeId))
703                                .gt(10)
704                                .into_condition()
705                        }),
706                    "cake_filling_alias"
707                )
708                .build(DbBackend::MySql)
709                .to_string(),
710            [
711                "SELECT `cake`.`id`, `cake`.`name`, `cake_filling_alias`.`cake_id` AS `cake_filling_cake_id` FROM `cake`",
712                "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id` AND `fruit`.`name` LIKE '%tropical%'",
713                "LEFT JOIN `cake_filling` AS `cake_filling_alias` ON `cake_filling_alias`.`cake_id` = `cake`.`id` AND `cake_filling_alias`.`cake_id` > 10",
714            ]
715            .join(" ")
716        );
717    }
718
719    #[test]
720    fn join_22() {
721        assert_eq!(
722            cake_compact::Entity::find()
723                .column_as(
724                    Expr::col(("cake_filling_alias", cake_filling::Column::CakeId)),
725                    "cake_filling_cake_id"
726                )
727                .join(JoinType::LeftJoin, cake_compact::Relation::OrTropicalFruit.def())
728                .join_as_rev(
729                    JoinType::LeftJoin,
730                    cake_filling::Relation::Cake
731                        .def()
732                        .condition_type(ConditionType::Any)
733                        .on_condition(|left, _right| {
734                            Expr::col((left, cake_filling::Column::CakeId))
735                                .gt(10)
736                                .into_condition()
737                        }),
738                    "cake_filling_alias"
739                )
740                .build(DbBackend::MySql)
741                .to_string(),
742            [
743                "SELECT `cake`.`id`, `cake`.`name`, `cake_filling_alias`.`cake_id` AS `cake_filling_cake_id` FROM `cake`",
744                "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id` OR `fruit`.`name` LIKE '%tropical%'",
745                "LEFT JOIN `cake_filling` AS `cake_filling_alias` ON `cake_filling_alias`.`cake_id` = `cake`.`id` OR `cake_filling_alias`.`cake_id` > 10",
746            ]
747            .join(" ")
748        );
749    }
750
751    #[test]
752    fn join_23() {
753        let cake_model = cake::Model {
754            id: 18,
755            name: "".to_owned(),
756        };
757
758        assert_eq!(
759            cake_model
760                .find_linked(entity_linked::CakeToCakeViaFilling)
761                .build(DbBackend::MySql)
762                .to_string(),
763            [
764                "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
765                "INNER JOIN `cake_filling` AS `r0` ON `r0`.`cake_id` = `cake`.`id`",
766                "INNER JOIN `filling` AS `r1` ON `r1`.`id` = `r0`.`filling_id`",
767                "INNER JOIN `cake_filling` AS `r2` ON `r2`.`filling_id` = `r1`.`id`",
768                "INNER JOIN `cake` AS `r3` ON `r3`.`id` = `r2`.`cake_id`",
769                "WHERE `r3`.`id` = 18",
770            ]
771            .join(" ")
772        );
773    }
774
775    #[test]
776    fn join_24() {
777        let cake_model = cake::Model {
778            id: 12,
779            name: "".to_owned(),
780        };
781
782        assert_eq!(
783            cake_model
784                .find_linked_recursive(entity_linked::CakeToCakeViaFilling)
785                .build(DbBackend::MySql)
786                .to_string(),
787            [
788                "WITH `cte` AS (SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
789                "INNER JOIN `cake_filling` AS `r0` ON `r0`.`cake_id` = `cake`.`id`",
790                "INNER JOIN `filling` AS `r1` ON `r1`.`id` = `r0`.`filling_id`",
791                "INNER JOIN `cake_filling` AS `r2` ON `r2`.`filling_id` = `r1`.`id`",
792                "INNER JOIN `cake` AS `r3` ON `r3`.`id` = `r2`.`cake_id`",
793                "WHERE `r3`.`id` = 12",
794                "UNION ALL (SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
795                "INNER JOIN `cake_filling` AS `r0` ON `r0`.`cake_id` = `cake`.`id`",
796                "INNER JOIN `filling` AS `r1` ON `r1`.`id` = `r0`.`filling_id`",
797                "INNER JOIN `cake_filling` AS `r2` ON `r2`.`filling_id` = `r1`.`id`",
798                "INNER JOIN `cte` AS `r3` ON `r3`.`id` = `r2`.`cake_id`))",
799                "SELECT `cake`.`id`, `cake`.`name` FROM `cte` AS `cake`",
800            ]
801            .join(" ")
802        );
803    }
804
805    #[test]
806    fn join_25() {
807        assert_eq!(
808            cake::Entity::find()
809                .find_with_linked_recursive(entity_linked::CakeToCakeViaFilling)
810                .build(DbBackend::MySql)
811                .to_string(),
812            [
813                "WITH `cte` AS (SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
814                "UNION ALL (SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
815                "INNER JOIN `cake_filling` AS `r0` ON `r0`.`cake_id` = `cake`.`id`",
816                "INNER JOIN `filling` AS `r1` ON `r1`.`id` = `r0`.`filling_id`",
817                "INNER JOIN `cake_filling` AS `r2` ON `r2`.`filling_id` = `r1`.`id`",
818                "INNER JOIN `cte` AS `r3` ON `r3`.`id` = `r2`.`cake_id`))",
819                "SELECT `cake`.`id`, `cake`.`name` FROM `cte` AS `cake`",
820            ]
821            .join(" ")
822        );
823    }
824
825    #[test]
826    fn chained_left_join_linked_uses_previous_hop_alias() {
827        assert_eq!(
828            cake::Entity::find()
829                .left_join_linked(entity_linked::CakeToFilling)
830                .left_join_linked(entity_linked::CakeToFilling)
831                .select_only()
832                .column(cake::Column::Id)
833                .build(DbBackend::MySql)
834                .to_string(),
835            [
836                r"SELECT `cake`.`id` FROM `cake`",
837                r"LEFT JOIN `cake_filling` AS `r0` ON `cake`.`id` = `r0`.`cake_id`",
838                r"LEFT JOIN `filling` AS `r1` ON `r0`.`filling_id` = `r1`.`id`",
839                r"LEFT JOIN `cake_filling` AS `r2` ON `cake`.`id` = `r2`.`cake_id`",
840                r"LEFT JOIN `filling` AS `r3` ON `r2`.`filling_id` = `r3`.`id`",
841            ]
842            .join(" ")
843        );
844    }
845
846    #[derive(Debug)]
847    struct CakeToOrTropicalFruit;
848
849    impl crate::Linked for CakeToOrTropicalFruit {
850        type FromEntity = cake_compact::Entity;
851
852        type ToEntity = fruit::Entity;
853
854        fn link(&self) -> Vec<crate::LinkDef> {
855            vec![
856                cake_compact::Relation::Fruit
857                    .def()
858                    .condition_type(ConditionType::Any)
859                    .on_condition(|_left, right| {
860                        Expr::col((right, fruit::Column::Name))
861                            .like("%tropical%")
862                            .into_condition()
863                    }),
864            ]
865        }
866    }
867
868    #[test]
869    fn left_join_linked_respects_condition_type() {
870        assert_eq!(
871            cake_compact::Entity::find()
872                .left_join_linked(CakeToOrTropicalFruit)
873                .select_only()
874                .column(cake_compact::Column::Id)
875                .build(DbBackend::MySql)
876                .to_string(),
877            [
878                r"SELECT `cake`.`id` FROM `cake`",
879                r"LEFT JOIN `fruit` AS `r0` ON `cake`.`id` = `r0`.`cake_id` OR `r0`.`name` LIKE '%tropical%'",
880            ]
881            .join(" ")
882        );
883    }
884
885    #[test]
886    fn find_linked_respects_condition_type() {
887        let cake_model = cake_compact::Model {
888            id: 18,
889            name: "".to_owned(),
890        };
891
892        assert_eq!(
893            cake_model
894                .find_linked(CakeToOrTropicalFruit)
895                .build(DbBackend::MySql)
896                .to_string(),
897            [
898                r"SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
899                r"INNER JOIN `cake` AS `r0` ON `r0`.`id` = `fruit`.`cake_id` OR `fruit`.`name` LIKE '%tropical%'",
900                r"WHERE `r0`.`id` = 18",
901            ]
902            .join(" ")
903        );
904    }
905}
sea_orm/query/join.rs

sea_orm/query/
join.rs
