What Happened When I Used ChatGPT for Code

I struggle with writing recursive functions, it’s a weakness of mine. I’ve been trying to write a function in Ruby that returns all of the data from a Postgres table. The parent_id column references the id column. If parent_id is null then the record is a parent. I’m sure this will need some massaging but I made a couple of attempts to get ChatGPT to write this for me.

The primary request was this:

create a recursive database schema with the following fields: id, name, parent_id. parent_id will be null if the row is a parent row.

And here’s the output:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  parent_id INTEGER REFERENCES categories(id)
);

The AI will explain itself as well. I did not specify a name, however, a category tree is an excellent and typical example of a use case for a nested or recursive database table, this is likely a common use case in OpenAI’s training data:

In this schema, each row represents a category with an id, name, and a parent_id. The id field is a unique identifier for each category, and the name field stores the name of the category.

The parent_id field is used to create a hierarchy between categories. If a row represents a parent category, then its parent_id field will be NULL. If a row represents a child category, then its parent_id field will reference the id of its parent category.

With this schema, you can recursively traverse the hierarchy of categories by following the parent_id fields until you reach the root of the tree (i.e., a row with a NULL parent_id).

Here’s where things get tricky, I want said data extracted in a nested JSON format. I asked two ways, one was continuing the conversation and another was a fresh conversation:

write ruby code that selects all data from this table.  Loop through the data and return all of the data in nested JSON format

require 'json'
require 'pg'

conn = PG.connect(dbname: 'your_database_name', user: 'your_username', password: 'your_password')

# Select all data from the my_table table
result = conn.exec("SELECT * FROM my_table")

# Convert the data to a nested hash
data = {}
result.each do |row|
  # Create a new hash for the row with its name and ID
  hash = { 'name' => row['name'], 'id' => row['id'] }

  # If the row has a parent ID, add it to its parent's children array
  parent_id = row['parent_id']
  if parent_id
    parent_hash = data[parent_id.to_i]
    parent_hash['children'] ||= []
    parent_hash['children'] << hash
  end

  # Add the hash to the data hash, keyed by its ID
  data[row['id'].to_i] = hash
end

# Find the root nodes (those with a null parent ID)
root_nodes = data.values.select { |row| !row['id'].nil? && row['parent_id'].nil? }

# Convert the root nodes to JSON, including their children
json_data = root_nodes.to_json(include: { children: { include: :children } })

puts json_data

The code first selects all data from the my_table table and then iterates over each row to build a nested hash representing the hierarchy. Each row is represented by a hash with its name and id, and if it has a parent ID, it is added to its parent's children array. The data hash keeps track of all the rows, keyed by their ID.

After building the nested hash, the code finds the root nodes (those with a null parent ID) and converts them to JSON using the to_json method. The include option is used to include each node's children array and their children's children arrays recursively. The resulting JSON is printed to the console using puts.

write ruby code that selects all data from a postgres table that has the following fields: id, name, parent_id.  Loop through the data and return all of the data in nested JSON format

require 'pg'
require 'json'

# Connect to the PostgreSQL database
conn = PG.connect(dbname: 'your_database_name', user: 'your_username', password: 'your_password')

# Define a recursive function to retrieve all nested data
def get_nested_data(rows, parent_id = nil)
  # Initialize an empty hash to hold the nested data
  nested_data = {}

  # Filter the rows by parent_id
  children = rows.select { |row| row['parent_id'] == parent_id }

  # Loop through the children and recursively call get_nested_data to retrieve their nested data
  children.each do |child|
    nested_children = get_nested_data(rows, child['id'])
    # Add the child's nested data to the hash
    nested_data[child['name']] = nested_children unless nested_children.empty?
  end

  # Return the nested data
  nested_data
end

# Select all data from the table and store it in a variable
result = conn.exec('SELECT * FROM your_table_name')

# Call the get_nested_data function to retrieve the nested data
nested_data = get_nested_data(result)

# Convert the nested data to JSON format and print it
puts JSON.pretty_generate(nested_data)

# Close the database connection
conn.close

In this example, we use the pg gem to connect to the PostgreSQL database and retrieve all data from a table with the specified fields. We then define a recursive function called get_nested_data that loops through the data and retrieves the nested data in a hash format.

We call the get_nested_data function with the retrieved data and print the resulting nested JSON data using the JSON.pretty_generate method. Finally, we close the database connection.

Note: Make sure to replace your_database_name, your_username, your_password, and your_table_name with the appropriate values for your PostgreSQL database and table.

I’m just barely getting started with this technology, but I’m blown away at how fast it has evolved over the last couple of years.

Two distinct examples of roughly the same code. This is going to be an extremely useful technology, however, I would never blindly trust any code this generates, nor would I expect it to be correct. Anyone who uses this will still need to know how to read the output and determine if it satisfies the conditions. It appears to do what I want, but I’m using ActiveRecord, so we’ll request that:

write ruby code that selects all data from a postgres table that has the following fields: id, name, parent_id.  Loop through the data and return all of the data in nested JSON format.  Use ruby active record for the connection.

require 'active_record'

# Establish a connection to the PostgreSQL database
ActiveRecord::Base.establish_connection(
  adapter: 'postgresql',
  host: 'localhost',
  database: 'my_database',
  username: 'my_username',
  password: 'my_password'
)

# Define a model class to represent the my_table table
class MyTable < ActiveRecord::Base
end

# Retrieve all rows from the my_table table
rows = MyTable.all

# Define a recursive function to generate nested JSON
def generate_nested_json(rows, parent_id = nil)
  # Filter the rows by the given parent_id
  filtered_rows = rows.select { |row| row.parent_id == parent_id }
  
  # If there are no rows with the given parent_id, return nil
  return nil if filtered_rows.empty?
  
  # Map each filtered row to a hash containing its id, name, and nested children
  filtered_rows.map do |row|
    {
      id: row.id,
      name: row.name,
      children: generate_nested_json(rows, row.id)
    }
  end
end

# Generate nested JSON from the retrieved rows
nested_json = generate_nested_json(rows)

# Print the nested JSON as a string
puts nested_json.to_json

Still, to the haters: How many times have you written code that has worked 100% of the time on the first run?