Learning (Articles)

The Articles feature allows care teams to build a library of content that can be delivered to patients. This content can be uploaded to Carium as an asset or the be represented by a link to external content using a URL. The content typically consists of PDFs, videos, and websites.

The data that describes articles in the system can be divided into two main categories: data that describes the library of content, and data that describes the assignment and interaction with the content.

In the diagram above, the caredb_article_articleinfo and caredb_article_content tables represent the library of content. While the caredb_article_individualarticlecontent table tracks assignment of articles to patients and the caredb_articleactivity table tracks a patient's engagement with the article.

tip

The caredb_core_patient.id is equivalent to the identity_core_individual.id.

Examples

List all articles present in the system

SELECT
    ai.name AS article_name,
    ai.title AS article_title,
    ai.description AS article_description
FROM caredb_article_articleinfo AS ai
WHERE ai.is_active = true
LIMIT 50

List the top ten articles by number of times read

SELECT
    ai.name AS article_name,
    ai.title AS article_title,
    ai.description AS article_description,
    SUM(ac.views) AS views
FROM caredb_article_articleinfo AS ai
JOIN caredb_article_articlecontent AS ac
    ON ac.article_info_id = ai.id
WHERE ai.is_active = true
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 10

In this case, you can use SUM() to add up the total number of views. This is because each articleinfo can have multiple versions of articlecontent. The sum represents total number of views for all versions of the content that have been uploaded to represent a single article.

List the top ten articles by unique user reads

SELECT
    ai.name AS article_name,
    ai.title AS article_title,
    ai.description AS article_description,
    COUNT(DISTINCT aa.individual_id) AS views
FROM caredb_article_articleinfo AS ai
JOIN caredb_article_articleactivity as aa
    ON aa.article_info_id = ai.id
WHERE ai.is_active = true
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 10

In contrast to the previous example which used the caredb_article_articlecontent.views column, this query utilizes the caredb_article_articleactivity table which represents every view of the article content. Instead of summing views, this query uses COUNT DISTINCT for the individual_id column.

Examples​

Examples