# Discourse AI + Data Explorer? **URL:** https://meta.discourse.org/t/discourse-ai-data-explorer/274011 **Category:** Feature **Tags:** data-explorer, completed, ai, ai-bot **Created:** [August 4, 2023, 6:54pm UTC](https://meta.discourse.org/t/discourse-ai-data-explorer/274011 "2023-08-04T18:54:32Z") **Posts on this page:** 1 **Showing post:** 5
### Author: ![simon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simon/32/339122_2.png) [@simon](https://meta.discourse.org/u/simon) #### Post date: [August 6, 2023, 6:30am UTC](https://meta.discourse.org/t/discourse-ai-data-explorer/274011/5 "2023-08-06T06:30:22Z")
> [@jordan-violet](#): > > Has there been consideration for integrating AI into the [data explorer](https://meta.discourse.org/t/32566) area? 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](https://meta.discourse.org/t/32566?silent=true) 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: > [@EricGT](#): > > Care to share the prompt, the version of ChatGPT and the model used with ChatGPT. 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: > > ```plaintext > # == 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: > > ```sql > 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: > > ```plaintext > # == 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: > > ```sql > 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: > > ```sql > 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. --- _[View the full topic](https://meta.discourse.org/t/discourse-ai-data-explorer/274011)._