Skip to content

Instantly share code, notes, and snippets.

@joemsak

joemsak/Gemfile

Last active January 26, 2024 18:59
Show Gist options
  • Save joemsak/aa3954dfb157235326216f4f5fe027f0 to your computer and use it in GitHub Desktop.
Save joemsak/aa3954dfb157235326216f4f5fe027f0 to your computer and use it in GitHub Desktop.
Download ZIP
Learn SQL the Hard Way Ex 6 in Ruby
Raw
ex6.rb
require "sqlite3"
require "active_model"
require "active_support"
require "active_support/core_ext/string"
require "pry"
DB = SQLite3::Database.new("./ex6.db")
class Pet
include ActiveModel::Serializers::JSON
attr_accessor :id, :name, :breed, :age, :dead
def attributes=(hash)
hash.each do |key, value|
send("#{key}=", value)
end
end
def attributes
instance_values
end
end
class Person
include ActiveModel::Serializers::JSON
def self.find_by(attributes = {})
person = new
row = DB.execute(%(
SELECT * FROM person
WHERE #{attributes.map { |k, v| "#{k} = '#{v}'"}.join(" AND ")}
LIMIT 1
).squish).last
person.from_json({
id: row[0],
first_name: row[1],
last_name: row[2],
age: row[3]
}.to_json)
end
attr_accessor :id, :first_name, :last_name, :age
def attributes=(hash)
hash.each do |key, value|
send("#{key}=", value)
end
end
def attributes
instance_values
end
def pets
@pets ||= DB.execute(%(
SELECT pet.id, pet.name, pet.breed, pet.age, pet.dead
FROM pet, person_pet, person
WHERE pet.id = person_pet.pet_id
AND person.id = person_pet.person_id
AND person.id = #{id}
).squish).map do |row|
pet = Pet.new
pet.from_json({
id: row[0],
name: row[1],
breed: row[2],
age: row[3],
dead: row[4]
}.to_json)
end
end
end
zed = Person.find_by(first_name: 'Zed')
joe = Person.find_by(first_name: 'Joe')
puts zed.as_json
zed.pets.each { |pet| puts pet.as_json }
puts "-" * 45
puts joe.as_json
joe.pets.each { |pet| puts pet.as_json }
Raw
ex6.sql
CREATE TABLE person (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER
);
CREATE TABLE pet (
id INTEGER PRIMARY KEY,
name TEXT,
breed TEXT,
age INTEGER,
dead INTEGER
);
CREATE TABLE person_pet (
person_id INTEGER,
pet_id INTEGER
);
INSERT INTO pet (id, name, breed, age, dead)
VALUES (0, 'Fluffy', 'Unicorn', 1000, 0);
INSERT INTO pet VALUES (1, 'Gigantor', 'Robot', 1, 1);
INSERT INTO person (id, first_name, last_name, age)
VALUES (1, 'Joe', 'Sak', 40);
INSERT INTO pet (id, name, breed, age, dead)
VALUES (2, 'Fuji', 'Calico', 6, 0);
INSERT INTO pet (id, name, breed, age, dead)
VALUES (3, 'Bizen', 'Gray tabby', 9, 0);
INSERT INTO pet (id, name, breed, age, dead)
VALUES (4, 'Michu', 'Orange tabby', 3, 0);
INSERT INTO person_pet (person_id, pet_id) VALUES (0, 0);
INSERT INTO person_pet VALUES (0, 1);
INSERT INTO person_pet (person_id, pet_id) VALUES (1, 2);
INSERT INTO person_pet (person_id, pet_id) VALUES (1, 3);
INSERT INTO person_pet (person_id, pet_id) VALUES (1, 4);
Raw
Gemfile
source "https://rubygems.org"
ruby "3.3.0"
gem "activemodel"
gem "activesupport"
gem "sqlite3"
gem "pry"
Raw
shell
sqlite3 ex6.db < ex6.sql
ruby ex6.rb
{"id"=>0, "first_name"=>"Zed", "last_name"=>"Shaw", "age"=>37}
{"id"=>0, "name"=>"Fluffy", "breed"=>"Unicorn", "age"=>1000, "dead"=>0}
{"id"=>1, "name"=>"Gigantor", "breed"=>"Robot", "age"=>1, "dead"=>1}
---------------------------------------------
{"id"=>1, "first_name"=>"Joe", "last_name"=>"Sak", "age"=>40}
{"id"=>2, "name"=>"Fuji", "breed"=>"Calico", "age"=>6, "dead"=>0}
{"id"=>3, "name"=>"Bizen", "breed"=>"Gray tabby", "age"=>9, "dead"=>0}
{"id"=>4, "name"=>"Michu", "breed"=>"Orange tabby", "age"=>3, "dead"=>0}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment