 I want to talk about databases and Ecto, hence the title of my talk, which is Embrace the Database with Ecto. So my name is Josh Branshaw, I'm a software developer at Hash Rocket. Hash Rocket is a Rails consultancy. I've been there a little over two years and I like to think of us as like Rails and Postgres experts and there's definitely a lot of buzz and excitement around Elixir as of late and so that's a big part of why I'm here and that's why a lot of my co-workers are here, we're very excited about Elixir. Also we host Chicago's Elixir Meetup so if you're ever in town let us know, maybe we can get you in for a speaking slot. Here we go. So I have some opinions about databases. My first opinion is that the database is your friend. I know it's my friend, it gives me all kinds of assurances, it makes me feel powerful when I'm able to like do these like things that feel like insane queries, like that's a good feeling. And when it does it really fast that's great. But you might be thinking like, but the database is scary, right? There's things like composite indexes, full outer joins, commentable expressions, explain analyze output, these are like scary sounding things. Database can feel like this black box with all these crazy features. But the database doesn't need to feel like a black box and these features don't have to be scary if we get familiar with them. And that comes to my second opinion which is that the database doesn't just have to be some dumb data store but if we like know those features then we can think of the database as a powerful computation engine. So there's lots of stuff that we might do sort of inefficiently in our application logic that would be much better handled in the database. Things like organizing results in a particular way, joining data together and then like crunching data for reporting, among other things. So like if we understand our database we can take advantage of these things. Question three, so I have a question. What is the single most important asset in the life of your business or product? I don't think it is like your product's idea necessarily. I don't think it's your code base, right? We rewrite our code bases all the time. Our app used to be in Rails and now we're writing it, rewriting it in Elixir perhaps. But in fact I think it's the data. So I think the data stored in your database is the single most important asset. Like that tells you everything about your users. It's the content that your users create and come back for. Perhaps you're like running a business that deals with money and transactions. It's all your transactional data. So that's super important. We need to protect it. We need to make sure that it's correct. So opinion four, the best database for applications is PostgreSQL. It's open source. It has a really fast development cycle, release cycle. So lots of new features and releases coming out all the time. It's asset compliant. So that means that we get transactional guarantees. So if we tell Postgres to insert something and it doesn't, we know it's going to be inserted or Postgres will tell us that it didn't. We're not just going to send off an insert query and who knows what might happen with other databases. So we have that assurance with Postgres. Also it's performant and scalable. So we can have tables with hundreds of millions of rows on a single node. That's awesome. So here's the agenda for the rest of the talk. I want to talk about data integrity because our data is important and we want to protect it. We'll also look at schema as queries, which is an awesome feature of Ecto. Ecto can't do everything though. So we'll look at Ecto as a skate patch and then we'll also look at how we can enhance Ecto with custom functions for our queries. I don't want us to play around with a contrived data set though. So we're going to use a sample data set that, or rather not a sample data set. But actually a data set that comes from a production application today I learned, which is something that we built internally at Hash Rocket. It's built by our team, primarily by my co-worker Jake Worth. And it catalogs the sharing and accumulation of knowledge as it happens day to day. So we do a lot of pair programming. I might be sitting next to my co-worker Chris and he shows me this cool new trick in Vim. And I'm like, okay, I have to write a TL about that and I'm able to post on this site and then can reference that later. Yeah, we use it a lot, we use it every day pretty much. So, TL's database schema. The three things I want to focus on, there's more to it than this. But this is all we care about for the purposes of this talk. We have post table, which is the content, those are the things we post. We have developers, those are the people that post those things. So there's a one to many relationship between developers and posts. And we have a channels table. So those are like the topics that posts fall into. So again, there's a one to many relationship between channels and posts. So here's the important stuff in our post table. We have an ID, which is the primary key. We have a title and body, obviously. There's likes, so if people upvote your post, that likes count will go up. And then there's also foreign keys to the developer ID and channel ID. Or to the developer table and the channel table. So we also have a developers table, right? So there's an email and a user name. And then that ID is the primary key that's being referenced by the posts table. And then our channels table, which just has a name. And then the ID that's being referenced by the post table as well. So the name is going to be things like Elix or Vim or Ruby. Those are the topics that posts tend to fall into. So TIL has been around for almost two years. We use it a lot, so it's got a good amount of data. So we've got this database full of data. And when I see something like that, when I open up a P-school session, I see all this data, like I feel like we have this pile of answers just waiting to be asked the right question. So what we're going to be talking about is asking questions. So how do we ask questions of our data? Well, in order to ask questions, we need a mediator. And so the question is, what's the best mediator between us and our data? Well, this is an Elixir conference, so obviously the answer is SQL, right? SQL is the best way for us to talk to our SQL database. Right? Like, it's very expressive. Like, I just say, give me the count of the things in my post table and we get a count. There's 1,066 posts. But you might be wondering, what about Elixir and Ecto? Yeah, so I think even though SQL really is my favorite way of interacting with a SQL database, it's like super expressive. We have access to everything when we're dealing with SQL. I think Elixir and Ecto provide like a really compelling way for us to interact with our data and write queries, perhaps in ways that we can't necessarily do with SQL. So that's what we're going to be looking at. If you don't already know, Ecto is a domain-specific language for writing queries and interacting with our database in Elixir. So this is perfect. Yeah, so here's what a query in Ecto looks like. We can ask the same question, how many posts are there? We say, from our post table, give us a count of the post IDs. And the first step is to define the query and then we have to use something like repo.one or repo.alt to actually execute it against our database. And then the result is the same result we saw before, 1066. So queries are just data. That's something I want to point out real quick. So we can think of queries as data. The reason we had to execute our query was because before we did that, it was just a struct. And what's cool about that is we can just build these things up as we go. We can build a partial query and then add to it as we know more or passing it off to a different function or something like that. And then we can also inspect them. So for debugging or want to know something about our query, we can just look inside. So the next part of the talk is data integrity. So like I said before, our data is important. We want to take care of it. So I think data integrity is a really important thing to talk about. And so I want to talk about laying a solid foundation for our data. Because without a solid foundation, we're going to end up writing, in order to write complex queries, it's going to be even harder if we don't have a solid foundation for our data. Because our data is only any good if it is correct. If we have incorrect or mismanaged data, that means our business suffers. It means the experience of our users suffers. And if we're dealing with money or transactional data, it could mean that our business is throwing money away. So that's a problem. We need our data to be correct. For those of us that are web developers, we like putting data in our database, right? Like we build our main Rails application, for instance. And that inserts and updates data all the time. And then we need to do certain things faster or better. So we write microservices in Elixir. And those things also insert and update data. And then our users want to be able to access our app with their phone, like their iPhone or their Android phone. And so they want a mobile app. Those apps are also going to insert and update data. So we've got all these clients that are inserting and updating data when we build an application. And the question that pops into my mind is like who makes sure we don't put bad data in our database? And it's the validations, right? Like we write validations in our app, in our mobile apps, in our microservices. These are the things that we put in front of, between our applications and the database, to like make sure that the data is right. But my concern when looking at this is like we've duplicated our validations across like many clients. That seems like a problem to me because those things could get out of sync and then we're not enforcing the same validations everywhere. So I want us to put our validations primarily in the database because I think our database is the ultimate gatekeeper. We want our database to make sure that our data is correct and right. As Joe Selcoe puts it in the Advanced SQL Programming Book, a database actively seeks to maintain the correctness of all of its data. So if we tell our database what our like business concerns are and like what are the things, what the shape of our data should take, like it can enforce that for us. We just need to give it the logic and we can do that with a variety of constraint mechanisms. The first of which are data types, right? So we do this all the time. We say like our email field is a string, so we use VARCAR, we say our admin flag, it can be true or false, so that's a boolean. And then something like a created ad, that's a point in time, that's a timestamp. So these are ways that we constrain our data. Right, our database will yell at us if we try and put things into these different columns that like don't match their data type. And we can go a step further. Depending on our database implementation, we might have access to, you know, a variety of different like more custom data types. So for instance, we might want to use UUID instead of sequential IDs. Maybe if we're dealing with really big numbers, we'll need like something like a big N. But there's a variety of numeric types that Postgres gives us access to. And then we can even like pull in extensions like the CI text extension, which gives us access to an additional data type called the CI text, which is case and sensitive text. And that's great for email. So you can see here, like we've replaced our ID with the UUID primary key. And then we also use the CI text extension for our email, because emails should be treated as case and sensitive. So there's just a couple of examples. The next thing we can enforce is that we can enforce the presence of things using not null constraints. Right, so we've used not null in all these things except for our created at. So I guess what I want us to think about there is like if we allow a created at to be nullable and then we end up with null data and created at, like what does that mean? Do we not know when it was created? Or does that mean something else? Like it's ambiguous when there's null in our column. So we should just like always use not null constraints. As Joe Selcoe puts it, also in the same book is the most important column constraint is the not null. We should just use this automatically and only remove it when it makes sense. And this will help us avoid complications when writing queries, if we allow things to be null and we don't like have a good reason for why they're null, that makes it harder to write complex queries. The next kind of a data integrity we can enforce is like we can enforce relationships with foreign key constraints. This is super important. Anytime tables are referencing each other, like please use foreign key constraints. Without them, our database won't be able to prevent the breaking of relationships and we can end up with orphaned records. And then lastly is check constraints. So we can enforce more general relationships with check constraints. We create one right here that just says our likes column always has to be greater than or equal to zero. So we can only upvote posts and that check constraint helps ensure that, but we can do much more interesting things than this. We can create check constraints that enforce relationships between different columns in the same table. So yeah, don't be afraid to take advantage of check constraints. So with all these things in mind, we can lay us all the foundation for our data. So the next thing to do is to start querying that data. So we're gonna look at schemaless queries. So the top thing there is a schemaless query, right? The target of our from clause is a string which references the post table. The bottom thing is a query against a schema, right? My app post module, which is some schema that is against the post table. So we're gonna be talking about the thing on the top. Also one other thing I just wanna point out as we continue into this talk and look at some source code examples. You can assume I've imported ecto.query for all these examples. That's what gives us access to things like from. And then you can also assume that I've aliased whatever the namespacing of repo is so that we just are able to use repo. So you might be wondering why schemaless? So when I'm like curious about what's in my database or I'm about to write like a complex query, what I like to do is open up a peaceful session like a terminal into my database and like just start playing around with the SQL figuring out like what things look like and start iterating on the query. And I think we can do this with Elixer too using schemaless queries. What schemaless queries allow us to do is kind of take a free formed unconstrained approach to query building. So we can like start small and then start building up a solution to whatever question we're trying to ask. And we don't necessarily need a schema in place to do that. You know, it gives us the freedom to kind of explore that before creating our schema. And frankly, like if we're not like dealing with the situation where we have like a one-to-one mapping between our table and the data that we're requesting, maybe it doesn't make sense to create a schema. And perhaps later on you'll see a pattern emerge and then it makes sense to create a schema but don't do that beforehand because that's sort of a premature abstraction. So it's query time. So the first question I wanna ask is how many developers are there in this database? And we probably have a pretty good idea of how to do that already because we saw a count of the posts. So instead we have our from clause target the developers table and again we select the count of the developer IDs. There's 32 developers. So the two things I want to point out here. First is the from clause, right? It specifies one or more source tables for the select. Every query in Ecto starts with the from clause. And this declares like the source. So we can be like thinking about our source first. And then we've got the select clause as well. This is what retrieves rows from, so select clause retrieves rows from zero or more tables with Ecto because we're always targeting a table. It really we're selecting from one or more tables. So with SQL we're always starting with a select clause like I don't know much SQL y'all right but we always start with a select clause and then eventually we get to our from clause. Maybe we join some things in. What I like about Ecto is that we start with the from clause. We're always thinking about the source first and then we get to like what are the results that we want to return. So the next question I want to ask, how many posts by channel do we have? So this question deals with both the post table and the channel table, so we need a join clause. And a join clause allows us to combine two from clauses. So let's see what that looks like. So again we use the from clause to target our post table. We join in the channel's table and then we add the on portion to say that we only want to join when the channel IDs match. Without that on portion we're gonna get like the Cartesian product of our two tables and that's way more results than we want. On one other thing I bound it to the variable posts and channels so this is like a partial query. We can't execute this on its own but because it's bound to posts and channels now it's something that we can use in subsequent queries. So we can think of channels as like these buckets and each channel bucket has the posts that belong to that channel and so in order to get things into those buckets we need a group by clause, right? Because a group by clause combines the output into groups of rows that match the grouping value and for this our grouping value is the name of the channel and we can select that name and then you'll see because posts and channels variable that we created just a moment ago has two sources, the post table and the channels table we're able to reference both of those using a list which binds to the P and the C. So if we execute this query we'll get a list of our buckets, right? These channels that we wanna group things together in but we're still missing the count of the posts. So in order to get the count of the posts we're gonna need to think about some sort of aggregate function, right? Because aggregate functions are competed across all rows making up each group and then producing some separate value. So there's a variety of aggregate functions that we have access to in SQL but the one we care about here is like count because we're trying to count the posts in each of these channel buckets. So we add in account to our select clause and that should do it. The thing to note here is that our select clause is now dealing with a tuple that has the count and then the name and why that's interesting is because with Ecto and Elixir we are able to wrap the results of our select query in any sort of data structure that we want. So that gives us a lot of like power and flexibility to kind of organize our results, the result of our query. So if we execute that, right, we see we've got the count of each post and then the name of the channel that those posts are in. But these results are kind of messy. It's hard to see like what's the most used channel and so forth so I'd like to order them. So we'll look at an order by clause. So if the order by clause is specified then it returns all the rows sorted in the specified order. With an order by clause you can order just by rows that are in like are specifically on the table or by some like computed value. You can order an ascending or descending order. And one interesting thing to note about the database is that like a Postgres there's no default ordering. If you query something and you don't include an order by clause the database is just gonna return it in like whatever was the fastest order that it could like compute that query in. So if you do care about the order of your results like you should specify an order by clause. So we do that here saying order by and then we give it a keyword list. We say in descending order give me the counts of the posts. So we're not actually specifying something in the table but instead we're specifying the some computed value which is the count of our posts. And if we execute this we'll see a much nicer output of our results. So Vim, Ruby, SQL those are pretty frequently posted about things. We've got Elixir has about 73 posts. Cool. The next question I wanna ask. How many posts on average are there per developer? So for this query we just need the post table. The post table includes a developer ID so we can kind of group things by that. And we can just jump in with most of the query here. Most of this should look pretty familiar. We have our from clause specifying the post table. We group by the developer ID. Just like similar to how we did with the channels in the previous query. And then we select both the count of the posts as well as the developer ID for each. What you'll notice we did this time though was we used a map instead of a tuple. And what's cool about that is it's sort of like labeling or aliasing the values in the select result. And we'll use that to great effect here in a moment. So if we execute that query we get the results. It's just a big mass of data, right? The developer IDs and how many posts they've written. But this isn't what we're looking for. We're looking for like the average of all of this. Now sure we could like map over this and compute the average in Elixir LAN but I would rather do it in the database world. So we're gonna do that. But I guess one thing else that I wanna point out about SQL is that like the result sets that we get from any query that they're just like another table. Like everything in SQL is tables. Like that's the only data structure. So this query here like the result of it at least in SQL land is just another table which means it's another source. And so we can use that as like a source table and that's where a sub query will come in. So then we can sub query that as like a source. So do that here. We sub query the post count that we just created. We say using the aggregate function we say give us the average of the post counts. And the reason we're able to say post count like use that atom post count is because back here we've labeled it post count. So if we hadn't done that with using the map then we wouldn't have been able to reference it in that way outside of the sub query. So we get the result and it's about 37 posts on average per developer. That's cool. That's interesting. Yeah, so that's kind of an introduction to Schemeless Queries but I want us to try something a little bit more complex. So here's the next question I want to ask. What is the channel and title of each developer's most liked post in 2016? So this query is a little bit more complex. And the thing I want to get across here is that when we're faced with asking like a really complex question, like let's start simple and build our solution from the ground up piece by piece. So again, the question, what is the channel and title of each developer's most liked post in 2016? So this question involves the channels, posts and developers table, all three of them. So we're gonna just need to join all three of those together and we'll do that here in a partial query that we bind to post devs channels. Again, we target posts first with the from clause. We join in developers where the developer ID matches and we join in channels where the channel ID matches. I think the next step here is to filter the most liked post to the top because that's what we're looking for is the most liked posts. So we'll create something called top of 2016 where we target our post dev channels partial query that we just created which gives us access to post devs and channels. We order by the posts likes and descending order. So the most liked posts will be at the top. And yeah, we can select for starters the username, channel name and post title. And if we execute that, you see we're well in our way to our solution but there's one pretty glaring problem if we look closely at the developers here. Developer 26 is showing up twice and we only want one result per developer. So we have to figure out how to deal with that. Now I don't think we need a resort to something like a complicated subquery or some sort of comment table expression. What we're trying to do here is have our results be distinct per developer and that word distinct is key. So we can use the distinct clause. So if select distinct is specified all duplicate rows are removed from the result set. Only one row is kept from each group of duplicates. So we can include the distinct clause and this works really well because of our order by clause. They like play well together because it means that when we say that we want our results to be distinct by developer ID well it's gonna take the first result for each developer and since we've ordered our most liked results at the top then each result we get is gonna be the most liked result for that developer. So we're pretty close to being finished with this query but one thing we're missing is that the name of this variable top of 2016 isn't quite true yet. We need to constrain our results to top of 2016. So we want the created at date of our posts to be greater than the beginning of 2016 and less than the end of 2016 or January 1st of 2017. So this is where we finally introduce the where clause which is actually a very important clause. Something we'll use quite a bit when we're constraining our results because the where clause allows us to eliminate results from our output that don't satisfy the condition or conditions that we specify. So we add these in here. We can add as many where clauses we want. In this case we only need two. The first one says where the created at date is greater than the beginning of 2016 and where the created at date is less than the end of 2016. And if we take a look at our output it looks pretty good. These are our results. See there's some elixir, Vim, Ruby posts in there. Yeah, so that's the answer to our question there. And that's exciting because we had a pretty complex query or question that we were faced with asking and we were able to build what turned out to be a fairly complex query from the ground up just iterating on our solution until we had the answer we were looking for. A couple other things worth pointing out with Ecto 2.0 there's update all, insert all and delete all. These are also functions that can be used in a schema-less fashion. I'm not gonna talk about them but I just thought they were worth noting. Cool, so on to Ecto's escape patch. So we can't do everything with Ecto. It provides a pretty good abstraction on top of SQL but there's lots of cool features of our database that it doesn't give us access to. So sometimes we need an escape patch. One way of getting right into the database is just using Ecto.repa.query. This allows us to just execute raw SQL against our database. So if we wanna use a function like Generate Series, we can do it and we get the results. But perhaps more interesting and to me at least is the Fragments function. So we have access to the Fragments function as part of the query API. So this is a function that we can use in the context of our queries to inject SQL into our queries that we wouldn't otherwise be able to use. So we can see an example of that here. This is sort of contrived because we have access to the built-in count function but why not use the SQL count function? So again, we can select the count of our developers and yeah, we can see the slot clause that it produces actually looks pretty similar to the one we used before. We can even interpolate arguments into our fragment in this way. So if we specify a question mark or any number of question marks then we just give it that many arguments and those will be interpolated into that chunk of SQL. And that's a great way to pull values like the d.id, things that are only existing in Elixirland. That's a great way for us to pull those into our query. So this is a pretty cool tool. So let's revisit our query from before and really put it to use. So we have these two whereby clauses but I think we can improve on them by introducing a fragment and I think the way we can improve on them is by better conveying the intent of the whereby clause because what we really wanna say is that our date is between two things, right? We wanna use the between predicate that allows us to simplify the way we think about this range test. So our created at date has to be between some values X and Y and the between predicate allows us to do that. So we can create a fragment where we say something is between something and something and then we give it the three additional arguments where it's post.created at and then the two bounds of our time and now we can just add this to our where clause. So now we're down to one where clause and I think the intent of that where clause is better conveyed now. We know when we look at this that oh, we're trying to say that this post is between these two dates. So this is pretty good use of the fragment function. Another place you can use the fragment function is it's exposed in migration. So if you wanna use default values that are based on functions in your database, you can do that as well. So I think we can take that example. We just looked out one step further. It was a nice use of the fragment function but it's a little bit clunky and I think we can make it a bit more elegant using custom functions. So this is the fragment we just had and between is like a common thing to use in Postgres. It's a pretty common feature. It's something that if we have a long run application we might be using it quite a bit. So not only would it be nice to clean it up but it'd also be nice to make it reusable. So let's wrap it in a custom function. So if we define some sort of module like custom functions module, we can put all sorts of macros in here that expose different fragments that we wanna be able to reuse. In our case we define the between macro which takes some value and a left and right bound and creates this fragment for us. And yeah, if you wanna understand the kind of nitty gritties of macros a little bit better I'm not gonna talk about that here but Jason's talk tomorrow afternoon will be a good place for that. So check that out. So yeah, we import our custom functions module and then we're able to use it. So anywhere we wanna use the between function, we just import our module and we can make use of it. And I think that cleans things up a little bit more and it's reusable. We like things that are reusable because then we don't have to worry about messing them up. Cool, so I have one more question that I wanna ask. I think this question will allow us to like put all the things we learned, put them all together but it's kind of a tricky question. So what are the hottest posts? So this is actually a feature that already exists in TIL right now. Right, if we have the stats page and on the stats page is a thing that says hottest TILs and these are like in real time like the posts that are like on fire. The posts that people are like upvoting the most. So I want us to think about how we would write this as a query in Ecto. But the question we need to ask first is like how do we measure hotness? I'm sure there's lots of ways to measure the hotness of a post but the way that I want to do it is with this like hacker news-esque ranking algorithm that says that the hotness score of our post is the number of likes that it has divided by its age and hours and where the age and hours is raised to some power of gravity. And for our purposes, you know, you can play around with that gravity value. It could, you know, depending on how popular side it is different values might make sense but for our purposes we'll just use 0.8. So that's the function we want to compute in order to come up with a hotness score for each of our posts. But it's kind of a lot to think about. So maybe we just need to break it down piece by piece. So let's just say what's the age and hours? Well, it's the age and seconds divided by 3,600. That's true. But what is the age and seconds of our posts? Well, I guess it would be like the current time stamp like right now, like as we're executing the query minus like the published at time stamp because it was published at some point in the past. But that's not quite right because in Postgres when we subtract time stamps or add time stamps or whatever, we get an interval. And an interval is its own thing. What we actually want though is seconds. So we can convert that interval to seconds using the extract epic function. And so yeah, that'll give us our time and seconds and we can put this all together then. And so our age and hours is the subtraction of these time stamps, figuring out what the time and what that is in seconds and then dividing it by 3,600. So that's a lot there. And we can just pull it out into a fragment and then put it inside another macro called hours since. And this is something that we can use in the query we're about to build then. So what are the hottest posts? Well, let's figure out for starters just what the age and hours is for each of our posts. So we'll select from the post table and we'll be creating a map where we get the ID of each of our posts and then the hours age, where we call hours since the function we just defined with the published at date of our posts. And if we run this partial query as there is something that we can execute, we'll see we get the hours age of all of our posts and then we'll also get the IDs. So we're off to a good start. But one thing to note is that posts can be in like a draft state. So we only wanna deal with published posts so we want to filter out any unpublished posts. And so we do that by including a where clause that says don't give us anything where the published ad is nil because that's how we signal what a draft is. It's using nil. And that's actually, that's a good place to allow a column to be knowable because the nil means something. Also another thing, I don't know how likely this is to happen but because we're gonna be dividing by the hours age, the age and hours of our posts, I don't want that to ever be zero because then we'd have a divide by zero error. So I wanna ensure that the minimum value that we ever get for hours age is like 0.1. And so let's define another thing, another macro called greatest that uses greatest from Postgres. And given two things that'll give you whichever one is larger. So we can add that into our query. We wrap greatest around what we already had for hours age and we say either the age of our posts or 0.1, whichever one is larger, like give us that thing and that's the hours age. So that'll make sure we don't end up with any like zero values or anything like super, super small. Also this is a great example of how we can nest the custom functions that we create, how we can like nest them together. So let's also add likes into our select clause because we wanna know what the likes of each of our posts are. And cool, this is a good stopping point for this query or for this portion of our query. This gives us the age and the likes and the ID for each of our posts. But now we wanna compute the hotness score. So we're gonna, excuse me, or we're gonna subquery the partial query that we just created. And from that we're gonna grab the ID which we have access to on P and then we'll add another fragment that allows us to compute the hotness score based on the algorithm we looked at before where we divide, where we take likes and we divide that by the hours age raised to the 0.8 power. We're not quite there though because we wanna order the results of this query to make sure that the hottest posts are at the top. So we add in an order by, where we say descending. But I said descending by two, which might look weird, but if you're familiar with sort of the intricacies of order by clauses in SQL, you'll know that an order by clause can also reference like the ordinal rows, that can reference rows based on their ordinality. So because hotness score is the second thing in our select list, we can say order by the second thing and that's what it refers to. So that's nice because then we don't have to re-specify the entire fragment. We can just specify that as the second thing. And then one last thing I'd like to do is limit our result set to just the top five things. So we add a limit by five to the bottom and that's our entire query and now we're ready to execute it and here are things with the hottest scores at the top and the IDs of the posts. And that's kind of all there is to it, but I don't wanna leave you in total suspense about what those posts are so we can fill out the query a little bit more. We join in our post table and then replace the ID with the title of the posts and we execute that again. And you can see we've got some custom loaders for webpack, clean on track files and get. So these are the hottest posts as of the time that I took a snapshot of the database. Cool, so that's it. I hope at this point that if you didn't already you now think of the database as your friend. Here's a few sources and links. Please check out tiil.hashrocket.com. That's the, those are example dataset for today. Also this book and the Postgres documentation are incredible resources. And thanks for listening. Again, my name is Josh Branchot, I'm at Hashrocket. Yeah, I'm on Twitter and GitHub and please come find me in the hallway and I'd love to chat about Elixir and Postgres and whatever else. Thanks.