sea_orm/query/
join.rs

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

sea_orm/query/
join.rs
