1use crate::{
2 ColumnTrait, EntityTrait, IdenStatic, Iterable, Linked, QuerySelect, QueryTrait, Related,
3 Select, SelectA, SelectB, SelectThree, SelectTwo, SelectTwoMany, TopologyChain, TopologyStar,
4 join_tbl_on_condition,
5};
6pub use sea_query::JoinType;
7use sea_query::{Condition, Expr, IntoIden, SelectExpr};
8
9impl<E> Select<E>
10where
11 E: EntityTrait,
12{
13 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 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 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 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 pub fn find_also_related<R>(self, 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 pub fn find_with_related<R>(self, r: R) -> SelectTwoMany<E, R>
59 where
60 R: EntityTrait,
61 E: Related<R>,
62 {
63 self.left_join(r).select_with(r)
64 }
65
66 pub fn find_also_linked<L, T>(self, l: L) -> SelectTwo<E, T>
68 where
69 L: Linked<FromEntity = E, ToEntity = T>,
70 T: EntityTrait,
71 {
72 SelectTwo::new_without_prepare(self.left_join_linked(l).into_query())
73 }
74
75 pub fn find_with_linked<L, T>(self, l: L) -> SelectTwoMany<E, T>
77 where
78 L: Linked<FromEntity = E, ToEntity = T>,
79 T: EntityTrait,
80 {
81 SelectTwoMany::new_without_prepare(self.left_join_linked(l).into_query())
82 }
83
84 pub fn left_join_linked<L, T>(mut self, l: L) -> Self
86 where
87 L: Linked<FromEntity = E, ToEntity = T>,
88 T: EntityTrait,
89 {
90 for (i, mut rel) in l.link().into_iter().enumerate() {
91 let r = self.linked_index;
92 self.linked_index += 1;
93 let to_tbl = format!("r{r}").into_iden();
94 let from_tbl = if i > 0 {
95 format!("r{}", i - 1).into_iden()
96 } else {
97 rel.from_tbl.sea_orm_table().clone()
98 };
99 let table_ref = rel.to_tbl;
100
101 let mut condition = Condition::all().add(join_tbl_on_condition(
102 from_tbl.clone(),
103 to_tbl.clone(),
104 rel.from_col,
105 rel.to_col,
106 ));
107 if let Some(f) = rel.on_condition.take() {
108 condition = condition.add(f(from_tbl.clone(), to_tbl.clone()));
109 }
110
111 self.query
112 .join_as(JoinType::LeftJoin, table_ref, to_tbl, condition);
113 }
114 self = self.apply_alias(SelectA.as_str());
115 for col in <T::Column as Iterable>::iter() {
116 let alias = format!("{}{}", SelectB.as_str(), col.as_str());
117 let expr = Expr::col((
118 format!("r{}", self.linked_index - 1).into_iden(),
119 col.into_iden(),
120 ));
121 self.query.expr(SelectExpr {
122 expr: col.select_as(expr),
123 alias: Some(alias.into_iden()),
124 window: None,
125 });
126 }
127 self
128 }
129}
130
131impl<E, F> SelectTwo<E, F>
132where
133 E: EntityTrait,
134 F: EntityTrait,
135{
136 pub fn find_also_related<R>(self, _: R) -> SelectThree<E, F, R, TopologyStar>
138 where
139 R: EntityTrait,
140 E: Related<R>,
141 {
142 SelectThree::new(
143 self.join_join(JoinType::LeftJoin, E::to(), E::via())
144 .into_query(),
145 )
146 }
147
148 pub fn and_also_related<R>(self, _: R) -> SelectThree<E, F, R, TopologyChain>
150 where
151 R: EntityTrait,
152 F: Related<R>,
153 {
154 SelectThree::new(
155 self.join_join(JoinType::LeftJoin, F::to(), F::via())
156 .into_query(),
157 )
158 }
159}
160
161#[cfg(test)]
162mod tests {
163 use crate::tests_cfg::{cake, cake_filling, cake_filling_price, entity_linked, filling, fruit};
164 use crate::{
165 ColumnTrait, DbBackend, EntityTrait, ModelTrait, QueryFilter, QuerySelect, QueryTrait,
166 RelationTrait,
167 };
168 use pretty_assertions::assert_eq;
169 use sea_query::{ConditionType, Expr, ExprTrait, IntoCondition, JoinType};
170
171 #[test]
172 fn join_1() {
173 assert_eq!(
174 cake::Entity::find()
175 .left_join(fruit::Entity)
176 .build(DbBackend::MySql)
177 .to_string(),
178 [
179 "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
180 "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id`",
181 ]
182 .join(" ")
183 );
184 }
185
186 #[test]
187 fn join_2() {
188 assert_eq!(
189 cake::Entity::find()
190 .inner_join(fruit::Entity)
191 .filter(fruit::Column::Name.contains("cherry"))
192 .build(DbBackend::MySql)
193 .to_string(),
194 [
195 "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
196 "INNER JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id`",
197 "WHERE `fruit`.`name` LIKE \'%cherry%\'"
198 ]
199 .join(" ")
200 );
201 }
202
203 #[test]
204 fn join_3() {
205 assert_eq!(
206 fruit::Entity::find()
207 .reverse_join(cake::Entity)
208 .build(DbBackend::MySql)
209 .to_string(),
210 [
211 "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
212 "INNER JOIN `cake` ON `cake`.`id` = `fruit`.`cake_id`",
213 ]
214 .join(" ")
215 );
216 }
217
218 #[test]
219 fn join_4() {
220 use crate::{Related, Select};
221
222 let find_fruit: Select<fruit::Entity> = cake::Entity::find_related();
223 assert_eq!(
224 find_fruit
225 .filter(cake::Column::Id.eq(11))
226 .build(DbBackend::MySql)
227 .to_string(),
228 [
229 "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
230 "INNER JOIN `cake` ON `cake`.`id` = `fruit`.`cake_id`",
231 "WHERE `cake`.`id` = 11",
232 ]
233 .join(" ")
234 );
235 }
236
237 #[test]
238 fn join_5() {
239 let cake_model = cake::Model {
240 id: 12,
241 name: "".to_owned(),
242 };
243
244 assert_eq!(
245 cake_model
246 .find_related(fruit::Entity)
247 .build(DbBackend::MySql)
248 .to_string(),
249 [
250 "SELECT `fruit`.`id`, `fruit`.`name`, `fruit`.`cake_id` FROM `fruit`",
251 "INNER JOIN `cake` ON `cake`.`id` = `fruit`.`cake_id`",
252 "WHERE `cake`.`id` = 12",
253 ]
254 .join(" ")
255 );
256 }
257
258 #[test]
259 fn join_6() {
260 assert_eq!(
261 cake::Entity::find()
262 .left_join(filling::Entity)
263 .build(DbBackend::MySql)
264 .to_string(),
265 [
266 "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
267 "LEFT JOIN `cake_filling` ON `cake`.`id` = `cake_filling`.`cake_id`",
268 "LEFT JOIN `filling` ON `cake_filling`.`filling_id` = `filling`.`id`",
269 ]
270 .join(" ")
271 );
272 }
273
274 #[test]
275 fn join_7() {
276 use crate::{Related, Select};
277
278 let find_filling: Select<filling::Entity> = cake::Entity::find_related();
279 assert_eq!(
280 find_filling.build(DbBackend::MySql).to_string(),
281 [
282 "SELECT `filling`.`id`, `filling`.`name`, `filling`.`vendor_id` FROM `filling`",
283 "INNER JOIN `cake_filling` ON `cake_filling`.`filling_id` = `filling`.`id`",
284 "INNER JOIN `cake` ON `cake`.`id` = `cake_filling`.`cake_id`",
285 ]
286 .join(" ")
287 );
288 }
289
290 #[test]
291 fn join_8() {
292 use crate::{Related, Select};
293
294 let find_cake_filling_price: Select<cake_filling_price::Entity> =
295 cake_filling::Entity::find_related();
296 assert_eq!(
297 find_cake_filling_price.build(DbBackend::Postgres).to_string(),
298 [
299 r#"SELECT "cake_filling_price"."cake_id", "cake_filling_price"."filling_id", "cake_filling_price"."price""#,
300 r#"FROM "public"."cake_filling_price""#,
301 r#"INNER JOIN "cake_filling" ON"#,
302 r#""cake_filling"."cake_id" = "cake_filling_price"."cake_id" AND"#,
303 r#""cake_filling"."filling_id" = "cake_filling_price"."filling_id""#,
304 ]
305 .join(" ")
306 );
307 }
308
309 #[test]
310 fn join_9() {
311 use crate::{Related, Select};
312
313 let find_cake_filling: Select<cake_filling::Entity> =
314 cake_filling_price::Entity::find_related();
315 assert_eq!(
316 find_cake_filling.build(DbBackend::Postgres).to_string(),
317 [
318 r#"SELECT "cake_filling"."cake_id", "cake_filling"."filling_id""#,
319 r#"FROM "cake_filling""#,
320 r#"INNER JOIN "public"."cake_filling_price" ON"#,
321 r#""cake_filling_price"."cake_id" = "cake_filling"."cake_id" AND"#,
322 r#""cake_filling_price"."filling_id" = "cake_filling"."filling_id""#,
323 ]
324 .join(" ")
325 );
326 }
327
328 #[test]
329 fn join_10() {
330 let cake_model = cake::Model {
331 id: 12,
332 name: "".to_owned(),
333 };
334
335 assert_eq!(
336 cake_model
337 .find_linked(entity_linked::CakeToFilling)
338 .build(DbBackend::MySql)
339 .to_string(),
340 [
341 r#"SELECT `filling`.`id`, `filling`.`name`, `filling`.`vendor_id`"#,
342 r#"FROM `filling`"#,
343 r#"INNER JOIN `cake_filling` AS `r0` ON `r0`.`filling_id` = `filling`.`id`"#,
344 r#"INNER JOIN `cake` AS `r1` ON `r1`.`id` = `r0`.`cake_id`"#,
345 r#"WHERE `r1`.`id` = 12"#,
346 ]
347 .join(" ")
348 );
349 }
350
351 #[test]
352 fn join_11() {
353 let cake_model = cake::Model {
354 id: 18,
355 name: "".to_owned(),
356 };
357
358 assert_eq!(
359 cake_model
360 .find_linked(entity_linked::CakeToFillingVendor)
361 .build(DbBackend::MySql)
362 .to_string(),
363 [
364 r#"SELECT `vendor`.`id`, `vendor`.`name`"#,
365 r#"FROM `vendor`"#,
366 r#"INNER JOIN `filling` AS `r0` ON `r0`.`vendor_id` = `vendor`.`id`"#,
367 r#"INNER JOIN `cake_filling` AS `r1` ON `r1`.`filling_id` = `r0`.`id`"#,
368 r#"INNER JOIN `cake` AS `r2` ON `r2`.`id` = `r1`.`cake_id`"#,
369 r#"WHERE `r2`.`id` = 18"#,
370 ]
371 .join(" ")
372 );
373 }
374
375 #[test]
376 fn join_12() {
377 assert_eq!(
378 cake::Entity::find()
379 .find_also_linked(entity_linked::CakeToFilling)
380 .build(DbBackend::MySql)
381 .to_string(),
382 [
383 r#"SELECT `cake`.`id` AS `A_id`, `cake`.`name` AS `A_name`,"#,
384 r#"`r1`.`id` AS `B_id`, `r1`.`name` AS `B_name`, `r1`.`vendor_id` AS `B_vendor_id`"#,
385 r#"FROM `cake`"#,
386 r#"LEFT JOIN `cake_filling` AS `r0` ON `cake`.`id` = `r0`.`cake_id`"#,
387 r#"LEFT JOIN `filling` AS `r1` ON `r0`.`filling_id` = `r1`.`id`"#,
388 ]
389 .join(" ")
390 );
391 }
392
393 #[test]
394 fn join_13() {
395 assert_eq!(
396 cake::Entity::find()
397 .find_also_linked(entity_linked::CakeToFillingVendor)
398 .build(DbBackend::MySql)
399 .to_string(),
400 [
401 r#"SELECT `cake`.`id` AS `A_id`, `cake`.`name` AS `A_name`,"#,
402 r#"`r2`.`id` AS `B_id`, `r2`.`name` AS `B_name`"#,
403 r#"FROM `cake`"#,
404 r#"LEFT JOIN `cake_filling` AS `r0` ON `cake`.`id` = `r0`.`cake_id`"#,
405 r#"LEFT JOIN `filling` AS `r1` ON `r0`.`filling_id` = `r1`.`id`"#,
406 r#"LEFT JOIN `vendor` AS `r2` ON `r1`.`vendor_id` = `r2`.`id`"#,
407 ]
408 .join(" ")
409 );
410 }
411
412 #[test]
413 fn join_14() {
414 assert_eq!(
415 cake::Entity::find()
416 .join(JoinType::LeftJoin, cake::Relation::TropicalFruit.def())
417 .build(DbBackend::MySql)
418 .to_string(),
419 [
420 "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
421 "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id` AND `fruit`.`name` LIKE '%tropical%'",
422 ]
423 .join(" ")
424 );
425 }
426
427 #[test]
428 fn join_15() {
429 let cake_model = cake::Model {
430 id: 18,
431 name: "".to_owned(),
432 };
433
434 assert_eq!(
435 cake_model
436 .find_linked(entity_linked::CheeseCakeToFillingVendor)
437 .build(DbBackend::MySql)
438 .to_string(),
439 [
440 r#"SELECT `vendor`.`id`, `vendor`.`name`"#,
441 r#"FROM `vendor`"#,
442 r#"INNER JOIN `filling` AS `r0` ON `r0`.`vendor_id` = `vendor`.`id`"#,
443 r#"INNER JOIN `cake_filling` AS `r1` ON `r1`.`filling_id` = `r0`.`id`"#,
444 r#"INNER JOIN `cake` AS `r2` ON `r2`.`id` = `r1`.`cake_id` AND `r2`.`name` LIKE '%cheese%'"#,
445 r#"WHERE `r2`.`id` = 18"#,
446 ]
447 .join(" ")
448 );
449 }
450
451 #[test]
452 fn join_16() {
453 let cake_model = cake::Model {
454 id: 18,
455 name: "".to_owned(),
456 };
457 assert_eq!(
458 cake_model
459 .find_linked(entity_linked::JoinWithoutReverse)
460 .build(DbBackend::MySql)
461 .to_string(),
462 [
463 r#"SELECT `vendor`.`id`, `vendor`.`name`"#,
464 r#"FROM `vendor`"#,
465 r#"INNER JOIN `filling` AS `r0` ON `r0`.`vendor_id` = `vendor`.`id`"#,
466 r#"INNER JOIN `cake_filling` AS `r1` ON `r1`.`filling_id` = `r0`.`id`"#,
467 r#"INNER JOIN `cake_filling` AS `r2` ON `r2`.`cake_id` = `r1`.`id` AND `r2`.`name` LIKE '%cheese%'"#,
468 r#"WHERE `r2`.`id` = 18"#,
469 ]
470 .join(" ")
471 );
472 }
473
474 #[test]
475 fn join_17() {
476 assert_eq!(
477 cake::Entity::find()
478 .find_also_linked(entity_linked::CheeseCakeToFillingVendor)
479 .build(DbBackend::MySql)
480 .to_string(),
481 [
482 r#"SELECT `cake`.`id` AS `A_id`, `cake`.`name` AS `A_name`,"#,
483 r#"`r2`.`id` AS `B_id`, `r2`.`name` AS `B_name`"#,
484 r#"FROM `cake`"#,
485 r#"LEFT JOIN `cake_filling` AS `r0` ON `cake`.`id` = `r0`.`cake_id` AND `cake`.`name` LIKE '%cheese%'"#,
486 r#"LEFT JOIN `filling` AS `r1` ON `r0`.`filling_id` = `r1`.`id`"#,
487 r#"LEFT JOIN `vendor` AS `r2` ON `r1`.`vendor_id` = `r2`.`id`"#,
488 ]
489 .join(" ")
490 );
491 }
492
493 #[test]
494 fn join_18() {
495 assert_eq!(
496 cake::Entity::find()
497 .find_also_linked(entity_linked::JoinWithoutReverse)
498 .build(DbBackend::MySql)
499 .to_string(),
500 [
501 r#"SELECT `cake`.`id` AS `A_id`, `cake`.`name` AS `A_name`,"#,
502 r#"`r2`.`id` AS `B_id`, `r2`.`name` AS `B_name`"#,
503 r#"FROM `cake`"#,
504 r#"LEFT JOIN `cake` AS `r0` ON `cake_filling`.`cake_id` = `r0`.`id` AND `cake_filling`.`name` LIKE '%cheese%'"#,
505 r#"LEFT JOIN `filling` AS `r1` ON `r0`.`filling_id` = `r1`.`id`"#,
506 r#"LEFT JOIN `vendor` AS `r2` ON `r1`.`vendor_id` = `r2`.`id`"#,
507 ]
508 .join(" ")
509 );
510 }
511
512 #[test]
513 fn join_19() {
514 assert_eq!(
515 cake::Entity::find()
516 .join(JoinType::LeftJoin, cake::Relation::TropicalFruit.def())
517 .join(
518 JoinType::LeftJoin,
519 cake_filling::Relation::Cake
520 .def()
521 .rev()
522 .on_condition(|_left, right| {
523 Expr::col((right, cake_filling::Column::CakeId))
524 .gt(10)
525 .into_condition()
526 })
527 )
528 .join(
529 JoinType::LeftJoin,
530 cake_filling::Relation::Filling
531 .def()
532 .on_condition(|_left, right| {
533 Expr::col((right, filling::Column::Name))
534 .like("%lemon%")
535 .into_condition()
536 })
537 )
538 .join(JoinType::LeftJoin, filling::Relation::Vendor.def())
539 .build(DbBackend::MySql)
540 .to_string(),
541 [
542 "SELECT `cake`.`id`, `cake`.`name` FROM `cake`",
543 "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id` AND `fruit`.`name` LIKE '%tropical%'",
544 "LEFT JOIN `cake_filling` ON `cake`.`id` = `cake_filling`.`cake_id` AND `cake_filling`.`cake_id` > 10",
545 "LEFT JOIN `filling` ON `cake_filling`.`filling_id` = `filling`.`id` AND `filling`.`name` LIKE '%lemon%'",
546 "LEFT JOIN `vendor` ON `filling`.`vendor_id` = `vendor`.`id`",
547 ]
548 .join(" ")
549 );
550 }
551
552 #[test]
553 fn join_20() {
554 assert_eq!(
555 cake::Entity::find()
556 .column_as(
557 Expr::col(("fruit_alias", fruit::Column::Name)),
558 "fruit_name"
559 )
560 .join_as(
561 JoinType::LeftJoin,
562 cake::Relation::Fruit
563 .def()
564 .on_condition(|_left, right| {
565 Expr::col((right, fruit::Column::Name))
566 .like("%tropical%")
567 .into_condition()
568 }),
569 "fruit_alias"
570 )
571 .build(DbBackend::MySql)
572 .to_string(),
573 [
574 "SELECT `cake`.`id`, `cake`.`name`, `fruit_alias`.`name` AS `fruit_name` FROM `cake`",
575 "LEFT JOIN `fruit` AS `fruit_alias` ON `cake`.`id` = `fruit_alias`.`cake_id` AND `fruit_alias`.`name` LIKE '%tropical%'",
576 ]
577 .join(" ")
578 );
579 }
580
581 #[test]
582 fn join_21() {
583 assert_eq!(
584 cake::Entity::find()
585 .column_as(
586 Expr::col(("cake_filling_alias", cake_filling::Column::CakeId)),
587 "cake_filling_cake_id"
588 )
589 .join(JoinType::LeftJoin, cake::Relation::TropicalFruit.def())
590 .join_as_rev(
591 JoinType::LeftJoin,
592 cake_filling::Relation::Cake
593 .def()
594 .on_condition(|left, _right| {
595 Expr::col((left, cake_filling::Column::CakeId))
596 .gt(10)
597 .into_condition()
598 }),
599 "cake_filling_alias"
600 )
601 .build(DbBackend::MySql)
602 .to_string(),
603 [
604 "SELECT `cake`.`id`, `cake`.`name`, `cake_filling_alias`.`cake_id` AS `cake_filling_cake_id` FROM `cake`",
605 "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id` AND `fruit`.`name` LIKE '%tropical%'",
606 "LEFT JOIN `cake_filling` AS `cake_filling_alias` ON `cake_filling_alias`.`cake_id` = `cake`.`id` AND `cake_filling_alias`.`cake_id` > 10",
607 ]
608 .join(" ")
609 );
610 }
611
612 #[test]
613 fn join_22() {
614 assert_eq!(
615 cake::Entity::find()
616 .column_as(
617 Expr::col(("cake_filling_alias", cake_filling::Column::CakeId)),
618 "cake_filling_cake_id"
619 )
620 .join(JoinType::LeftJoin, cake::Relation::OrTropicalFruit.def())
621 .join_as_rev(
622 JoinType::LeftJoin,
623 cake_filling::Relation::Cake
624 .def()
625 .condition_type(ConditionType::Any)
626 .on_condition(|left, _right| {
627 Expr::col((left, cake_filling::Column::CakeId))
628 .gt(10)
629 .into_condition()
630 }),
631 "cake_filling_alias"
632 )
633 .build(DbBackend::MySql)
634 .to_string(),
635 [
636 "SELECT `cake`.`id`, `cake`.`name`, `cake_filling_alias`.`cake_id` AS `cake_filling_cake_id` FROM `cake`",
637 "LEFT JOIN `fruit` ON `cake`.`id` = `fruit`.`cake_id` OR `fruit`.`name` LIKE '%tropical%'",
638 "LEFT JOIN `cake_filling` AS `cake_filling_alias` ON `cake_filling_alias`.`cake_id` = `cake`.`id` OR `cake_filling_alias`.`cake_id` > 10",
639 ]
640 .join(" ")
641 );
642 }
643}