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