cartel-sqlite 0.26.0

SQLite driver for the dope runtime
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

use crate::{
    common::schema::Comment, common::schema::Post, common::schema::Tag, common::schema::User,
};

#[cartel_sqlite::query]
pub fn user_with_posts() -> Vec<(User, Post)> {
    User::join::<Post>(|u, p| u.id == p.author_id)
        .filter(|_u, _p| true)
        .order_by(|_u, p| p.id)
        .all()
}

#[cartel_sqlite::query]
pub fn user_left_join_posts() -> i64 {
    User::left_join::<Post>(|u, p| u.id == p.author_id)
        .filter(|_u, _p| true)
        .count()
}

#[cartel_sqlite::query]
pub fn user_post_comment() -> Vec<(User, Post, Comment)> {
    User::join::<Post>(|u, p| u.id == p.author_id)
        .join::<Comment>(|_u, p, c| p.id == c.post_id)
        .filter(|_u, _p, _c| true)
        .order_by(|_u, _p, c| c.id)
        .all()
}

#[cartel_sqlite::query]
pub fn user_post_comment_tag() -> Vec<(User, Post, Comment, Tag)> {
    User::join::<Post>(|u, p| u.id == p.author_id)
        .join::<Comment>(|_u, p, c| p.id == c.post_id)
        .join::<Tag>(|_u, p, _c, t| p.id == t.post_id)
        .filter(|_u, _p, _c, _t| true)
        .order_by(|_u, _p, c, _t| c.id)
        .all()
}

#[cartel_sqlite::query]
pub fn count_user_posts() -> i64 {
    User::join::<Post>(|u, p| u.id == p.author_id)
        .filter(|_u, _p| true)
        .count()
}

#[cartel_sqlite::query]
pub fn post_titles_by_user(uid: i64) -> Vec<String> {
    User::join::<Post>(|u, p| u.id == p.author_id)
        .filter(|u, _p| u.id == uid)
        .select(|_u, p| p.title)
        .all()
}

#[cartel_sqlite::query]
pub fn users_union() -> Vec<User> {
    User::filter(|u| u.id <= 1)
        .union(User::filter(|u| u.id >= 4))
        .rows()
        .order_by(|u| u.id)
        .all()
}

#[cartel_sqlite::query]
pub fn users_union_all() -> Vec<User> {
    User::filter(|u| u.id <= 1)
        .union_all(User::filter(|u| u.id == 1))
        .rows()
        .order_by(|u| u.id)
        .all()
}

#[cartel_sqlite::query]
pub fn users_intersect() -> Vec<User> {
    User::filter(|u| u.age >= 25)
        .intersect(User::filter(|u| u.active))
        .rows()
        .order_by(|u| u.id)
        .all()
}

#[cartel_sqlite::query]
pub fn users_except() -> Vec<User> {
    User::filter(|_u| true)
        .except(User::filter(|u| !u.active))
        .rows()
        .order_by(|u| u.id)
        .all()
}

#[cartel_sqlite::query]
pub fn cte_active_users() -> Vec<User> {
    let active = User::filter(User::active).cte();
    active
        .filter(|u| u.age >= 25)
        .rows()
        .order_by(|u| u.id)
        .all()
}

#[cartel_sqlite::query]
pub fn cte_union(min_age: i32) -> Vec<User> {
    let young = User::filter(User::age < 30).cte();
    let actives = User::filter(User::active).cte();
    young
        .filter(|u| u.age >= min_age)
        .union(actives.filter(|u| u.age >= min_age))
        .rows()
        .order_by(|u| u.id)
        .all()
}

#[cartel_sqlite::query]
pub fn users_with_any_post() -> Vec<User> {
    User::filter(|u| Dialect::exists(Post::filter(|p| p.author_id == u.id)))
        .rows()
        .order_by(|u| u.id)
        .all()
}

#[cartel_sqlite::query]
pub fn users_without_post() -> Vec<User> {
    User::filter(|u| Dialect::not_exists(Post::filter(|p| p.author_id == u.id)))
        .rows()
        .order_by(|u| u.id)
        .all()
}

#[cartel_sqlite::query]
pub fn user_post_counts() -> Vec<(i64, i64, Option<i64>)> {
    User::filter(|_u| true)
        .rows()
        .order_by(|u| u.id)
        .select(|u| {
            (
                u.id,
                Post::filter(|p| p.author_id == u.id).count(),
                Post::filter(|p| p.author_id == u.id).max(|p| p.id),
            )
        })
        .all()
}