Learn to use several calculation methods to perform mathematical operations on database records efficiently, without having to retrieve all of the records.
- [Narrator] In this movie, we will learn how to ask active record to perform calculations on records in the database. This simplest of these is just simply count. And it returns a count of the records. So for example product dot count will return a count of the number of product records within the database. The total number of products that are in the database. We can also put it after a query, such as where so that we can ask for product where price is greater than or equal to 20. And it'll tell us the count of how many records match that condition.
It's important to realize though this is not the same thing as querying the database to get back all of the matching records and then to count the results. Instead what this does is it just simply asks the database to perform the count for us. It says hey database, don't return all these records to me just tell me how many there are. So maybe there's a hundred thousand records don't send me a hundred thousand records back as a response. Just send me the number, one hundred thousand. It's much more efficient if we don't actually need the data itself, we just want the count.
We can do something similar with sum and average. Now obviously these only work on numeric types of columns like integer and decimal columns so that we can add them together. They won't really work with booleans or strings and other things. So for example we might have product dot sum price. And that'll return the sum of everything that's in the price column, for all of the products. And again we can use that with where a clause is if we want to scope it to something smaller than all products. We also have average which does the same thing but not only does it come back with the sum of the price but then it divides it by the number of products in order to return the average price.
Again, in both of these cases just like count it's not going and getting all of the data and bringing it back to ruby, in order to calculate the sum instead it's asking the database to perform the calculation for us. And the database has a built in knowledge on how to do that you can do it much more efficiently. And then we have minimum and maximum which do what you would expect. They return the minimum value and the maximum value. So here I'm asking for the minimum price that's in the database, or the maximum price. Let's try these out, especially so we can see the SQL that it generates.
From the root of my rails project I'll launch the rails console and let's start just by doing product dot count. Now I have four products in my database. It doesn't matter if you have the same thing or me it's not just make sure that you have several products in there so that we can add the prices together. So you can see the query that it generated here it didn't ask for all the product data back it asked the database to return a count of how many records it would return. And so it just returned back four to me. Let's try another version of this where we do product dot where and let's do price, oops lowercase, price greater than or equal to 20 count you can see it still generates the query exactly the same the only difference is that it adds count here so that it just returns back a count of the matching records.
Alright before we actually do the sum let's use pluck, which we just learned about and let's pluck the price back from the product table. This will show us all the prices. Now those are big decimals cause it's a decimal column. Those aren't very easy to read. So let's also just amend this to add map and p, p dot two s and that'll turn into a string for each one. And then we can read it a little better. So we have prices of 100, 40, 10 and nil.
You can see that was actually here nil turned into a string, so it because a nil string. So those are the prices that are in my database right now. So now let's add those together. Let's ask it to do product dot sum price and then let's also call to string on it because we want it to be readable instead of being a big decimal. You can see that the sum of those was 150 and that's what we'd expect. 100 plus 40 plus 10. Nil just gets omitted, it doesn't get counted at all. So now let's try average, product dot average price and let's also call to string on the result and you see it came back with 50.
Now notice what it did there it did not count nil as being one of the divisors. It divided it by three, not by four. So 150 divided by three gives us 50. 150 divided by four would not. Alright next let's try the minimum and the maximum product dot minimum price again call to string on it and let's try product dot maximum price and you can see it gives me back the maximum price.
10 and 100 that's exactly what we'd expect. Now look at the SQL that it generated for all these. Just like we had up here with count notice that we had sum, avg, min and max those are the, my SQL commands to tell the database to return this value to us. So it tells the database that it should perform the calculation, and just return the result to us. In cases where we don't need to work with the underlying records or when we're working with a very large number of records this is going to be a much more efficient way to interact with the database.
- Migrating databases
- Creating, updating, and deleting records
- Tracking changes to objects and attributes
- Writing queries
- Delegating attributes
- Working with associations