headless_lms_models/
user_details.rs

1use std::collections::HashMap;
2
3use futures::Stream;
4
5use crate::{prelude::*, users::User};
6
7#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
8#[cfg_attr(feature = "ts_rs", derive(TS))]
9pub struct UserDetail {
10    pub user_id: Uuid,
11    pub created_at: DateTime<Utc>,
12    pub updated_at: DateTime<Utc>,
13    pub email: String,
14    pub first_name: Option<String>,
15    pub last_name: Option<String>,
16    pub search_helper: Option<String>,
17    pub country: Option<String>,
18}
19
20pub async fn get_user_details_by_user_id(
21    conn: &mut PgConnection,
22    user_id: Uuid,
23) -> ModelResult<UserDetail> {
24    let res = sqlx::query_as!(
25        UserDetail,
26        "
27SELECT *
28FROM user_details
29WHERE user_id = $1 ",
30        user_id
31    )
32    .fetch_one(conn)
33    .await?;
34    Ok(res)
35}
36
37pub async fn get_users_details_by_user_id_map(
38    conn: &mut PgConnection,
39    users: &[User],
40) -> ModelResult<HashMap<Uuid, UserDetail>> {
41    let ids = users.iter().map(|u| u.id).collect::<Vec<_>>();
42    let details = sqlx::query_as!(
43        UserDetail,
44        "
45SELECT *
46FROM user_details
47WHERE user_id IN (
48    SELECT UNNEST($1::uuid [])
49  )
50",
51        &ids
52    )
53    .fetch_all(conn)
54    .await?;
55    let mut res = HashMap::new();
56    details.into_iter().for_each(|d| {
57        res.insert(d.user_id, d);
58    });
59    Ok(res)
60}
61
62/// Includes all users who have returned an exercise on a course
63pub fn stream_users_details_having_user_exercise_states_on_course(
64    conn: &mut PgConnection,
65    course_id: Uuid,
66) -> impl Stream<Item = sqlx::Result<UserDetail>> + '_ {
67    sqlx::query_as!(
68        UserDetail,
69        "
70SELECT distinct (ud.user_id),
71 ud.created_at,
72 ud.updated_at,
73 ud.first_name,
74 ud.last_name,
75 ud.email,
76 ud.search_helper,
77 ud.country
78FROM user_details ud
79JOIN users u
80  ON u.id = ud.user_id
81JOIN user_exercise_states ues
82  ON ud.user_id = ues.user_id
83JOIN course_instances ci
84  ON ci.id = ues.course_instance_id
85WHERE ci.course_id = $1
86  AND u.deleted_at IS NULL
87  AND ues.deleted_at IS NULL
88  AND ci.deleted_at IS NULL;
89        ",
90        course_id
91    )
92    .fetch(conn)
93}
94
95pub async fn search_for_user_details_by_email(
96    conn: &mut PgConnection,
97    email: &str,
98) -> ModelResult<Vec<UserDetail>> {
99    let res = sqlx::query_as!(
100        UserDetail,
101        "
102SELECT *
103FROM user_details
104WHERE lower(email::text) LIKE '%' || lower($1) || '%'
105LIMIT 1000;
106",
107        email.trim(),
108    )
109    .fetch_all(conn)
110    .await?;
111    Ok(res)
112}
113
114pub async fn search_for_user_details_by_other_details(
115    conn: &mut PgConnection,
116    search: &str,
117) -> ModelResult<Vec<UserDetail>> {
118    let res = sqlx::query_as!(
119        UserDetail,
120        "
121SELECT *
122FROM user_details
123WHERE lower(search_helper::text) LIKE '%' || lower($1) || '%'
124LIMIT 1000;
125",
126        search.trim(),
127    )
128    .fetch_all(conn)
129    .await?;
130    Ok(res)
131}
132
133pub async fn search_for_user_details_fuzzy_match(
134    conn: &mut PgConnection,
135    search: &str,
136) -> ModelResult<Vec<UserDetail>> {
137    // For email domains, the fuzzy match returns too much results that have the same domain
138    // To combat this, we omit the email domain from the fuzzy match
139    let search = search.split('@').next().unwrap_or(search);
140
141    let res = sqlx::query_as!(
142        UserDetail,
143        "
144SELECT user_id,
145  created_at,
146  updated_at,
147  email,
148  first_name,
149  last_name,
150  search_helper,
151  country
152FROM (
153    SELECT *,
154      LOWER($1) <<->search_helper AS dist
155    FROM user_details
156    ORDER BY dist, LENGTH(search_helper)
157    LIMIT 100
158  ) search
159WHERE dist < 0.7;
160",
161        search.trim(),
162    )
163    .fetch_all(conn)
164    .await?;
165    Ok(res)
166}
167
168/// Retrieves all users enrolled in a specific course
169pub async fn get_users_by_course_id(
170    conn: &mut PgConnection,
171    course_id: Uuid,
172) -> ModelResult<Vec<UserDetail>> {
173    let res = sqlx::query_as!(
174        UserDetail,
175        r#"
176SELECT d.user_id,
177  d.created_at,
178  d.updated_at,
179  d.email,
180  d.first_name,
181  d.last_name,
182  d.search_helper,
183  d.country
184FROM course_instance_enrollments e
185  JOIN user_details d ON e.user_id = d.user_id
186WHERE e.course_id = $1
187  AND e.deleted_at IS NULL
188        "#,
189        course_id
190    )
191    .fetch_all(conn)
192    .await?;
193
194    Ok(res)
195}
196
197pub async fn update_user_country(
198    conn: &mut PgConnection,
199    user_id: Uuid,
200    country: &str,
201) -> Result<(), sqlx::Error> {
202    sqlx::query!(
203        r#"
204UPDATE user_details
205SET country = $1
206WHERE user_id = $2
207"#,
208        country,
209        user_id,
210    )
211    .execute(conn)
212    .await?;
213    Ok(())
214}
215
216pub async fn update_user_info(
217    conn: &mut PgConnection,
218    user_id: Uuid,
219    first_name: &str,
220    last_name: &str,
221    country: &str,
222) -> Result<(), sqlx::Error> {
223    sqlx::query!(
224        r#"
225UPDATE user_details
226SET first_name = $1,
227  last_name = $2,
228  country = $3
229WHERE user_id = $4
230"#,
231        first_name,
232        last_name,
233        country,
234        user_id,
235    )
236    .execute(conn)
237    .await?;
238    Ok(())
239}
headless_lms_models/user_details.rs

headless_lms_models/
user_details.rs
