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

sea_orm/query/
join.rs
