Changes between Version 2 and Version 3 of enrollInCourse
- Timestamp:
- 08/05/26 22:15:43 (10 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
enrollInCourse
v2 v3 1 1 == Купување на курс 2 2 3 === Актери 3 4 - Најавен корисник 4 === Чекор 1 5 6 === Чекор 1 7 5 8 На најавен корисник му се прикажува листа од сите достапни курсеви од каде корисникот селектира еден. 6 9 7 {{{ 10 {{{ 8 11 SELECT 9 c. id,12 c.course_id, 10 13 c.image_url, 11 14 c.color, … … 21 24 course_translate 22 25 WHERE 23 course_id = c. id26 course_id = c.course_id 24 27 ) AS translated_languages, 25 28 ( … … 29 32 course_content cc 30 33 JOIN course_version cv 31 ON cc.course_version_id = cv. id32 WHERE 33 cv.course_id = c. id34 AND cv. active = TRUE34 ON cc.course_version_id = cv.course_version_id 35 WHERE 36 cv.course_id = c.course_id 37 AND cv.is_active 35 38 ) AS course_content_count, 36 39 ( … … 40 43 course_lecture cl 41 44 JOIN course_content cc 42 ON cl.course_content_id = cc. id43 JOIN course_version cv 44 ON cc.course_version_id = cv. id45 WHERE 46 cv.course_id = c. id47 AND cv. active = TRUE45 ON cl.course_content_id = cc.course_content_id 46 JOIN course_version cv 47 ON cc.course_version_id = cv.course_version_id 48 WHERE 49 cv.course_id = c.course_id 50 AND cv.is_active 48 51 ) AS course_lecture_count, 49 52 ( … … 53 56 review r 54 57 JOIN enrollment e 55 ON e. id = r.enrollment_id56 JOIN course_version cv 57 ON cv. id = e.course_version_id58 WHERE 59 cv.course_id = c. id58 ON e.enrollment_id = r.enrollment_id 59 JOIN course_version cv 60 ON cv.course_version_id = e.course_version_id 61 WHERE 62 cv.course_id = c.course_id 60 63 ) AS avg_rating, 61 64 ( … … 65 68 course_tag ctag 66 69 JOIN tag t 67 ON t.id = ctag.tag_id 68 JOIN tag_translate tt 69 ON t.id = tt.tag_id 70 WHERE 71 ctag.course_id = c.id 72 AND tt.language = 'en' 73 AND t.type = 'skill' 70 ON t.tag_id = ctag.tag_id 71 JOIN tag_translate tt 72 ON tt.tag_id = t.tag_id 73 JOIN language l 74 ON l.id = tt.language_id 75 WHERE 76 ctag.course_id = c.course_id 77 AND l.value = 'en' 78 AND t.tag_type = 'skill' 74 79 ) AS skills, 75 80 ( … … 79 84 course_tag ctag 80 85 JOIN tag t 81 ON t.id = ctag.tag_id 82 JOIN tag_translate tt 83 ON t.id = tt.tag_id 84 WHERE 85 ctag.course_id = c.id 86 AND tt.language = 'en' 87 AND t.type = 'interest' 86 ON t.tag_id = ctag.tag_id 87 JOIN tag_translate tt 88 ON tt.tag_id = t.tag_id 89 JOIN language l 90 ON l.id = tt.language_id 91 WHERE 92 ctag.course_id = c.course_id 93 AND l.value = 'en' 94 AND t.tag_type = 'topic' 88 95 ) AS topics 89 96 FROM 90 97 course c 91 98 JOIN course_translate ct 92 ON c.id = ct.course_id 99 ON ct.course_id = c.course_id 100 JOIN language lang 101 ON lang.id = ct.language_id 93 102 WHERE 94 ct.language = 'en'; 103 lang.value = 'en' 104 AND EXISTS ( 105 SELECT 1 106 FROM course_version cv 107 WHERE cv.course_id = c.course_id 108 AND cv.is_active 109 ); 95 110 }}} 96 97 111 98 112 === Чекор 2 … … 102 116 {{{ 103 117 SELECT 104 c. id,118 c.course_id, 105 119 c.image_url, 106 120 c.color, … … 120 134 course_translate 121 135 WHERE 122 course_id = c. id136 course_id = c.course_id 123 137 ) AS translated_languages, 124 138 … … 129 143 course_content cc 130 144 JOIN course_version cv 131 ON cc.course_version_id = cv. id132 WHERE 133 cv.course_id = c. id134 AND cv. active = TRUE145 ON cc.course_version_id = cv.course_version_id 146 WHERE 147 cv.course_id = c.course_id 148 AND cv.is_active 135 149 ) AS course_content_count, 136 150 … … 141 155 course_lecture cl 142 156 JOIN course_content cc 143 ON cl.course_content_id = cc. id144 JOIN course_version cv 145 ON cc.course_version_id = cv. id146 WHERE 147 cv.course_id = c. id148 AND cv. active = TRUE157 ON cl.course_content_id = cc.course_content_id 158 JOIN course_version cv 159 ON cc.course_version_id = cv.course_version_id 160 WHERE 161 cv.course_id = c.course_id 162 AND cv.is_active 149 163 ) AS course_lecture_count, 150 164 … … 155 169 review r 156 170 JOIN enrollment e 157 ON e. id = r.enrollment_id158 JOIN course_version cv 159 ON cv. id = e.course_version_id160 WHERE 161 cv.course_id = c. id171 ON e.enrollment_id = r.enrollment_id 172 JOIN course_version cv 173 ON cv.course_version_id = e.course_version_id 174 WHERE 175 cv.course_id = c.course_id 162 176 ) AS avg_rating, 163 177 … … 168 182 course_tag ctag 169 183 JOIN tag t 170 ON t.id = ctag.tag_id 171 JOIN tag_translate tt 172 ON t.id = tt.tag_id 173 WHERE 174 ctag.course_id = c.id 175 AND tt.language = 'en' 176 AND t.type = 'skill' 184 ON t.tag_id = ctag.tag_id 185 JOIN tag_translate tt 186 ON tt.tag_id = t.tag_id 187 JOIN language l 188 ON l.id = tt.language_id 189 WHERE 190 ctag.course_id = c.course_id 191 AND l.value = 'en' 192 AND t.tag_type = 'skill' 177 193 ) AS skills, 178 194 … … 183 199 course_tag ctag 184 200 JOIN tag t 185 ON t.id = ctag.tag_id 186 JOIN tag_translate tt 187 ON t.id = tt.tag_id 188 WHERE 189 ctag.course_id = c.id 190 AND tt.language = 'en' 191 AND t.type = 'interest' 201 ON t.tag_id = ctag.tag_id 202 JOIN tag_translate tt 203 ON tt.tag_id = t.tag_id 204 JOIN language l 205 ON l.id = tt.language_id 206 WHERE 207 ctag.course_id = c.course_id 208 AND l.value = 'en' 209 AND t.tag_type = 'topic' 192 210 ) AS topics, 193 211 194 ( 195 SELECT 196 JSON_AGG(ctwwbl.what_will_be_learned) 197 FROM 198 course_translate_what_will_be_learned ctwwbl 199 WHERE 200 ctwwbl.course_translate_id = ct.id 201 ) AS what_will_be_learned, 212 -- what_will_be_learned е text[] колона во course_translate. 213 -- Ако се врати нормализираната табела course_translate_what_will_be_learned, 214 -- ова се заменува со корелиран подзапрос по ct.course_translate_id. 215 TO_JSON(ct.what_will_be_learned) AS what_will_be_learned, 202 216 203 217 ( … … 222 236 course_lecture cl 223 237 JOIN course_lecture_translate clt 224 ON clt.course_lecture_id = cl.id 238 ON clt.course_lecture_id = cl.course_lecture_id 239 JOIN language ll 240 ON ll.id = clt.language_id 225 241 WHERE 226 cl.course_content_id = cc. id227 AND clt.language = 'en'242 cl.course_content_id = cc.course_content_id 243 AND ll.value = 'en' 228 244 ) 229 245 ) … … 233 249 course_content cc 234 250 JOIN course_content_translate cct 235 ON cct.course_content_id = cc.id 236 JOIN course_version cv 237 ON cc.course_version_id = cv.id 238 WHERE 239 cv.course_id = c.id 240 AND cv.active = TRUE 241 AND cct.language = 'en' 251 ON cct.course_content_id = cc.course_content_id 252 JOIN language lc 253 ON lc.id = cct.language_id 254 JOIN course_version cv 255 ON cc.course_version_id = cv.course_version_id 256 WHERE 257 cv.course_id = c.course_id 258 AND cv.is_active 259 AND lc.value = 'en' 242 260 ) AS course_content 243 261 … … 245 263 course c 246 264 JOIN course_translate ct 247 ON c.id = ct.course_id 265 ON ct.course_id = c.course_id 266 JOIN language lang 267 ON lang.id = ct.language_id 248 268 249 269 WHERE 250 ct.language = 'en'; 270 lang.value = 'en' 271 AND c.course_id = :course_id; 251 272 }}} 252 273 253 === Чекор и3254 255 Корисникот кликнува на копчето за купување на курс 274 === Чекор 3 275 276 Корисникот кликнува на копчето за купување на курс. 256 277 257 278 {{{ 258 INSERT INTO enrollment (purchase_date, activation_date, completion_date, enrollment_status, user_id, course_version_id) 259 VALUES ('2026-01-25 00:00:00', null, null, 'pending', 1, 2); 279 INSERT INTO enrollment (purchase_date, activation_date, completion_date, 280 enrollment_status, user_id, course_version_id) 281 VALUES (DATE '2026-01-25', NULL, NULL, 'pending', 1, 2) 282 RETURNING enrollment_id; 260 283 }}} 261 284 262 285 purchase_date -> датум на купување 263 activation_date -> датум на активација 264 completion_date -> датум на завршување 265 enrollment_status -> статус за тоа дали не е почнат, е почнат или е завршен курсот ( default е pending бидејќи при купување на курсот тојне е започнат)286 activation_date -> датум на активација (NULL додека курсот не е активиран) 287 completion_date -> датум на завршување (NULL додека курсот не е завршен) 288 enrollment_status -> статус за тоа дали не е почнат, е почнат или е завршен курсот (при купување се внесува 'pending' бидејќи курсот сè уште не е започнат) 266 289 user_id -> ид на корисник 267 290 course_version_id -> ид на верзија на курсот која се купува 268 291 269 270 271 272 273 292 Ограничувањето `ck_enrollment_status_dates` бара: 293 - 'pending' -> activation_date IS NULL И completion_date IS NULL 294 - 'active' -> activation_date IS NOT NULL И completion_date IS NULL 295 - 'completed' -> двете полиња се пополнети 296 297 Ограничувањето `uq_enrollment_user_version` не дозволува ист корисник да купи иста верзија од курс двапати.
