Discourse AI + Data Explorer?

feature
, , ,
5

Extending this idea a bit, allowing users to interact with applications using natural language is pretty much my dream as far as AI goes. With Discourse and the Data Explorer plugin, it could be possible to get results from prompts like “return a list of users who have expressed interest in moderation.” Or even better, “send an invitation to the community_moderation group to all users who have expressed an interest in moderation.”

Getting back to what’s possible now:

Testing with ChatGPT 3.0 and 3.5, I’ve gotten good results when asking it to return a PostgreSQL query and copying the structure of the tables I’m interested in into the prompt. For example:

Summary

Me: Return a postgresql query that lists all usernames for users who are in the “community_moderators” group for the following database structure:

# == Schema Information
#
# Table name: groups
#
#  id                                 :integer          not null, primary key
#  name                               :string           not null

# == Schema Information
#
# Table name: group_users
#
#  id                 :integer          not null, primary key
#  group_id           :integer          not null

# == Schema Information
#
# Table name: users
#
#  id                        :integer          not null, primary key
#  username                  :string(60)       not null

ChatGPT 3.5: Sure, here’s a PostgreSQL query that lists all usernames for users who are in the “community_moderators” group based on the given database structure:

SELECT u.username
FROM users u
JOIN group_users gu ON u.id = gu.user_id
JOIN groups g ON gu.group_id = g.id
WHERE g.name = 'community_moderators';

This query retrieves the usernames from the users table by joining it with the group_users table on the user ID and then joining with the groups table on the group ID. It filters the results to only include usernames for users who are in the “community_moderators” group.

Me: Here is the structure of the application’s topics and posts tables:

# == Schema Information
#
# Table name: topics
#
#  id                        :integer          not null, primary key
#  title                     :string           not null

# == Schema Information
#
# Table name: posts
#
#  id                      :integer          not null, primary key
#  user_id                 :integer
#  topic_id                :integer          not null
#  post_number             :integer          not null
#  raw                     :text             not null

Return a query that lists all topic ids, post ids, and usernames for all posts created by members of the “community_moderators” group that give some indication that the poster is interested in how to moderate polarizing discussions.

ChatGPT 3.5: To achieve this, you would need to join the posts , topics , users , and group_users tables based on the provided schema. Here’s a PostgreSQL query that retrieves the requested information:

SELECT t.id AS topic_id, p.id AS post_id, u.username
FROM posts p
JOIN topics t ON p.topic_id = t.id
JOIN users u ON p.user_id = u.id
JOIN group_users gu ON u.id = gu.user_id
JOIN groups g ON gu.group_id = g.id
WHERE g.name = 'community_moderators'
  AND (p.raw ILIKE '%moderate%' OR p.raw ILIKE '%polarizing%' OR p.raw ILIKE '%discussion%');

This query retrieves the topic_id, post_id, and username for all posts created by members of the “community_moderators” group that contain certain keywords indicating an interest in moderating polarizing discussions. The ILIKE operator is used for case-insensitive pattern matching in the raw column of the posts table.

Please note that this query assumes that the keywords you provided are indicative of the desired posts. You might need to adjust or expand the keywords based on your specific criteria.

Me: Write a query that returns a count of posts created in each weekly period since September 2022, created by members of the community_moderators group that indicate some interest in how to moderate polarizing discussions.

ChatGPT 3.5: To achieve this, you can use the following PostgreSQL query to return a count of posts created in each weekly period since September 2022, created by members of the “community_moderators” group that indicate an interest in how to moderate polarizing discussions:

SELECT
    DATE_TRUNC('week', p.created_at) AS week_start,
    COUNT(p.id) AS post_count
FROM
    posts p
JOIN
    users u ON p.user_id = u.id
JOIN
    group_users gu ON u.id = gu.user_id
JOIN
    groups g ON gu.group_id = g.id
WHERE
    g.name = 'community_moderators'
    AND (p.raw ILIKE '%moderate%'
         OR p.raw ILIKE '%polarizing%'
         OR p.raw ILIKE '%political%'
         OR p.raw ILIKE '%contentious%'
         OR p.raw ILIKE '%debate%'
         OR p.raw ILIKE '%controversial%'
         OR p.raw ILIKE '%discussion%')
    AND p.created_at >= '2022-09-01' -- Start date: September 2022
GROUP BY
    week_start
ORDER BY
    week_start;

This query calculates the count of posts created by members of the “community_moderators” group that contain the specified keywords in each weekly period since September 2022. It groups the results by week and orders them chronologically. Please make sure to adjust the column names and table relationships based on your actual database schema.

The results seem good to me and represent the types of queries that took me a fair amount of time to write in the past. I’m assuming it would be possible to train a model on the Discourse database structure so that details about the structure could be left out of the prompts.

4 Likes