Day Eleven kicked off a day of very little actual Ruby. In the morning we worked on a debugging project where we were handed some broken ruby code and the expectations for how it was supposed to work and had to fix it. I really enjoyed that project, as much as I like building new things its also fun to fix broken stuff. After that though, the rest of the day was spent on SQL.

Something I like about Flatiron is that we learn everything from the ground up. The magic of a lot of things like Rails and Active Record will be understood by us as we will have learned to do everything without them. That is exactly how I want to learn to program. That said, it’s also a pain in the butt. Writing SQL queries isn’t conceptually difficult, though the new syntax creates some conflicts, but writing it in sublime as a .sql file and smashing it into a DB in BASH is a more tedious process than I think is probably necessary. I’m about ready for Active Record!

Most of the classes issues seemed to stem from Queries and Joins. Most of the organizing of the databases came pretty naturally to most people I talked to, but there is a lot of syntax with queries/joins that can be pretty confusing. I’m going to post a snippit of my code and try and explain it…

1
2
3
4
5
6
7
8
9

SELECT project.title, SUM(pledge.amount) AS 'total_pledge_amount'
FROM
project
INNER JOIN
pledge
ON
project.id = pledge.project_id
GROUP BY
project.title;

So, this query has several parts which I will try and explain correctly one at a time.

The first is SELECT. This is the specific data we want this query to look at. In this case its the ‘title’ column from the ‘project’ table and the sum of the ‘amount’ column from the ‘pledge’ table. The AS key just renames the result of the SUM function to ‘total_pledge_amount’ and could be removed and the query would still function. Next is FROM, we are joining from the project table, using an INNER JOIN with the pledge table. I reversed the places of project and pledge and got the same results, so I’m not yet sure where it would be important to specify one table in front of another. After that is ON, which is where the JOIN will link the two tables based on a mutual key. In this case its the primary key of project (project.id) and the foreign key in pledge that corresponds with the project primary key (pledge.project_id). Lastly is GROUP BY for which i put ‘project.title’ this orders the query results alphabetically by the ‘title’ column in the ‘project’ table. The result is a list of each project.title and the total amount of pledges that match each projects key.