I have a mysql table called bookings. A booking can be referenced by any number of rows in the table future_payments. The future_payments table has a date_due field and a has_been_paid field. I would like to create a view which shows one future_payment for each booking, where the future_payment has has_been_paid=0 and is the one with the most recent date which is later than now. How do I write this?
I write some pretty complex queries, but it came up with something using constructs I'd not used before... I tweaked it to my specific circumstance and it worked first time!
Here what it immediately came back with:
CREATE VIEW booking_latest_unpaid_payment AS
SELECT b.*, fp.*
FROM bookings b
LEFT JOIN (
SELECT fp.booking_id, fp.date_due, fp.has_been_paid
FROM future_payments fp
WHERE fp.has_been_paid = 0
AND fp.date_due > NOW()
GROUP BY fp.booking_id
HAVING fp.date_due = MIN(fp.date_due)
) fp ON b.id = fp.booking_id;
I can’t remember the exact query, but my geek out moment was when I fed it a less-common query to optimize a while back. I expected it to rearrange it or swap some joins with CTE’s for performance, etc, but instead it went off the deep-end and actually leveraged some obscure limitation in Postgres and exploited it in reverse to compress the whole query in half. Taking documented DBMS limitations and exploiting them to your advantage is… something else.
Definitely agree with you.
I m doing a work that’s mainly done with complex queries and all of them are done through chatGPT
Also made a python script running on a docker container receiving parameters from API server connect component and working very well.
Now I’m just trying to create a Wappler component done by chatGPT just for curiosity.
That s interesting for me.
What kind of instruction you gave ChatGPT to make it? I put the community page as instruction. At the moment it works but do not pass the value to the next step
Yep, definitely where ChatGPT scores highly. I frequently paste my table(s) structure and then state what I want the query to do and it gives me the SQL with all the correct tables and fields because I'd already pasted the structure. And it usually gets it right first time.
It's also really great for reverse engineering someone else's Stored Procedures. I work on projects with databases that I didn't design. I think I might know what one of their stored procedures does, but then I can just copy and paste the entire procedure into chatgpt and ask "what does this do?" oh wow!!! Uh, that's really useful! I immediately signed up for the subscription after that. This is really a useful tool.
Also really useful for create tables in database with just standard info etc. I ask chatgpt to create sql query for populating the names of the states. Typical thing I would need in putting a DB together. But you have to create the table and then get the data to put in it etc. Chatpgt will just create the sql query for me and I run it from the sql manager and bam! Stuff like that saves loads of time. If you need a table with all the countries in the world? No problem for chatgpt. Super easy and very useful. I am a recent fan!
Some do certain things better than others, they often leapfrog each other as they are updated. Not really a definitive answer to that.
I use copilot mainly as its on my desktop
GPT 4o (not mini) has been spot on, less than 5% slip rate on hallucination. Just feed it your pertinent DDL (table definitions) first, followed by the queries and you’ll have great results. Always follow up with a secondary prompt to loop back around and check for edge cases like NULLS in FK columns, input params with incorrect data types, etc to ensure operability with poor input, and have it put in place all the safeties in the resultant DML.
I use both Claude and ChatGPT, often if ChatGPT is a little wide of the mark or I'm just not solving what I need to, Claude will, but I do tend to use ChatGPT mostly.
I use it to:
Generate/Improve SQL Queries
Write Custom Wappler Extensions
Custom code (mostly JS) for our app.
Wappler is really missing a beat by not implementing AI.
For now you can use the terminal within Wappler to query LLM's and output their response using ShellGPT
ShellGPT
A command-line productivity tool powered by AI large language models (LLM). This command-line tool offers streamlined generation of shell commands, code snippets, documentation, eliminating the need for external resources (like Google search). Supports Linux, macOS, Windows and compatible with all major Shells like PowerShell, CMD, Bash, Zsh, etc.
It is Open Source so maybe could be integrated in to Wappler?
