Project

General

Profile

Feature #4191

Courses analytics catalog

Added by Yannick Warnier over 7 years ago. Updated over 7 years ago.

Status:
Feature implemented
Priority:
Normal
Assignee:
Category:
-
Target version:
Start date:
22/12/2011
Due date:
% Done:

100%

Estimated time:
5.00 h
Spent time:
Complexity:
Normal
SCRUM pts - complexity:
?

Description

We are missing ways to push users into learning (with the best courses on the platform).

A common way to classify courses is to:

  • show the latest courses
  • show the hottest (most used) courses
  • show the best scored (by users) courses

To get those values quickly, it is not a good idea to scan the course_access table nor the course table, because these are used massively and continuously, so I suggest creating a new table:

CREATE TABLE track_course_classification(
 id   int unsigned not null PRIMARY KEY AUTO_INCREMENT,
 cid  int unsigned not null, --course id
 sid  int unsigned not null default 0, --session id
 aid  int unsigned not null default 0, --address id (or "URL" id)
 cdate datetime not null default NOW(), --date of created in these conditions (session and url)
 accesses int unsigned not null default 0, -- total number of accesses in these conditions (session and url)
 uvote int unsigned not null default 0, --total number of voting points in these conditions (session and url)
 uvote_count int unsigned not null default 0 --total number of users who voted in these conditions (so we can divide uvote by uvotenum to get the score as x/5)
);
ALTER TABLE track_course_classification ADD INDEX idx_tcc_cid (cid);
ALTER TABLE track_course_classification ADD INDEX idx_tcc_sid (sid);
ALTER TABLE track_course_classification ADD INDEX idx_tcc_aid (aid);
ALTER TABLE track_course_classification ADD INDEX idx_tcc_cdate (cdate);

We would also need a table user_course_vote (I think we already did plan that but I'm not sure we actually have a field to store the value).

CREATE TABLE user_course_vote(
  id int unsigned not null AUTO_INCREMENT PRIMARY KEY,
  user_id int unsigned not null, -- user id (avoid using "uid" for database portability - not supported in oracle)
  cid int unsigned not null, -- course id
  sid int unsigned not null default 0, -- session id
  aid int unsigned not null default 0  -- URL id
);
ALTER TABLE user_course_vote ADD INDEX idx_ucv_uid (user_id);
ALTER TABLE user_course_vote ADD INDEX idx_ucv_cid (cid);
ALTER TABLE user_course_vote ADD INDEX idx_ucv_cuid (user_id, cid);

The changes required into the code would be:

  • hack the event_course_access() function to store the access into the track_course_classification table ("add 1" for the current course, session and url)
  • hack the course creation function to store the cdate with sid = 0 and aid = [url id]
  • hack the functions to assign (or remove) a course in a session, to add or remove an entry in track_course_classification with the cid and the sid
  • hack the functions to assign (or remove) a course from a URL

One additional step would be to store the information about the user vote, which is basically adding some kind of "five star" plugin to the course catalog so each user can make his vote by clicking the amount of stars he wants. This calls an AJAX function which adds (or updates) an entry in user_course_vote and adds or remove a few points from track_course_classification.uvote and adds or remove one point from uvote_count.

The final step would be to add three "blocks" as tpls in Smarty to "plug" anywhere we want, which would show, for the current url and session, the current latest, hottest and most popular courses.


Files

rating.png (21.5 KB) rating.png Julio Montoya, 26/12/2011 17:46

Related issues

Related to Chamilo LMS - Feature #4200: Courses catalogue as block on homepageFeature implemented23/12/2011

Actions

Associated revisions

Revision f89a25ff (diff)
Added by Julio Montoya over 7 years ago

Fixing counter field update due the global chat see #4191

History

#1

Updated by Yannick Warnier over 7 years ago

  • Estimated time set to 5.00 h
#2

Updated by Julio Montoya over 7 years ago

Why using those complicated name fields?

Field names should be self explanatory:

  1. cid should be renamed to c_id (all course tables have the c_id)
  2. sid should be renamed to session_id (we use that name field everywhere)
  3. aid should be url_id or address_id (to be more easy to use)
  4. cdate should be creation_date
  5. track_course_classification should be renamed to track_course_ranking or track_course_popularity
  6. uvote ==> votes
  7. uvote_count => number_users or users_count or just users
  8. 1 more thing using NOW was deprecated as far as I know in Chamilo
#3

Updated by Julio Montoya over 7 years ago

  • Status changed from New to Assigned
  • Assignee set to Julio Montoya

I'm going to create those tables:

CREATE TABLE track_course_ranking (
 id   int unsigned not null PRIMARY KEY AUTO_INCREMENT,
 c_id  int unsigned not null,
 session_id  int unsigned not null default 0,
 url_id  int unsigned not null default 0,
 creation_date datetime not null,
 accesses int unsigned not null default 0,
 votes int unsigned not null default 0,
 users int unsigned not null default 0
);

ALTER TABLE track_course_ranking ADD INDEX idx_tcc_cid (c_id);
ALTER TABLE track_course_ranking ADD INDEX idx_tcc_sid (session_id);
ALTER TABLE track_course_ranking ADD INDEX idx_tcc_urlid (url_id);
ALTER TABLE track_course_ranking ADD INDEX idx_tcc_creation_date (creation_date);

CREATE TABLE user_course_vote(
  id int unsigned not null AUTO_INCREMENT PRIMARY KEY,
  c_id int unsigned not null,
  user_id int unsigned not null,  
  session_id int unsigned not null default 0,
  url_id int unsigned not null default 0
);

ALTER TABLE user_course_vote ADD INDEX idx_ucv_cid (c_id);
ALTER TABLE user_course_vote ADD INDEX idx_ucv_uid (user_id);
ALTER TABLE user_course_vote ADD INDEX idx_ucv_cuid (user_id, c_id);

#4

Updated by Julio Montoya over 7 years ago

In the user_course_vote table i'm adding a vote field otherwise we don't now how many points the user put in that course

#5

Updated by Julio Montoya over 7 years ago

  • File rating.png rating.png added
  • Status changed from Assigned to Needs more info
  • Assignee deleted (Julio Montoya)
  • % Done changed from 0 to 80
#6

Updated by Yannick Warnier over 7 years ago

  • Status changed from Needs more info to Assigned
  • Assignee set to Julio Montoya
  • Target version set to 1.9 Stable
  • % Done changed from 80 to 70

The view in the catalogue on chamilodev.beeznest.com right now (once course per line) is definitely better than what we had (I guess we can't play much if we have a variable course title width anyway)!

When I press the stars for a course, it says it's saved, but it doesn't change the number of votes currently saved, and no score appears as a result of my vote (in number of stars).

This is all finishing touches, but it looks great right now.
One thing we could add (because I guess not everybody goes to the course catalogue) is a plugin (working like the "date" plugin) that would show in the header or the footer of Chamilo and say:

Current score for this course: * * * _ _
Did you like this course? Vote it up!

Something like that... (this can be saved as a separate task and done later on just before the 1.9 release).

#7

Updated by Yannick Warnier over 7 years ago

Julio Montoya wrote:

usability need to be improved

I'm waiting validation for the table fields, names

http://code.google.com/p/chamilo/source/detail?r=212b48dcaca2177d47a5fd15bf9f16b47f21673f&repo=classic
http://code.google.com/p/chamilo/source/detail?r=e9e33f3d1520f3027545d5cb2b5fae2f8bcdda05&repo=classic

"points" is a dangerous name for an SQL field (I guess it could be used in the future for geographical points in some database system). Could you change it to "total_score"?

I wrote my examples too quickly: in order to respect our previous naming convention, and because the user_course_vote is a relation between "user" and "course", it should be called "user_rel_course_vote". My fault.

The rest is OK, so this is a "Go", but make sure you fix the update of the score in the catalog: once someone has votes, he cannot vote anymore (an option could be that he can change his vote, which seems more complete to me).

#8

Updated by Julio Montoya over 7 years ago

1. yeah I know about the refreshing issue (after selecting a star), I will manage it later

2. ok for the new task about the hot courses block.

3. The system already supports that the user can change his vote, but due the problem in 1. is not evident to see it

4. I will change points to total_score and the table name ...

#9

Updated by Julio Montoya over 7 years ago

  • Status changed from Assigned to Needs more info
  • Assignee changed from Julio Montoya to Yannick Warnier
#10

Updated by Marko Kastelic over 7 years ago

connections per month counting is wrong. looks like every click inside a course adds 3 connections to the count ...

#11

Updated by Julio Montoya over 7 years ago

  • Assignee deleted (Yannick Warnier)

Marko Kastelic wrote:

connections per month counting is wrong. looks like every click inside a course adds 3 connections to the count ...

I also noticed that behaviour that was fixed in the latest version of mercurial, if that's not the case please report it

#12

Updated by Marko Kastelic over 7 years ago

nope, entering course adds three or four 'connection', using various tools from two to four. Reporting from fresh install (jan 18 changeset 16532 6babcaa3589d )

#13

Updated by Julio Montoya over 7 years ago

  • Status changed from Needs more info to Assigned
  • Assignee set to Julio Montoya

Marko Kastelic wrote:

nope, entering course adds three or four 'connection', using various tools from two to four. Reporting from fresh install (jan 18 changeset 16532 6babcaa3589d )

great ! I will take a look

#14

Updated by Julio Montoya over 7 years ago

When clicking in a course the visits show +1, then when clicking in the document tool I'm also adding +1, when clicking in a folder +1 etc

#15

Updated by Julio Montoya over 7 years ago

seems that the function event_course_login() is called everytime I click in a link in a course ...

#16

Updated by Julio Montoya over 7 years ago

this was my fault because I have 2 tabs: one with:

localhost/chamilonet/index.php

and the other

http://localhost/chamilonet/main/document/document.php?cidReq=HISTORY&id_session=0&gidReq=0

so a cidReset (course id is considered null) is called in index.php so it's like I leave the course and I'm entering again ...

I don't now if you did the same?

I could fix the "visits count" if I'm considering a new visit like a visit every 30 minutes or something like that?

What do you think?

#17

Updated by Marko Kastelic over 7 years ago

ah, a kind a problem about a philosophy what the 'visit' is ?
a) 1 visit can be : 1 user inside 1 (time)session as you proposed.
b) counting just 'entering' the course (course index page and possible other entry points like LP
not sure about that, it's not correct counting or is it?

/* i notices strange counting on local fresh install after about a half an hour playing: count shows 3-digit number ! So i opened second window for count checking. Simple refresh increments counting too.
so i can expect at least 5-digit number after 6 months on a course with 30-40 active users ... i'm afraid of many mocking and sour smiles at the sight of that number ...
*/

Will do complete fresh install later today and try some count tracking and possibly figured something out of it.

#18

Updated by Marko Kastelic over 7 years ago

fresh database, fresh install. Here's action description:
sequence:
course creation
entering course (admin)
add user to course (admin)
entering course (user)
access document tool (user)

track_e_course_access table shows 19 records; note: every course access generates three records for the table (it's not just +1)
I left the client and logged-in user for for about 7 hours. Than the relevant tables were checked. here's the part of the content:

tables (relevant content):
track_course_ranking.access = 4 (ok)
track_e_access.count(access_id) = 4 (ok)
track_e_course_access : * count(course_access_id) = 389 (??)

7 hours inactivity generates 300+ records to the table when should be no additional one ?
some 'sniffing' between client and server shows constant flow of requests:

#request# GET http://localhost/chamiloC/main/inc/ajax/chat.ajax.php?action=chatheartbeat&_=1327217388939
GET /chamiloC/main/inc/ajax/chat.ajax.php?action=chatheartbeat&_=1327217388939

from the log file:

http://localhost/chamiloC/main/inc/ajax/chat.ajax.php?action=chatheartbeat&_=1327221276908

GET /chamiloC/main/inc/ajax/chat.ajax.php?action=chatheartbeat&_=1327221276908 HTTP/1.1
Host: localhost
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:9.0.1) Gecko/20100101 Firefox/9.0.1
Accept: application/json, text/javascript, */*; q=0.01
Accept-Language: sl,en-gb;q=0.7,en;q=0.3
Accept-Encoding: gzip, deflate
Accept-Charset: ISO-8859-2,utf-8;q=0.7,*;q=0.7
Connection: keep-alive
X-Requested-With: XMLHttpRequest
Referer: http://localhost/chamiloC/user_portal.php
Cookie: PHPSESSID=qi45ilnv4jrjpmnt8ecmf99g42; ch_sid=9f08egrlspau7g2slpl9p0v7d7

HTTP/1.1 200 OK
Date: Sun, 22 Jan 2012 08:34:36 GMT
Server: Apache/2.2.17 (Win32) PHP/5.3.5
X-Powered-By: PHP/5.3.5
Expires: Thu, 19 Nov 1981 08:52:00 GMT
Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
Pragma: no-cache
Content-Length: 12
Keep-Alive: timeout=5, max=100
Connection: Keep-Alive
Content-Type: text/html
----------------------------------------------------------
http://localhost/chamiloC/main/inc/ajax/chat.ajax.php?action=chatheartbeat&_=1327221310297

GET /chamiloC/main/inc/ajax/chat.ajax.php?action=chatheartbeat&_=1327221310297 HTTP/1.1
Host: localhost
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:9.0.1) Gecko/20100101 Firefox/9.0.1
Accept: application/json, text/javascript, */*; q=0.01
Accept-Language: sl,en-gb;q=0.7,en;q=0.3
Accept-Encoding: gzip, deflate

could it be related to #3656 (global chat implementation; )?

edit (21:24 GMT):
yes, disabling global chat results in correct counting : every access or tool usage increments click-counter by 1 and there is no additional counter increments in the time of inactivity.
further i'd like to suggest to change the hit-cunter text from 'Connections last month' . Can me missleading as in my case - counter counts course and course-tools usage (comulative) not how many times a course is accessed.

#19

Updated by Julio Montoya over 7 years ago

  • Status changed from Assigned to Needs more info
  • Assignee deleted (Julio Montoya)
#20

Updated by Marko Kastelic over 7 years ago

good work. Counting is fine.
just noticed 2 other things:
a) playing audio from documents->audio does not increment counting (everything else from documents tools but does)
b) voting is a little strange : when course is created, there is already 1 vote with 0 stars, if i add 5 stars, got 2.5/5 .
this is not intentional isn't it ?

(
other observations and thoughts:
- still can not accept 'connections last month'. There is montly usage of corse/course tools we are talking about ... ,
- visits counting is a bit strange : page refresh increments visits counter, changing between courses and coming back inside
the same logged-in session/per user increments visits counter - can be better to limit the increments by 1 for every logged-in session ?
- counting works for the teachers and the students; values can be missinterpreted when teachers activity is very high and an activity of the students is very low, anyway, high 'scores' are desirable.

but all these are things i can live with ....

)

#21

Updated by Yannick Warnier over 7 years ago

  • Target version changed from 1.9 Stable to 1.9 Beta
#22

Updated by Michela Mosquera over 7 years ago

  • Status changed from Needs more info to Feature implemented
  • Assignee set to Julio Montoya
  • % Done changed from 70 to 100

Marko, you make very good points but in order for us to be able to structure the work and assign priorities, we would need you to log it into different tasks (the effect of extending requirements is very bad on developers - it generates depression :-) ).

Please make sure you log this into other tasks so we can take them into account and focus on releasing a 1.9 soon.

For the rest of the task, it is working fine now. Thanks to you guys for your good work on this.

Also available in: Atom PDF