- Read Tutorial
- Watch Guide Video
Great job in going through that section, you should now have a better idea on how to work with data in your application by leveraging models. You went through how to implement validations, how to work with concerns and went all the way through and even built out nested attributes.
That is a great job! I can promise you that not a ton of Rails developers know how to effectively use nested attributes especially in the right way, now you do. If you are unclear about any items, I definitely recommend for you to reference those videos and those guides whenever you need a refresher. The more times you watch them and the more times you practice writing that code, the more clear it's going to be in your mind.
In this deep dive, I want to take a look at a very important part of how Rails manages data and it's something that's a little bit hidden. Behind the scenes, every time a model is communicating with the database, it's using a language called sql. In this deep dive I want to take a deeper look at what sql does and how you can use it to manage data in your application and any application that uses the popular sql database.
Before I started filming I created a brand new rails app and this is going to be the example we use throughout this deep dive. It's not related to the portfolio app. This is going to specifically allow us to focus just on sql.
I am I'm going to open up the schema file, I created a few tables
- I have a authors table
- I have a books table
- I have a genre table
The way the relationships work is: an author has many books, any genre has many books (a book belongs to an author and to a genre).
If I open up Google Chrome, you can see I have a basic scaffold. I have a set of books (I have four books) they all have a author object, a genre object and sales figures.
Before we go into that, I want to give you a link to the official active record Query interface (link is at the bottom of this page). This is one of the best resources for learning more about how you can integrate sql and run database queries in rails so that associated with the show notes.
We have a lot to cover, one of the first things I want to start on is a refresher on relationships.
Right here, this doesn't look very nice. We have "The Force" which is the name of the book title and then we have this weird looking object and a genre object. I thought about fixing this before I even started, I didn't because I want you to see this, if you run a scaffold and you create a relationship you're going to probably see something that looks kind of like this.
I'm also going to push all of the items that we have worked on in this deep dive up to the show. So you can go and reference it yourself.
Switching back to the code,
you can see that we have authors that have a name, genre also has a name. Now this could be title or anything like that, it's just something arbitrary.
If I go to app, then views, books and the index action.
If I come right here you can see that I have an iteration, I have a table, and it's iterating over the collection of books. It's saying book title, book author, book genre and book sales along with the action items.
The reason we have an object, in the browser, it looks like this because we actually need to call the attribute name on author. By saying book author all Rail's knows is that there is an author object. We actually have to give that an attribute name. We have to say this is the attribute name out of that author. Let's say I did an inspect here, inspect is going to show all of the items related to author.
If I do inspect, you can see that inside of this author object I have a name, country, created at and updated at. There's a lot of information hidden inside of this object. That's the reason why it can't just show Luke or Vader. It is going to show the whole object, what would happen if you wanted to do something like show the book and also show the book's authors country.
First start with the name, hit refresh and look, now it's actually showing the data we want.
If we want to show the country, I can change that to author country. Come to the table and say author, I can say location.
Come up here and hit refresh.
Now you have the author, their name and the author's location all listed right here.
Moving on to genre, It's going to be identical to what we did with author. Genre only has one main attribute which is name. You can see that here it now has artist genre.
This is pretty cool, even though we are only making a database query on the book's table, we're actually able to pull in everything from the author and from the genre.
Next, we are going to get into sql. I wanted to start off by fixing something very specific and very practical. Now I want to show the underlying details behind that and to do that I'm going to jump into the rails console for our application.
We're going to get into some sequel queries then we're going to talk about some dynamic methods and we're going to run all kinds of database queries.
First you saw the results, at least on the screen, of what this book query looks like. If we open the code to the books controller.
All we're doing is calling the Book.all and I think this would be a pretty good place to start.
Copy this and paste it into the console
Book.all
you can see that this runs a database query, it brings back what's called an "active record relation" which is the type of object that ships with Rails active records.
This gives us an array of objects, the reason why I know it's an array is just like Ruby. I can do a
Book.all.count
it is going to print out a count of how many books there are.
That's a good way of knowing that we're dealing with a collection and not a single object.
I'm going to run Book.all again, I want you to look at the process. Sql is a query language, it allows us to do this right here, where you see all of this yellow. This is actually a sql query.
Rails is nice because it doesn't really make you work with Sql directly. We can have a nice education about how sql works simply based on what is shown here on the console.
This is also in the terminal, I have a tab open for the terminal. As you can see, all of these yellow spots are where there were sql queries occurring.
This is pure sql, this says "select authors from authors with authors ID is one." That's a pure sql statement and Rail's has the ability not only to call these methods but actually see what they do.
Here it says select books from books.
What we're saying here with the Asterix * is saying, bring me back all the books and all of their respective data.
If I run
Book.find_by_sql("SELECT.* FROM books")
You can see that this also works identically.
We have a method in Rails called "find_by_sql" where you can pass in pure sql code. It's not every day that you have to do this, this is accomplished from some great active record methods.
Saying
Book.all
is the exact same thing as saying
Book.find_by_sql("SELECT books.* FROM books")
it is a much more succinct way of saying it.
This is another reason why people love Rail's so much, they have so many great helper methods like this one. That's a way seeing how sql works from the inside out, you can see each of the queries it's running and then you can adjust it accordingly.
Next thing I want to talk about is dynamic query names. It's very helpful to do things like
Book.all
there are times where you may want to be more specific with what you're looking for.
An example of this would be that we have a book called "The Force" and say that we want run a database query to find just this book.
There is a cool way of doing that, I'll show you the uncool way first.
If I enter
Book.where(title: "The Force")
This is going to run a database query, it says "select books from books where the book's title is equal to "The Force."
Now this works (kind of), except I want to show you something a little bit tricky. Say that we wanted to do something like this "OK I want to go grab the book called "The Force" and then I want to grab the author for the book." When we talked about relations, whenever I call something that has a "belongs to" relationship, I can say something like this. However, this isn't going to work for a tricky reason, especially if you've never done this before.
If I hit return this is going to throw a nasty error, the issue says "undefined method author." So what is the problem? The problem is that when I run a
Book.where
and pass this in, it doesn't actually bring me back a single book instead it brings me back a collection.
Even though it only brought back one book, Rail's itself looks at it like a collection, it just is a collection of one. In order to make this work I'd have to say
Book.where(title: "The Force").first.author
That's going to just bring me back the first one in the collection and then bring me back the author.
If I run this it works
I can see that the first book's author is Luke. Now that is not really the best way of writing it. If you're running a query and you only want a single result it's not really great practice to write book.where and then bring it. If you know the title then you can do something like this and say Book.find_by_title and then pass in as a string "The force" and that will work perfectly. If I say .author, it brings back Luke.
Book.find_by_title("The Force") Book.find_by_title("The Force").author
So this is a little trick and I have done this a number of times. I think I've even done on screen casts where I will mistakenly forget that "where" brings back a collection and I'll try to call a method on it, like finding the author. But you have to know that "where" brings back a collection which means that you have to parse through that data.
On the other hand "find" these find methods anytime you see find underscore or even just find by itself that is going to bring back a single result. A good way of testing this is one of the first things whenever I see you there's a bug in a query I'll do is say
Book.where(title: "The Force").class
and you can see this is an ActiveReord_Relation. If I go up and say
Book.find_by_title("The Force").class
you can see this is a book object.
So that's where it's different, when I ran book.where.class it shows that this type is a book Active relation. But when I ran .class on the find by it shows that it's an actual book. That's where it's different. I can call a method on an actual book as something specific to the book where here I had to actually parse through the collection of books in order to do anything to them. So that is the rationale for that.
So that is a dynamic name. Now the very cool thing with this is notice how I typed in find by title, you may wonder you know where are you getting this title from. Because if I open up the code and you know that this is a method right here but if I open up the code if I run a full search call a universal search saying find by title. Hit return. There are no matches.
This is kind of weird because if there is a method that I can call on my book object I should be able to find it somewhere. Now that's where rails is really cool is that during the compilation process or when the application is run. Whenever you have a table that has its own attributes like title right here what Rails does is it goes through and it creates dynamic methods based on those attributes.
So we can do book find by title and rails looks at your schema file right here and when it runs it says OK. He's trying to look in the book stable and he wants to find something with the title attribute and it dynamically creates that method.
So if you look back and let's say that we want to run
Author.find_by_country("Tatooine")
Run that and that's going to bring it back.
Now this is also one of the little gotchas. Say that you had multiple authors from "Tatooine" if you run this query you are only going to bring back a single one. So I can show you that I'm not lying here I'm going to copy this paste it in and we'll say that this is Luke's twin who is also from "Tatooine" and now if I run this query find by country with "Tatooine" it still only brings me back one of those items.
Author.create!(name: "Luke's Twin", country: "Tatooine") Author.find_by_country("Tatooine")
This is where you would want to have something like
author.where(country: "Tatooine")
and now it brings back both records.
So hopefully that gives you a little bit of an idea on when you'd want to use one of them over the other find_by whenever you're wanting to be very isolated specific you want to bring back a single result. Where is when you want to know all of the items that match your criteria and you want to have those brought back to you.
So one of the next very helpful things is understanding and knowing if any records exist. So a good example of this would be if you notice I created three authors and I created four books. If you look only at Vader has books that he wrote and Luke has one and Leia didn't actually write any books. She's in the authors table.
So if I do
Author.find_by_name("Leia")
she exists there in the table. Now if I store Leia in a variable
leia = Author.find_by_name("Leia")
now if I try to call
Leia.books
right here it's going to be an empty collection. And so that's something that is very helpful to know because what happens if I do the same thing here with Luke.
luke = Author.find_by_name("Luke")
If I do this and then call
luke.books
you can see that it brings back the books or the single book that he wrote.
So a very helpful method is the any method. So if I do
luke.books.any?
it returns true. if I do
leia.books?
it returns false. And that's something that is very handy to know and it's also cool to come up here notice that no no database queries were run right there because the data was stored in the variable. So it's not a very big performance hit to run queries like this.
Now when I ran luke.books look at the sql query right here, right here at saying select all the books from the book's table where the books.author_id is 2. So that is right there a relational query. It's actually talking to two tables and saying I want you to bring me back all of the books that specifically have the author_id of 2 which in this case is Luke.
So that's also kind of a cool thing is with each one of these we can see the exact sql that's being generated and hopefully also appreciate how much easier it is to run these kind of methods that say luke.books as opposed to writing out an entire set of sequel just like this.
It's just one of the other reasons why the adoption rate on the rails side is so much faster than a lot of other languages because many languages require you to write this kind of code completely from scratch which can be pretty buggy and also not exactly the most fun thing to do.
When I was originally starting out as a developer I was building a lot of PHP applications and they were pretty much from scratch and I had to write all of this code out constantly and it gets very tiring I can assure you.
So we've covered copy and pasting sql to show you that running this is exactly the same as running the methods. We've talked about dynamic query names, talked about "where" "any." We also discussed count, so where you can do something like book.count and it brings back the full count and also the query here is pretty cool it shows select count which is a method.
So this is actually a programming mechanism and it's saying Asterix means all. So any time you see Asterix in sql it's saying I want everything from you know whatever you're passing in. So select count from books. It's just saying count up all of the books and that is very helpful. But we are not limited to that one method. There are all kinds of cool calculation methods inside of sql and ActiveRecord and Rails allow us to tap into those.
So the next thing is let's talk about what if we wanted to see how many sales a author made. Now if you go in reference to the schema file you may kind of wonder how this would be possible. So we have a book that has the sales but then we have an author over here. So what we need to essentially do is we need to wire up Vader and this author account to see and tally up all three of his books.
So that may not be the most intuitive if you've never done it before. You know one way you definitely could do it would be to make a query on the author to find Vader and then loop through all of his books and then tally up each one of the sales attributes that would be one way of doing it. However I would not recommend that because that is a ton of code for something that you can do in a single line.
So let's find Vader and store him in an instance variable. Hopefully he's cool with that. Say
vader = Author.find_by_name("Vader")
So now what we can do is say
vader.books
this will show us all the books I hit up now we can actually run our method. I can just call sum here and this is a method very similar to count but instead of counting we're summing and then pass in the column name that we want to sum.
vader.books.sum(:sales)
So here I can just pass in sales which as you can see right here sales is one of the attributes that's when the column names inside of the book stable.
If I hit return, you can see that Vader has twenty one hundred ninety five sales. Now this works perfectly for Leia as well.
So if I have
leia.books.sum(:sales)
she has zero books if you remember. So this should pull in 0 and it does. And then lastly we can do the same thing for
luke.books.sum(:sales)
and his is 500.
So that is a very nice and efficient way of being able to do that. And let's see in the sql exactly what's going on here. So here we're saying select and sum up the book sales column. So we're saying from the books table grab all the sales sum of them up and then it runs from the books where the author_id is equal to either 1, 2 or 3 whatever author were passing and whether it's Vader, Leia or Luke. That's also a pretty cool thing.
Additionally another item that I found to be very helpful is the ability to average things. So say that we want to just find the average number of book sales. I can run
book.average(:sales)
Now if I do this it's going to return a decimal. So if I return by default it's going to return what's called a big decimal. Don't be afraid or intimidated by this scary looking number. All you have to do to fix this is say .to_f.
book.average(:sales).to_f
that's going to cast it to a float and that data type is much easier to read.
So if I hit return it brings back the average of all the book sales is $673.75. If you want to run the math yourself and total up all four of these books you'll find that that is accurate. So that is how you can run averages.
Now another thing that's very helpful is by saying I want to know what book has sold more than all of the other ones or I should say what the total number is.
So if we have all of the books if I
Book.all
and you look through each one of these you can see that the first one was 500, then we have the next one is at 950 so on and so forth. That's nice but let's say that we had thousands of books. We don't want to have to go through each one of them to see which one was had the most.
The way that we can figure that out is by running
Book.maximum(:sales)
and it's just going to go and find the largest number and 1200 is the largest number.
Now this is very helpful if you're just looking for the value. So if you want to say I just want to see out of all of them which sales are the greatest then maximum works great. I've also found that a very common thing is to also need to know which book was the best selling.
So this is only returning to 1200 which if we look at our data this is only returning this item "DIY Deathstar" which is great but it's not telling me what the book is.
So in order to find that out I can run
Book.order('sales DESC')
and this technically is a little bit of a work around because if I run this one thing you'll see that order does it's kind of like where order brings back a collection.
So if I run this it brings back all of the books. The one difference is it brings them back in an order that is sorted by the sales. So here we can see the first one is 1200, the next one is 950 and it goes all the way down.
So this is good but if I wanted to bring back the top book the one that sold the most I can run
Book.order('sales DESC').first
and this is going to bring me back exactly what I want. So here brings back the book with the title and everything like that and everything's working perfectly. Because I called first I can also do things like call
Book.order('sales DESC').first.author.name
and it brings back the author name.
So that is a very cool way of being able to find the greatest value whether it's a great value or the object with the greatest value and it's not limited just to sales. You could do that on anything that you wanted. That typically you do with integers or decimals or that type of data type.
So we've talked quite a bit so far we've been covering a lot of sql. The next thing that I want to get into is the ability to run multiple queries and be able to connect tables that technically are not connected.
I know that may not be the clearest I think looking at real data is going to help out the most. So here we have our authors. Next we have our genres and then we have books. Now books are connected to genres and authors and that's really it. So the book is almost kind of like a little bit of an intermediary. Because of that we can actually leverage that.
So if you ever have a table that has two references, so it has a situation where it belongs to one table and it belongs to another. If you need to be able to connect these, if you need to be able to know which genres that Vader has written then you can actually do that. And we're going to leverage the "has many" through a mechanism in order to do that.
Another example where this is a very common and I've done this pretty much on every social networking application I've ever built is the concept of building relations between friends. So say that you are building a social network and you have to have the feature of one user being able to be friends with another user the way you can accomplish that is by using a has many through tables.
So this is definitely something that is important to understand. You'll most likely be asked to build this at some point or another. In our case we want to know which is genre each one of our authors has written in and vice versa. We want to know in our genres if we run a query for fiction for example I want to know all the authors that have written fiction.
So essentially we need to be able to have genres to have many authors and we need authors to have many genres. And there's a very cool way of being able to implement this. I'm going to open up author and even see right now. It's a traditional has many books. Now I can also say has many genres.
Now this by itself won't work, the reason why this won't work is because in order for this just like this to work it needs to be set up like a book. If you have has many books then the book table needs to have an author_id. That's when we talked about foreign key relationships, that's what I was talking about.
We're not going to have that because just thinking about it logically looking at the schema file. If we added a genre_id to authors or a author_id to genre that means that every single genre would have to have its own author and that would not be intuitive because what happens when we have several books written in the fiction category a fiction genre that were written by different authors? You wouldn't be able to have that kind of set up. So instead that's where Rail's created this concept where it allows you to pass through tables.
If you have a situation like we have right here we can say that an author has many genres through books and then we can reverse this. So if I open up the genre model. I can say that a genre has many authors through books.
The reason why this is possible is because even though authors does have a genre_id and vice versa for genre it doesn't have an author_id. The books table has both of those things. If you look at books books as a genre_id and an author_id so whenever you have a case like that then you can actually build a relationship and then you can run queries like this one right here.
Now in order to have this work if you do not read reload the rails console this will work because if you make changes to your model then the rails console doesn't listen in on those changes it's not like the Web site. We need to be able to hit
reload!
hit return and what that will do is kind of refresh its concept of relationships and all that kind of thing. If you don't do that you will get an error when you do this.
So now what I want to do is I want to find
vader
I have as instance variable. Now I know I can do
vader.books
we've already done that before but now what I want to do is
vader.genres
and look at that, now it says that it is not working.
So why isn't that working? Surprisingly it's not because of something in our code. Instead it's because of how we ran our query in the way that Ruby works with variables. It actually stored some knowledge inside of the vader variable.
So what we need to do now is just rerun that query so that it's not looking at old data. So if I go like this I can go
Author.first
which I know is vader and say
Author.first.genres
and now it works perfectly.
I could even say that and just so I override the old variable, I can say
vader_new = Author.first.genre
or updated it doesn't matter you can call it anything. In a real life application this won't be a big deal because the rail system would refresh itself.
I did want to point this out because it is something students have brought up as being a bug and they thought something was matter with their code when really it was just the way the rails console works.
So I'm going to say
vader_new = Author.first
run that and now I can say
vader_new.genres
and that works. So that's pretty cool.
Now what we can do is we have the ability to run these kind of queries where we don't have to worry about going specifically and only having the books shows genres. If we want to have say an author profile page, we can have that and have all of the authors genres every category that the author writes in right there. And that's a very cool thing and that's also something you're going to be asked to do a lot as a Rails developer.
So make sure that you're being able to implement has many through it becomes kind of an intuitive thing for you because that will be something you'll get a lot of practice with.
Now the next thing I want to talk about is pluck. Pluck is kind of an interesting one and it is incredibly helpful when it comes to being able to select pick and choose the kind of items that you want. Let's say that you have a page and you want to list off all of the genres for an application but you don't want to specifically have to iterate over all of it so you don't want to say
Genre.all
and have all of these objects. Instead you need an array of the objects you you don't want this big active record relation you just want to have the genre names.
The way that you can do that is by saying
Genre.pluck(:name)
and now if I hit return, you can see that this brings back an array of my names. So that's pretty cool.
I can do same thing with author. I can say
Author.pluck(:name)
and that brings back all of the titles.
That's also something I do quite a bit. Additionally one thing that I will use this for is to do things like this where I want to do
vader_new.books.pluck(:title)
and you can see this brings back all of the books that Vader wrote without having to have the full set of objects.
You're not required to use pluck, however, you will find that it comes in very handy because it lets you be more isolated with your query. Each time you need a very specific set of attribute values pluck can be very handy.
Also there's a little bit of a shortcut. You can also do something like this say that you needed all of the author_ids. You can say
Author.ids
and it'll bring back all of the ids. And that looks like pluck, so one of the things you may think is possible is to say something like.
Author.names
that will not work though.
So it does not dynamically create methods if you want to do that you just have to say
Author.pluck(:name)
and then it will work.
So we've been covering a lot of ground right here. So I only have one last thing that we going to finish up with. And to do it I'm actually going to clear everything off here in the browser just because I don't want any old database queries right there in the way I want to compare these.
So here I'm starting up the sql dive app and if I come to this book page and hit refresh then it's going to run the database query and look at all of these items so this is all of our sql queries we can see it's selected all the books from the book stable then with each item, notice we have an author and we have a genre, every single time it is going in hitting those.
So for each book and if you want even more of an isolated look at this so if you go to the index page where we were, what's happening here is every time that it's iterating through, so with each iteration here it's going and it's running a sub query. It's going and looking at the author and then it's looking at the genre. And that's the reason why we have all of these calls.
We have four subqueries and eight total queries right there and that's only for 4 records which is quite a bit because each one of these takes a little bit of time. It's not a lot of time, you know you can see it's half of a millisecond, that's not crazy. But what happens if you have a thousand or what happens if you're running processes on the data as it gets back. This can be really slow eventually. So this is considered a bad practice to do something like in a controller especially one that has relationships to run something like a book.all you will rarely ever see this in a professional Rail's application.
Instead what we're going to do is use the includes method. So what includes is going to do is it's going to wrap up all of our database queries into a single one. So instead of making all these little queries that total up to be a very long time it's going to run just one query and then it's going to store all of that data inside of our variable and then we can use it however we want.
You wouldn't do this for a small set of data but this comes in very handy when you start working with larger amounts of data. So if I do
book.includes
I can go pull in the author and the genre. And this is going to do the same exact thing from a results standpoint. This is the same as saying book.all in terms of what we're going to see.
I'll prove it here come into the browser if I hit refresh, nothing changes. But now let's go look at the console. This was our first time. So when we did the book.all this was our set equerries we had eight queries here plus the main one. Now look what it is it 8 went down to 3, if this was a thousand, guess what, this would still be 3. That's what's so cool about the includes method is it helps you to be a lot smarter and more efficient with your database queries.
So instead of going and hitting the database over and over and over again instead it allows you to say I want all the data because I'm going to use all of it and I want you to bring it all back and then I'm going to store it in a variable so I can use it however I need on that page.
So there is if you also notice there is no code change except in the controller. That's one of the cool things about the model view controller architecture is we made no changes here on the index page. We were able to use the same variable. We're able to call the same methods on everything like that. Only difference was that we made one change here and made our entire query more efficient.
So great job if you went through that. We covered a ton of ground, definitely recommend for you to cycle through and go through this deep dive a few times because if you've never used some of these methods before then it takes a little bit of practice and a little bit of repetition. Great job you're going to be able to have much cleaner equerries from now on by being able to implement these kind of processes.