I've been struggling with tasks that require me to write SQL queries for testing purposes. I have to create queries that return the same results as PHP functions. I thought this was a good moment to test the capabilities of ChatGPT. I prepared a simpler version of the function that I want to convert into plain SQL and asked it for help. Let's take a look at the results.
Example 1
function sumQuantity(array $offers): int
{
$groupsCount = [];
foreach ($offers as $offer) {
if ($offer->getStatus() !== OfferStatuses::STATUS_ACTIVE) {
continue;
}
if (isset($groupsCount[$offer->getGroup()->getId()])) {
continue;
}
if ($offer->getGroup()->getOldestOffer()) {
$offer = $offer->getGroup()->getOldestOffer();
}
$quantity = $offer->getGroup()->getQuantity();
$groupsCount[$offer->getGroup()->getId()] = $quantity;
}
return array_sum($groupsCount);
}
PostgreSQL tables:
- "offers", columns: id, group_id, company_status
- "groups", columns: id, oldest_offer_id, quantity
I need a sql query that returns the same result
I was surprised by the answer. It gave me a solution with a fancy explanation:
I thought - OK Chat, that was easy. Show me how you handle something harder, maybe with subquery.
Example 2
function getSumOfMaximalCounts($matches)
{
$matchesCount = 0;
foreach ($matches as $match) {
if (!$match->getMatches()) {
continue;
}
$maxCountFromGroups = 0;
foreach ($match->getMatches() as $matchItem) {
if ($matchItem['count'] > $maxCountFromGroups) {
$maxCountFromGroups = $matchItem['count'];
}
}
$matchesCount += $maxCountFromGroups;
}
return $matchesCount;
}
postreSQL table:
offer_matches:
- offer_id int
- matches json
I need a sql query that returns the same result
This time, I was also amazed:
I have handled all queries with this cooperation. Not all cases were generated without errors, but even with small corrections from myself, I saved a lot of time.
Conclusion
Even if not all answers were perfect, I'm very impressed with the capabilities of the OpenAI product. It should be noted that this is the previous version of model (3), but version 4 will be available soon. Another fact is that it is learning all the time, so the number of errors will diminish.
I will further experiment with this tool. I feel that the new era is here :)
Top comments (0)