1use rust_rel8::{
2 helper_tables::{One, Two},
3 *,
4};
5use sqlx::PgPool;
6
7#[derive(Debug, PartialEq, rust_rel8_derive::TableStruct)]
8struct User<'scope, Mode: TableMode = ExprMode> {
9 id: Mode::T<'scope, i32>,
10 name: Mode::T<'scope, String>,
11 age: Mode::T<'scope, i32>,
12}
13
14impl<'scope> User<'scope, NameMode> {
15 const SCHEMA: TableSchema<Self> = TableSchema {
16 name: "users",
17 columns: User {
18 id: "id",
19 name: "name",
20 age: "age",
21 },
22 };
23}
24
25#[derive(Debug, PartialEq, rust_rel8_derive::TableStruct)]
26struct Post<'scope, Mode: TableMode = ExprMode> {
27 id: Mode::T<'scope, i32>,
28 user_id: Mode::T<'scope, i32>,
29 contents: Mode::T<'scope, String>,
30}
31
32impl<'scope> Post<'scope, NameMode> {
33 const SCHEMA: TableSchema<Self> = TableSchema {
34 name: "posts",
35 columns: Post {
36 id: "id",
37 user_id: "user_id",
38 contents: "contents",
39 },
40 };
41}
42
43#[derive(rust_rel8_derive::TableStruct)]
44#[perfect_derive::perfect_derive(Debug, PartialEq, Clone)]
45struct Common<'scope, Mode: TableMode = ExprMode> {
46 id: Mode::T<'scope, i32>,
47 created: Mode::T<'scope, i32>,
48}
49
50#[derive(rust_rel8_derive::TableStruct)]
51#[perfect_derive::perfect_derive(Debug, PartialEq, Clone)]
52struct UsesCommon<'scope, Mode: TableMode = ExprMode> {
53 name: Mode::T<'scope, String>,
54 #[table(nested)]
55 common: Common<'scope, Mode>,
56}
57
58#[test]
59fn does_it_work() {
60 let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
61 let a = q.q(Query::values([1, 2, 3].map(|a| One { a })));
62 let b = q.q(Query::values([4, 5, 6].map(|a| One { a })));
63 return (a.a, b.a);
64 });
65 drop(q);
66}
67
68#[async_std::test]
69async fn no_queries() {
70 let q = query::<Expr<i32>>(|_q| {
71 return Expr::lit(1i32);
72 });
73
74 let db_string = format!(
75 "postgresql://{}@postgres?host={}",
76 std::env::var("USER").unwrap(),
77 std::env::var("PGHOST").unwrap(),
78 );
79
80 let connection = PgPool::connect(&db_string).await.unwrap();
81 let mut pool = connection.try_acquire().unwrap();
82
83 let rows = q.all(&mut *pool).await.unwrap();
84
85 assert_eq!(vec![1i32], rows)
86}
87
88fn with_number_matching<'a>(number: Expr<'a, i32>) -> Query<Expr<'a, i32>> {
89 query(|q| {
90 let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
91 q.where_(x.clone().equals(number));
92 return x.add(Expr::lit(1i32));
93 })
94}
95
96#[async_std::test]
97async fn test_each_with_inc() {
98 let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
99 let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
100 let b = q.q(with_number_matching(a.clone()));
101 (a, b)
102 });
103
104 let db_string = format!(
105 "postgresql://{}@postgres?host={}",
106 std::env::var("USER").unwrap(),
107 std::env::var("PGHOST").unwrap(),
108 );
109
110 let connection = PgPool::connect(&db_string).await.unwrap();
111 let mut pool = connection.try_acquire().unwrap();
112
113 let rows = q.all(&mut *pool).await.unwrap();
114
115 assert_eq!(vec![(1, 2), (2, 3), (3, 4)], rows)
116}
117
118#[async_std::test]
119async fn and_filters() {
120 let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
121 let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
122 let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
123 q.where_(a.clone().equals(b.clone()));
124 return (a, b);
125 });
126
127 let db_string = format!(
128 "postgresql://{}@postgres?host={}",
129 std::env::var("USER").unwrap(),
130 std::env::var("PGHOST").unwrap(),
131 );
132
133 let connection = PgPool::connect(&db_string).await.unwrap();
134 let mut pool = connection.try_acquire().unwrap();
135
136 let rows = q.all(&mut *pool).await.unwrap();
137
138 assert_eq!(vec![(2, 2), (3, 3)], rows);
139
140 let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
141 let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
142 let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
143 return (a, b);
144 })
145 .filter(|(a, b)| a.clone().equals(b.clone()));
146
147 let rows = q.all(&mut *pool).await.unwrap();
148
149 assert_eq!(vec![(2, 2), (3, 3)], rows);
150}
151
152#[async_std::test]
153async fn double_nested() {
154 let db_string = format!(
155 "postgresql://{}@postgres?host={}",
156 std::env::var("USER").unwrap(),
157 std::env::var("PGHOST").unwrap(),
158 );
159
160 let connection = PgPool::connect(&db_string).await.unwrap();
161 let mut pool = connection.try_acquire().unwrap();
162
163 let q = query::<((Expr<i32>, Expr<i32>), (Expr<i32>, Expr<i32>))>(|q| {
164 let inner_q = query::<(Expr<i32>, Expr<i32>)>(|q| {
165 let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
166 let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
167 q.where_(a.clone().equals(b.clone()));
168 return (a, b);
169 });
170
171 let x = q.q(inner_q.clone());
172 let y = q.q(inner_q);
173
174 return (x, y);
175 });
176
177 let rows = q.all(&mut *pool).await.unwrap();
178
179 assert_eq!(
180 vec![
181 ((2, 2), (2, 2)),
182 ((2, 2), (3, 3)),
183 ((3, 3), (2, 2)),
184 ((3, 3), (3, 3))
185 ],
186 rows
187 )
188}
189
190#[async_std::test]
191async fn optionals() {
192 let db_string = format!(
193 "postgresql://{}@postgres?host={}",
194 std::env::var("USER").unwrap(),
195 std::env::var("PGHOST").unwrap(),
196 );
197
198 let connection = PgPool::connect(&db_string).await.unwrap();
199 let mut pool = connection.try_acquire().unwrap();
200
201 let q = query::<(MaybeTable<Expr<i32>>, Expr<i32>)>(|q| {
202 let empty_query = query::<Expr<i32>>(|q| {
203 let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
204 q.where_(Expr::lit(false));
205 x
206 });
207 let a = q.q(empty_query.optional());
208 let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
209 return (a, b);
210 });
211
212 let rows = q.all(&mut *pool).await.unwrap();
213
214 assert_eq!(vec![(None::<i32>, 2), (None, 3), (None, 4)], rows);
215
216 let q = query::<(MaybeTable<Expr<i32>>, Expr<i32>)>(|q| {
217 let empty_query = query::<Expr<i32>>(|q| {
218 let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
219 q.where_(Expr::lit(true));
220 x
221 });
222 let a = q.q(empty_query.optional());
223 let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
224 return (a, b);
225 });
226
227 let rows = q.all(&mut *pool).await.unwrap();
228
229 assert_eq!(
230 vec![
231 (Some::<i32>(1), 2),
232 (Some(1), 3),
233 (Some(1), 4),
234 (Some(2), 2),
235 (Some(2), 3),
236 (Some(2), 4),
237 (Some(3), 2),
238 (Some(3), 3),
239 (Some(3), 4)
240 ],
241 rows
242 );
243
244 let q = query::<(Expr<Option<i32>>, Expr<i32>)>(|q| {
245 let empty_query = query::<(Expr<i32>, Expr<i32>)>(|q| {
246 let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
247 q.where_(Expr::lit(false));
248 (x.clone(), x)
249 });
250 let a = q.q(empty_query.optional()).project(|v| v.0.clone());
251 let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
252 return (a, b);
253 });
254
255 let rows = q.all(&mut *pool).await.unwrap();
256
257 assert_eq!(vec![(None::<i32>, 2), (None, 3), (None, 4)], rows);
258
259 let q = query::<(Expr<Option<i32>>, Expr<i32>)>(|q| {
260 let empty_query = query::<(Expr<i32>, Expr<i32>)>(|q| {
261 let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
262 q.where_(Expr::lit(true));
263 (x.clone(), x)
264 });
265 let a = q.q(empty_query.optional()).project(|v| v.0.clone());
266 let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
267 return (a, b);
268 });
269
270 let rows = q.all(&mut *pool).await.unwrap();
271
272 assert_eq!(
273 vec![
274 (Some::<i32>(1), 2),
275 (Some(1), 3),
276 (Some(1), 4),
277 (Some(2), 2),
278 (Some(2), 3),
279 (Some(2), 4),
280 (Some(3), 2),
281 (Some(3), 3),
282 (Some(3), 4)
283 ],
284 rows
285 );
286}
287
288#[async_std::test]
289async fn optionals_maybe() {
290 let db_string = format!(
291 "postgresql://{}@postgres?host={}",
292 std::env::var("USER").unwrap(),
293 std::env::var("PGHOST").unwrap(),
294 );
295
296 let connection = PgPool::connect(&db_string).await.unwrap();
297 let mut pool = connection.try_acquire().unwrap();
298
299 let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
300 let empty_query = query::<Expr<i32>>(|q| {
301 let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
302 q.where_(Expr::lit(false));
303 x
304 });
305 let a = q.q(empty_query.optional()).maybe(Expr::lit(99), |t| t);
306 let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
307 return (a, b);
308 });
309
310 let rows = q.all(&mut *pool).await.unwrap();
311
312 assert_eq!(vec![(99, 2), (99, 3), (99, 4)], rows);
313
314 let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
315 let empty_query = query::<Expr<i32>>(|q| {
316 let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
317 q.where_(Expr::lit(true));
318 x
319 });
320 let a = q.q(empty_query.optional()).maybe(Expr::lit(99), |t| t);
321 let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
322 return (a, b);
323 });
324
325 let rows = q.all(&mut *pool).await.unwrap();
326
327 assert_eq!(
328 vec![
329 (1, 2),
330 (1, 3),
331 (1, 4),
332 (2, 2),
333 (2, 3),
334 (2, 4),
335 (3, 2),
336 (3, 3),
337 (3, 4)
338 ],
339 rows
340 );
341}
342
343#[async_std::test]
344async fn my_table() {
345 let db_string = format!(
346 "postgresql://{}@postgres?host={}",
347 std::env::var("USER").unwrap(),
348 std::env::var("PGHOST").unwrap(),
349 );
350
351 let connection = PgPool::connect(&db_string).await.unwrap();
352 let mut pool = connection.try_acquire().unwrap();
353
354 sqlx::query("drop table if exists users cascade")
355 .execute(&mut *pool)
356 .await
357 .unwrap();
358
359 sqlx::query(
360 "create table users (id INTEGER GENERATED BY DEFAULT AS IDENTITY, name TEXT, age INTEGER)",
361 )
362 .execute(&mut *pool)
363 .await
364 .unwrap();
365
366 sqlx::query("insert into users values (DEFAULT, 'ben', 0), (DEFAULT, 'foo', 1)")
367 .execute(&mut *pool)
368 .await
369 .unwrap();
370
371 let q = query::<User<ExprMode>>(|q| {
372 let user = q.q(Query::each(&User::SCHEMA));
373 q.where_(user.name.clone().equals(Expr::lit("ben".to_string())));
374 user
375 });
376
377 let rows = q.all(&mut *pool).await.unwrap();
378
379 assert_eq!(
380 vec![User {
381 id: 1i32,
382 name: "ben".to_string(),
383 age: 0
384 }],
385 rows
386 )
387}
388
389#[async_std::test]
390async fn nextval_stuff() {
391 let db_string = format!(
392 "postgresql://{}@postgres?host={}",
393 std::env::var("USER").unwrap(),
394 std::env::var("PGHOST").unwrap(),
395 );
396
397 let connection = PgPool::connect(&db_string).await.unwrap();
398 let mut pool = connection.try_acquire().unwrap();
399
400 sqlx::query("drop table if exists nextval_test")
401 .execute(&mut *pool)
402 .await
403 .unwrap();
404
405 sqlx::query("create table nextval_test (id SERIAL PRIMARY KEY)")
406 .execute(&mut *pool)
407 .await
408 .unwrap();
409
410 let q = query::<(Expr<i32>, Expr<i32>, Expr<i32>)>(|q| {
411 let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
412 let b = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
413 let c = q.q(query::<Expr<i32>>(|q| {
414 q.q(Query::evaluate(Expr::nextval("nextval_test_id_seq")))
415 }));
416 (a, b, c)
417 });
418
419 let rows = q.all(&mut *pool).await.unwrap();
420
421 assert_eq!(
422 vec![
423 (1, 1, 1),
424 (1, 2, 2),
425 (1, 3, 3),
426 (2, 1, 4),
427 (2, 2, 5),
428 (2, 3, 6),
429 (3, 1, 7),
430 (3, 2, 8),
431 (3, 3, 9)
432 ],
433 rows
434 );
435
436 sqlx::query("drop table if exists nextval_test")
438 .execute(&mut *pool)
439 .await
440 .unwrap();
441
442 sqlx::query("create table nextval_test (id SERIAL PRIMARY KEY)")
443 .execute(&mut *pool)
444 .await
445 .unwrap();
446
447 let q = query::<(Expr<i32>, Expr<i32>, Expr<i32>)>(|q| {
448 let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
449 let b = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
450 let c = q.q(query::<Expr<i32>>(|_| Expr::nextval("nextval_test_id_seq")));
451 (a, b, c)
452 });
453
454 let rows = q.all(&mut *pool).await.unwrap();
455
456 assert_eq!(
457 vec![
458 (1, 1, 1),
459 (1, 2, 1),
460 (1, 3, 1),
461 (2, 1, 1),
462 (2, 2, 1),
463 (2, 3, 1),
464 (3, 1, 1),
465 (3, 2, 1),
466 (3, 3, 1)
467 ],
468 rows
469 );
470}
471
472#[async_std::test]
473async fn join_example() {
474 let db_string = format!(
475 "postgresql://{}@postgres?host={}",
476 std::env::var("USER").unwrap(),
477 std::env::var("PGHOST").unwrap(),
478 );
479
480 let connection = PgPool::connect(&db_string).await.unwrap();
481 let mut pool = connection.try_acquire().unwrap();
482
483 let demo_users = vec![
484 User::<ValueMode> {
485 id: 0,
486 name: "Undine".to_owned(),
487 age: 3,
488 },
489 User {
490 id: 1,
491 name: "Leschy".to_owned(),
492 age: 3,
493 },
494 User {
495 id: 2,
496 name: "Huldra".to_owned(),
497 age: 2,
498 },
499 ];
500
501 fn posts_of_user(user_id: Expr<i32>) -> Query<Post> {
502 let demo_posts = vec![
503 Post::<ValueMode> {
504 id: 0,
505 user_id: 0,
506 contents: "Croak".to_owned(),
507 },
508 Post {
509 id: 1,
510 user_id: 1,
511 contents: "Quak!".to_owned(),
512 },
513 Post {
514 id: 2,
515 user_id: 1,
516 contents: "Hello".to_owned(),
517 },
518 ];
519
520 query::<Post<ExprMode>>(|q| {
521 let post = q.q(Query::values(demo_posts));
522 q.where_(user_id.equals(post.user_id.clone()));
523 post
524 })
525 }
526
527 let q = query::<(Expr<String>, Expr<String>)>(|q| {
528 let user = q.q(Query::values(demo_users));
529 let post = q.q(posts_of_user(user.id.clone()));
530
531 (user.name, post.contents)
532 })
533 .order_by(|x| (x.clone(), sea_query::Order::Asc));
534
535 let rows = q.all(&mut *pool).await.unwrap();
536
537 assert_eq!(
538 vec![
539 ("Leschy".to_owned(), "Hello".to_owned()),
540 ("Leschy".to_owned(), "Quak!".to_owned()),
541 ("Undine".to_owned(), "Croak".to_owned())
542 ],
543 rows
544 )
545}
546
547#[async_std::test]
548async fn join_example_optional() {
549 let db_string = format!(
550 "postgresql://{}@postgres?host={}",
551 std::env::var("USER").unwrap(),
552 std::env::var("PGHOST").unwrap(),
553 );
554
555 let connection = PgPool::connect(&db_string).await.unwrap();
556 let mut pool = connection.try_acquire().unwrap();
557
558 let demo_users = vec![
559 User::<ValueMode> {
560 id: 0,
561 name: "Undine".to_owned(),
562 age: 3,
563 },
564 User {
565 id: 1,
566 name: "Leschy".to_owned(),
567 age: 3,
568 },
569 User {
570 id: 2,
571 name: "Huldra".to_owned(),
572 age: 2,
573 },
574 ];
575
576 fn latest_post_of_user(user_id: Expr<i32>) -> Query<MaybeTable<Post>> {
577 let demo_posts = vec![
578 Post::<ValueMode> {
579 id: 0,
580 user_id: 0,
581 contents: "Croak".to_owned(),
582 },
583 Post {
584 id: 1,
585 user_id: 1,
586 contents: "Quak!".to_owned(),
587 },
588 ];
589
590 query::<Post<ExprMode>>(|q| {
591 let post = q.q(Query::values(demo_posts));
592 q.where_(user_id.equals(post.user_id.clone()));
593 post
594 })
595 .order_by(|x| (x.id.clone(), sea_query::Order::Desc))
596 .limit(1)
597 .optional()
598 }
599
600 let q = query::<(Expr<String>, Expr<Option<String>>)>(|q| {
601 let user = q.q(Query::values(demo_users));
602 let post = q.q(latest_post_of_user(user.id.clone()));
603
604 (user.name, post.project(|p| p.contents.clone()))
605 })
606 .order_by(|x| (x.clone(), sea_query::Order::Asc));
607
608 let rows = q.all(&mut *pool).await.unwrap();
609
610 assert_eq!(
611 vec![
612 ("Huldra".to_owned(), None),
613 ("Leschy".to_owned(), Some("Quak!".to_owned())),
614 ("Undine".to_owned(), Some("Croak".to_owned()))
615 ],
616 rows
617 )
618}
619
620#[async_std::test]
621async fn nested() {
622 let db_string = format!(
623 "postgresql://{}@postgres?host={}",
624 std::env::var("USER").unwrap(),
625 std::env::var("PGHOST").unwrap(),
626 );
627
628 let connection = PgPool::connect(&db_string).await.unwrap();
629 let mut pool = connection.try_acquire().unwrap();
630
631 let demo_things = vec![
632 UsesCommon::<ValueMode> {
633 name: "Undine".to_owned(),
634 common: Common { id: 1, created: 0 },
635 },
636 UsesCommon::<ValueMode> {
637 name: "Leschy".to_owned(),
638 common: Common { id: 2, created: 0 },
639 },
640 UsesCommon::<ValueMode> {
641 name: "Huldra".to_owned(),
642 common: Common { id: 3, created: 0 },
643 },
644 ];
645
646 let q = query::<(UsesCommon, Common)>(|q| {
647 let thing = q.q(Query::values(demo_things));
648 let common = thing.common.clone();
649 (thing, common)
650 })
651 .order_by(|(_, common)| (common.id.clone(), sea_query::Order::Asc));
652
653 let rows = q.all(&mut *pool).await.unwrap();
654
655 assert_eq!(
656 vec![
657 (
658 UsesCommon {
659 name: "Undine".to_owned(),
660 common: Common { id: 1, created: 0 }
661 },
662 Common { id: 1, created: 0 }
663 ),
664 (
665 UsesCommon {
666 name: "Leschy".to_owned(),
667 common: Common { id: 2, created: 0 }
668 },
669 Common { id: 2, created: 0 }
670 ),
671 (
672 UsesCommon {
673 name: "Huldra".to_owned(),
674 common: Common { id: 3, created: 0 }
675 },
676 Common { id: 3, created: 0 }
677 )
678 ],
679 rows
680 )
681}
682
683#[async_std::test]
684async fn aggregations_simple() {
685 let db_string = format!(
686 "postgresql://{}@postgres?host={}",
687 std::env::var("USER").unwrap(),
688 std::env::var("PGHOST").unwrap(),
689 );
690
691 let connection = PgPool::connect(&db_string).await.unwrap();
692 let mut pool = connection.try_acquire().unwrap();
693
694 let q = query::<Expr<i32>>(|q| {
695 let a = q.q(Query::values([1, 1, 2, 2, 2, 3].map(|a| One { a }))).a;
696 a
697 })
698 .aggregate::<ListTable<Expr<i32>>>(|a, e| {
699 let _e = a.group_by(e.clone());
700 let as_array = a.array_agg(e);
701 as_array
702 });
703
704 let rows = q.all(&mut *pool).await.unwrap();
705
706 assert_eq!(vec![vec![1, 1], vec![3], vec![2, 2, 2]], rows);
707
708 let q = query::<Two<_, i32, i32>>(|q| {
709 let a = q.q(Query::values([
710 Two { a: 1, b: 1 },
711 Two { a: 1, b: 2 },
712 Two { a: 1, b: 3 },
713 Two { a: 1, b: 4 },
714 Two { a: 2, b: 1 },
715 Two { a: 2, b: 2 },
716 Two { a: 3, b: 1 },
717 ]));
718 a
719 })
720 .aggregate::<(Expr<i32>, ListTable<Expr<i32>>, ListTable<Two<_, i32, i32>>)>(|a, e| {
721 let x = a.group_by(e.a.clone());
722 let y = a.array_agg(e.a.clone().add(Expr::lit(1i32)));
723 let as_array = a.array_agg(e);
724 (x, y, as_array)
725 });
726
727 let rows = q.all(&mut *pool).await.unwrap();
728
729 assert_eq!(
730 vec![
731 (
732 1,
733 vec![2, 2, 2, 2],
734 vec![
735 Two { a: 1, b: 4 },
736 Two { a: 1, b: 3 },
737 Two { a: 1, b: 2 },
738 Two { a: 1, b: 1 }
739 ]
740 ),
741 (3, vec![4], vec![Two { a: 3, b: 1 }]),
742 (2, vec![3, 3], vec![Two { a: 2, b: 2 }, Two { a: 2, b: 1 }])
743 ],
744 rows
745 );
746}
747
748#[async_std::test]
749async fn windows_simple() {
750 let db_string = format!(
751 "postgresql://{}@postgres?host={}",
752 std::env::var("USER").unwrap(),
753 std::env::var("PGHOST").unwrap(),
754 );
755
756 let connection = PgPool::connect(&db_string).await.unwrap();
757 let mut pool = connection.try_acquire().unwrap();
758
759 let q = query::<Two<_, i32, i32>>(|q| {
760 let a = q.q(Query::values([
761 Two { a: 1, b: 1 },
762 Two { a: 1, b: 2 },
763 Two { a: 1, b: 3 },
764 Two { a: 1, b: 4 },
765 Two { a: 2, b: 1 },
766 Two { a: 2, b: 2 },
767 Two { a: 3, b: 1 },
768 ]));
769 a
770 })
771 .window::<(Expr<i64>, Two<_, i32, i32>, Two<_, i32, i32>)>(|a, e| {
772 let over = Over::new().partition_by(e.a.clone());
773 let row_num = a.row_number(over.clone());
774 let id_e = a.id(e.clone());
775 let lagged_e = a.lag(e, None, Some(Two { a: 0, b: 0 }.lit()), over);
776
777 (row_num, id_e, lagged_e)
778 });
779
780 let rows = q.all(&mut *pool).await.unwrap();
781
782 assert_eq!(
783 vec![
784 (1, Two { a: 1, b: 1 }, Two { a: 0, b: 0 }),
785 (2, Two { a: 1, b: 2 }, Two { a: 1, b: 1 }),
786 (3, Two { a: 1, b: 3 }, Two { a: 1, b: 2 }),
787 (4, Two { a: 1, b: 4 }, Two { a: 1, b: 3 }),
788 (1, Two { a: 2, b: 1 }, Two { a: 0, b: 0 }),
789 (2, Two { a: 2, b: 2 }, Two { a: 2, b: 1 }),
790 (1, Two { a: 3, b: 1 }, Two { a: 0, b: 0 })
791 ],
792 rows
793 );
794}
795
796#[async_std::test]
797async fn join_example_many() {
798 let db_string = format!(
799 "postgresql://{}@postgres?host={}",
800 std::env::var("USER").unwrap(),
801 std::env::var("PGHOST").unwrap(),
802 );
803
804 let connection = PgPool::connect(&db_string).await.unwrap();
805 let mut pool = connection.try_acquire().unwrap();
806
807 let demo_users = vec![
808 User::<ValueMode> {
809 id: 0,
810 name: "Undine".to_owned(),
811 age: 3,
812 },
813 User {
814 id: 1,
815 name: "Leschy".to_owned(),
816 age: 3,
817 },
818 User {
819 id: 2,
820 name: "Huldra".to_owned(),
821 age: 2,
822 },
823 ];
824
825 fn posts_of_user(user_id: Expr<i32>) -> Query<Expr<String>> {
826 let demo_posts = vec![
827 Post::<ValueMode> {
828 id: 0,
829 user_id: 0,
830 contents: "Croak".to_owned(),
831 },
832 Post {
833 id: 1,
834 user_id: 1,
835 contents: "Quak!".to_owned(),
836 },
837 Post {
838 id: 2,
839 user_id: 1,
840 contents: "Hello".to_owned(),
841 },
842 ];
843
844 query(|q| {
845 let post = q.q(Query::values(demo_posts));
846 q.where_(user_id.equals(post.user_id.clone()));
847 post.contents
848 })
849 }
850
851 let q = query::<(Expr<String>, ListTable<Expr<String>>)>(|q| {
852 let user = q.q(Query::values(demo_users));
853 let posts = q.q(posts_of_user(user.id.clone()).many());
854
855 (user.name, posts)
856 })
857 .order_by(|x| (x.0.clone(), sea_query::Order::Asc));
858
859 let rows = q.all(&mut *pool).await.unwrap();
860
861 assert_eq!(
862 vec![
863 ("Huldra".to_owned(), vec![]),
864 (
865 "Leschy".to_owned(),
866 vec!["Quak!".to_owned(), "Hello".to_owned()]
867 ),
868 ("Undine".to_owned(), vec!["Croak".to_owned()])
869 ],
870 rows
871 )
872}