Data explorer query - new topics for user by tag/category

Data & reporting
1

I’m trying to build a “recommendation engine” for my users via the Discourse API that lists all topics a specific user hasn’t read yet by tag/category. I browsed through the various existing queries, but I have no clue where to start. Can you please help? Thanks!

2 Likes
2

@simon Could you please add the tag here?

1 Like
3

@Krischan,

Have you checked the table post_timings?

Something like this will list the topics in a category that a user has never entered or read any post. It is not exactly what you want but a starting point.

-- [params]
-- user_id :user
-- category_id :category

SELECT t.id, t.title
FROM
  topics AS t
WHERE
    category_id = :category
AND user_id != :user
AND last_post_user_id != :user
AND deleted_at IS NULL
AND NOT EXISTS(SELECT 1 FROM post_timings AS p WHERE p.topic_id = t.id AND p.user_id = :user)

Note that in your “recommendation engine” you will probably have to check if the user can see the topic before recommending it.

2 Likes