SQL is a great tool, but for us Rubyists, it can sometimes be hard to grasp the concepts of querying a database. I’ve decided to re-write a couple of SQL queries in Ruby to remind myself of how the rows in a table correspond to an Object and its attributes.

For this example, we’ll be modeling a crowdfunding website; we have pledges, projects, and users.

• a User has a name
• a Project has a title, category, and funding goal
• a Pledge has an amount and belongs to both a user and a project

If we were to imagine the database, each of these objects would have its own table.

• the ‘users’ table would have an ‘id’ and ‘name’ column
• the ‘projects’ table would have an ‘id,’ ‘title,’ ‘category’ and ‘funding goal’ column
• the ‘pledges’ table would have an ‘id’, ‘user_id’, and ‘project_id’ column

For our Ruby “queries” I’ve created an Object Class for User, Project, and Pledge with these same column headers as attributes for their respective classes.

Now we are ready to start our queries! I will be writing my Ruby methods so that their output is identical to the SQLite output we would get if we ran the equivalent query in SQL.

Select the title of all projects and the total amount they have raised

SQL:

1
2
3

SELECT projects.title, SUM(pledges.amount)
FROM projects JOIN pledges ON pledges.project_id = projects.id
GROUP BY projects.title

Ruby:

1
2
3
4
5
6
7
8
9

projects.collect do |project| ##GROUP BY
  raised = 0
  pledges.each do |pledge| ##JOIN
    if pledge.project_id == project.id ##ON
      raised += pledge.amount ##SUM
    end
  end
  [project.title, raised] ##SELECT
end

OUTPUT: [[“World’s Best Book”, 300], [“World’s Worst Book”, 700], [“Save The Whales”, 1100], [“Save The Rhinos”, 500], [“Save The Fish”, 1000], [“World’s Best Album”, 800], [“World’s Worst Album”, 600]]

Select the title of all projects that have met their funding goal

SQL:

1
2
3
4

SELECT projects.title
FROM projects JOIN pledges ON pledges.project_id = projects.id
GROUP BY projects.title
HAVING SUM(pledges.amount) >= projects.funding_goal

Ruby:

1
2
3
4
5
6
7
8
9
10
11

projects.collect do |project| ##GROUP BY
  raised = 0
  pledges.each do |pledge| ##JOIN
    if pledge.project_id == project.id ##ON
      raised += pledge.amount ##SUM
    end
  end
  if raised > project.funding_goal ##HAVING
    [project.title] ##SELECT
  end
end.compact

OUTPUT: [[“Save The Whales”], [“World’s Best Album”]]

Select the names of all users and the total amount they have pledged

SQL:

1
2
3
4

SELECT users.name, SUM(pledges.amount)
FROM users JOIN pledges ON pledges.user_id = users.id
GROUP BY users.name
ORDER BY SUM(pledges.amount)

Ruby:

1
2
3
4
5
6
7
8
9

users.collect do |user| ##GROUP BY
  pledged = 0
  pledges.each do |pledge| ##JOIN
    if pledge.user_id == user.id ##ON
      pledged += pledge.amount ##SUM
    end
  end
  [user.name, pledged] ##SELECT
end.sort_by{|name, total_pledged| total_pledged} ##ORDER BY

OUTPUT: [[“Matthew”, 500], [“Jonathan”, 700], [“Rebecca”, 900], [“Jordan”, 1100], [“Melissa”, 1800]]

Select the category name and the sum total of the pledge amounts of all the pledges in the book category

SQL:

1
2
3

SELECT projects.category, SUM(pledges.amount)
FROM projects JOIN pledges ON projects.id = pledges.project_id
WHERE projects.category = 'books'

Ruby:

1
2
3
4
5
6
7
8
9
10
11

total = 0
projects.each do |project|
  if project.category == "books" ##WHERE
    pledges.each do |pledge| ##JOIN
      if pledge.project_id == project.id ##ON
        total += pledge.amount ##SUM
      end
    end
  end
end
[["books", total]] ##SELECT

OUTPUT: [[“books”, 1800]]

Another, more sophisticated Object-oriented model would be one in which each object had an attribute that explained their relationship to the other. For example, each pledge instance would have a user attribute and a project attribute. Each user would have an array of pledges they have made, and each project would have an array of pledges that were made towards their funding goal.

I hope this post helped you see SQL queries in a new way!