Forum: Precalculated statistics to improve performance of thread overview

1 Initial Problem

After implementing some prototypes and benchmarks this feature is not necessary (and has not a big impace on the performance).

In ILIAS forums a privileged user can enable the 'Approve Postings' feature. If enabled, moderators have to approve new postings before they are published. This implies that statistics displayed in the thread overview table vary depending on the user's context.

Let's suppose the following example: There are 10 postings in thread B of forum A, but only 7 of 10 have been already approved by moderators. Let's further suppose the ID of the firt posting is 20, and the ID of the last posting is 29. Each posting has different author.

Statistics for moderators:

• Number of postings: 10
• ID of the last posting: 29

• Number of postings: 7
• ID of the last posting: 26

• Number of postings: 8
• ID of the last posting: 27

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

SELECT
(
    CASE
        WHEN
            COUNT(DISTINCT(notification_id)) > 0 
        THEN
            1 
        ELSE
            0 
    END
) usr_notification_is_enabled, MAX(pos_date) post_date, COUNT(DISTINCT(pos_pk)) num_posts, COUNT(DISTINCT(pos_pk)) - COUNT(DISTINCT(postread.post_id)) num_unread_posts, 
    (
        SELECT
            COUNT(DISTINCT(ipos.pos_pk)) 
        FROM
            frm_posts ipos 
            LEFT JOIN
                frm_user_read iread 
                ON iread.post_id = ipos.pos_pk 
                AND iread.usr_id = 6 
            LEFT JOIN
                frm_thread_access iacc 
                ON (iacc.thread_id = ipos.pos_thr_fk 
                AND iacc.usr_id = 6) 
        WHERE
            ipos.pos_thr_fk = thr_pk 
            AND 
            (
                ipos.pos_update > iacc.access_old_ts 
                OR 
                (
                    iacc.access_old IS NULL 
                    AND 
                    (
                        ipos.pos_update > '2018-10-19 10:11:57'
                    )
                )
            )
            AND ipos.pos_author_id != 6 
            AND iread.usr_id IS NULL 
            AND 
            (
                ipos.pos_status = 1 
                OR ipos.pos_author_id = 6
            )
    )
    num_new_posts,
    thr_pk,
    thr_top_fk,
    thr_subject,
    thr_author_id,
    thr_display_user_id,
    thr_usr_alias,
    thr_num_posts,
    thr_last_post,
    thr_date,
    thr_update,
    visits,
    frm_threads.import_name,
    is_sticky,
    is_closed 
FROM
    frm_threads 
    LEFT JOIN
        frm_notification 
        ON frm_notification.thread_id = thr_pk 
        AND frm_notification.user_id = 6 
    LEFT JOIN
        frm_posts 
        ON pos_thr_fk = thr_pk 
        AND 
        (
            pos_status = 1 
            OR pos_author_id = 6
        )
    LEFT JOIN
        frm_user_read postread 
        ON postread.post_id = pos_pk 
        AND postread.usr_id = 6 
WHERE
    thr_top_fk = 27 
GROUP BY
    thr_pk,
    thr_top_fk,
    thr_subject,
    thr_author_id,
    thr_display_user_id,
    thr_usr_alias,
    thr_num_posts,
    thr_last_post,
    thr_date,
    thr_update,
    visits,
    frm_threads.import_name,
    is_sticky,
    is_closed 
HAVING
    num_posts > 0 
ORDER BY
    is_sticky DESC,
    post_date DESC,
    thr_date DESC

2 Conceptual Summary

Therefore we suggest the following changes:

• The number of postings will not be calculated via SQL, a precalculated value for moderators (all postings at all) and non-moderators (all active postings, if the postings have to be approved by moderators) has to be stored in table 'frm_threads'.
• The id and date of the last posting will not be calculated via SQL, precalculated values for moderators (last posting at all) and non-moderators (last active postings, if the postings have to be approved by moderators) have to be stored in table 'frm_threads'.

• If postings have to be approved by moderators, the author of a posting (if not being a moderator) will not his own 'pending' posting anymore.
• The precalculated values have to be refreshed on:
  • Creation of new postings
  • Deletion of postings
  • Enabling/Disabling the 'Approval by moderators' feature
  • Approvel by moderators

Furthermore we would like to get rid of the additional LEFT JOIN to table frm_notification. This is currently used to determine if the user has enabled thread-based notification for a result row. We suggest to determine this status by a separate query by using an IN(id1, id2, ...) statement for all thread ids determined for the current table page.

The information is not used for ordering, so we do not necessarily need to query this information in the base SQL query.

3 User Interface Modifications

3.1 List of Affected Views

• Screen-ID: frm/forums_threads/

3.2 User Interface Details

No changes, no details.

3.3 New User Interface Concepts

No changes, no new concepts.

4 Technical Information

4.1 Migration

We MUST provide a new database update step to calculcate ...

• The number of all postings for each thread in the ILIAS database
• The number of all active postings for each thread in the ILIAS database
• The ID and date of the last posting for each thread in the ILIAS database
• The ID and date of the last active posting for each thread in the ILIAS database

5 Contact

• Author of the Request: Jansen, Michael [mjansen]
• Maintainer: Jansen, Michael [mjansen]
• Implementation of the feature is done by: {The maintainer must add the name of the implementing developer.}

6 Funding

If you are interest in funding this feature, please add your name and institution to this list.

• …

7 Discussion

8 Implementation

{The maintainer has to give a description of the final implementation and add screenshots if possible.}

Test Cases

Test cases completed at {date} by {user}

• {Test case number linked to Testrail} : {test case title}

Approval

Approved at {date} by {user}.