That was a crash and burn. 
Using the Discourse AI - AI Bot
Prompt
Create an image for this SQL ``` WITH pairs AS ( SELECT p.user_id liked, pa.user_id liker FROM post_actions pa LEFT JOIN posts p ON p.id = pa.post_id WHERE post_action_type_id = 2 ) SELECT liker liker_user_id, liked liked_user_id, count(*) FROM pairs GROUP BY liked, liker ORDER BY count DESC LIMIT :limit ```
Completion
FYI
Yesterday out of curiosity did a Google search similar to this one
sql generated by ai from English
to find related information on generating SQL using AI from English worded questions. I was hoping for a list of research papers included in the list but the list was primarily a list of applications. One pattern I quickly saw for many applications listed but not all after looking at a handful of demos on such sites was that they were taking their existing tool that would allow a user to select tables and fields to construct an SQL statement similar to the way the GUI SQL builder worked in Microsoft Access as a starting point, in other words one did not start with an English question but had to select the tables and sometimes fields first before then entering the English question. While I would not consider that cheating, it does mean that to use such AI a user has to have some knowledge of SQL, specifically the meaning of a table and field for the tool to work.
I read your deleted post and agree that too is one of the ideas I think has great potential.
However I think that it needs more. A recent paper that I read, again not authoritative, but that goes into more details along those lines is
āChatLogo: A Large Language Model-Driven Hybrid Natural-Programming Language Interface for Agent-based Modeling and Programmingā by John Chen and Uri Wilensky (pdf)
Some of the key takeaways for me were
- Support novice programmers to ātalk to computersā in a mix of programming and natural languages.
- Provide a more friendly interface for learners with no or little computer science backgrounds to creatively express themselves by programming computers.
- Instead of right away writing code and giving instructions, ChatLogo attempts to clarify the learnersā needs and intention.
- Instead of sending large chunks of code directly to the learner, it attempts to co-develop the NetLogo code. The learner is free to edit the code: either in NetLogo, or in natural languages through the āAskā feature.
- Instead of overclaim the correctness of the code, it admits the possibility of making mistakes, and co-works with the learner to address the potential issues.
I could not find any source code or more details on how Chat was layered on top of or interfaced with NetLogo but the ideas alone are of value.
This again reminds me of how autopilots works in planes and the book I noted earlier. (ref) When I note autopilot do not think of a binary switch, think of all of the knobs and switches that give information to the autopilot and let the autopilot know what to do and what not to do.
While it seems that many other tools such as this like to use the word copilot, e.g. a Google search llm copilot source code, I tend to disagree with that analogy but will have to live with it as that is how one finds related ideas.
I deleted it because I thought Iād found a better way of dealing with the issue - why type out all those tables when ChatGPT could just be asked to parse a file thatās hosted on the internet that contains detailed information about the entire database? It turns out there are technical problems with that approach though. So Iām back to what I suggested in the deleted post. In any case, descriptions of tables, example queries, etc, are going to have to be created. Itās just a question of how to get that information to the LLM. Hereās where things are at with GPT-4:
Yes, the approach youāve outlined makes sense and is a feasible way to use my capabilities to assist in generating SQL queries based on natural language questions.
Hereās a breakdown of the steps:
- Question Interpretation: The user poses a question in natural language, which I interpret to understand the information being requested.
- Table Identification: Based on the question and the list of all tables in the Discourse database, I identify the tables that are most likely to be relevant to the query.
- Detailed Table Information: Discourse sends me detailed descriptions of the relevant tables, including all columns and example queries.
- Query Generation: Using the detailed table information, I generate an SQL query that answers the userās question.
This approach leverages my ability to understand natural language and map it to structured data queries. It also makes good use of the detailed table information provided by Discourse.
As for alternate approaches, one possibility could be to train a model specifically on Discourseās database schema and common query patterns. This would likely involve a significant amount of work and may not be feasible depending on the resources available. However, it could potentially result in more accurate and efficient query generation.
Another approach could be to develop a more interactive dialogue where I ask clarifying questions when the userās question is ambiguous or when multiple queries could potentially answer the question. This would make the process more interactive and could potentially lead to more accurate queries.
For the alternate approaches suggested in the last 2 paragraphs, Iām ignoring the first one, but if what Iām attempting doesnāt lead to reliable results, it might be something that the Discourse team could look into.
I asked GPT-4 about the second alternate approach. Itās fairly confident that given a question that couldnāt be answered via the provided table structure, it would be able to prompt the user to try to re-frame their question.
So Iām back to writing descriptions of all tables in the Discourse database. I now know what the allowed_pm_users table is used for. The ar_internal_metadata table is a bit of a head scratcher, but I think it can be ignored for now.
That is my favorite option so far, albeit a very hard one to implement. Thus, you guessed it, another paper. Again this is not an authoritative paper as there are many similar approaches.
āDiagGPT: An LLM-based Chatbot with Automatic Topic Management for Task-Oriented Dialogueā by Lang Cao (pdf)
Just replace the medical information with PostgreSQL information as needed. The nice part is the paper gives the prompts and suggest which tool with agents to use.
Since Task Oriented Dialogue is what appears is needed.
Google search: task oriented dialogue
Search result includes: Papers With Code - Task-Oriented Dialogue Systems
The leader board entry is T5-3b(UnifiedSKG) which includes a link to paper
āUnifiedSKG: Unifying and Multi-Tasking Structured Knowledge Grounding with Text-to-Text Language Modelsā by Tianbao Xie, Chen Henry Wu, Peng Shi, Ruiqi Zhong, Torsten Scholak, Michihiro Yasunaga, Chien-Sheng Wu, Ming Zhong, Pengcheng Yin, Sida I. Wang, Victor Zhong, Bailin Wang, Chengzu Li, Connor Boyle, Ansong Ni, Ziyu Yao, Dragomir Radev, Caiming Xiong, Lingpeng Kong, Rui Zhang, Noah A. Smith, Luke Zettlemoyer and Tao Yu (pdf)
Notice this
EDIT
From https://python.langchain.com/
Construct an SQL agent from an LLM and tools. (ref)
It list a few at the bottom of the page, this one should be looked at
Use case
Enterprise data is often stored in SQL databases.
LLMs make it possible to interact with SQL databases using natural language.
LangChain offers SQL Chains and Agents to build and run SQL queries based on natural language prompts.
These are compatible with any SQL dialect supported by SQLAlchemy (e.g., MySQL, PostgreSQL, Oracle SQL, Databricks, SQLite).
They enable use cases such as:
- Generating queries that will be run based on natural language questions
- Creating chatbots that can answer questions based on database data
- Building custom dashboards based on insights a user wants to analyze
EDIT (08/23/2023)
Spider is a large-scale complex and cross-domain semantic parsing and text-to-SQL dataset annotated by 11 Yale students. The goal of the Spider challenge is to develop natural language interfaces to cross-domain databases. It consists of 10,181 questions and 5,693 unique complex SQL queries on 200 databases with multiple tables covering 138 different domains. In Spider 1.0, different complex SQL queries and databases appear in train and test sets. To do well on it, systems must generalize well to not only new SQL queries but also new database schemas .
EDIT (0824/2023)
Dataherald is a natural language-to-SQL engine built for enterprise-level question answering over structured data. It allows you to set up an API from your database that can answer questions in plain English.
As I often tell others, for some things in life you just have to wait and someone will do the work for you for free.
Iām still having results Iām perfectly content with. Hereās another example. Gave me what I wanted, I popped in a WHERE clause to check for a topic ID myself afterwards, but that was it. I probably could have given it that information ahead of time and it would have done that too, though:
We now have the SQL Helper persona as part of the AI Bot module which is tailored for these kinds of requests and can be really useful for creating data explorer queries. 
(Though it does pay to double-check them 
)
Is there a topic where examples of the SQL Helper AI bot being used can be posted?
If not, it might be nice to create such a topic and then add a link to the topic here as others may be looking for examples and find this topic first.
I think if you come up with one worth sharing with the community you can pop it in a new topic in data & reporting. Thereās a new 'share conversation` button too that could be useful for this - Share Your AI Bot Conversations!
JammyDodger:
Hey Bert
Can you write me a query that will find all the users in the plugin_authors group?
gpt4_bot:
groups, group_users, users
Now that we have the schema for the relevant tables, we can write a query to find all the users in the āplugin-authorsā group. Based on the schema, weāll need to join the group_users table with the groups and users tables to get the usernames of the members of the āplugin-authorsā group.
Hereās the query to find all the users in the āplugin-authorsā group:
SELECT u.id AS user_id, u.username
FROM users u
JOIN group_users gu ON gu.user_id = u.id
JOIN groups g ON g.id = gu.group_id
WHERE g.name = 'plugin_authors'
This query selects the user ID and username from the users table by joining 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 include only those users who are part of the group with the name āplugin-authorsā.
This topic was automatically closed after 43 hours. New replies are no longer allowed.