Lesson 9

Ecto Queries

We want our splurty application to splurt out un-expected quotes to the user from the database. This means the homepage action of the QuoteController should load up a quote randomly from the database, and then show the details for the quote. We will do that in this lesson.

Before we keep coding, since you're staying up to date with cutting edge technologies, like Elixir and Phoenix we thought you may be interested in giving back and helping teach the next generation of web developers....

Take an Aspiring Web Developer Under Your Wing

Here's the gist:

Me and a few other developers are running theFirehoseProject - a 12 week mentor driven online coding program to train the next generation of web developers.

Our code mentors take a student under their wing for 12 weeks, pair program with them for one-hour per week and guide them towards their personal coding goal.

Throughout each week, students work on building several web applications (think Yelp and Udemy clones), solve real-world coding challenges (from FizzBuzz to LinkedLists), build a web app through TDD and join a group project to build a chess game as a team.

Our mentors are senior web devs from PayPal, Techstars companies, ex-Flickr, ex-bit.ly and get excited by seeing a beginner grow into a proficient developer who's able to solve complex CS challenges.

You can see what some of our students are saying about us here and find more info about what our mentors are doing here.

If you're interested email marco@thefirehoseproject and we'll give you more details!

Ok, the plug is over, let's get back to coding. Ecto's query syntax is quite nice for simple queries. See an example here. Unfortunately it doesn't allow us to perform an ORDER BY RANDOM() from SQL easily. This means we'll need to craft a custom SQL query and load up a Splurty.Quote based on that.

We will build a submodule within quote.ex that will allow us to put various queries inside it. In this module we can build a random method that will execute a SQL query to extract a random record and parse the result into a Splurty.Quote model. To do that, add the following lines of code to web/models/quote.ex:

defmodule Splurty.Quote do
  use Ecto.Model

  schema "quotes" do
    field :saying, :string
    field :author, :string

defmodule Queries do def random do query = Ecto.Adapters.Postgres.query( Repo, "SELECT id, saying, author from quotes ORDER BY RANDOM() LIMIT 1", []) %Postgrex.Result{rows: [row]} = query {id, saying, author} = row %Splurty.Quote{id: id, saying: saying, author: author} end end

Save the file.

Now we can extract a random quote from our database by calling Splurty.Quote.Queries.random.

Modify the homepage action within the QuoteController to hook this functionality up. The homepage action should load a random quote, assign it to @quote and render the show.html template. To do that edit web/controllers/quote_controller.ex to look like this:

defmodule Splurty.QuoteController do
  use Phoenix.Controller

  alias Splurty.Router
  import Splurty.Router.Helpers

  plug :action

  def homepage(conn, _params) do
conn |> assign(:quote, Splurty.Quote.Queries.random) |> render("show.html")
end def index(conn, _params) do conn |> assign(:quotes, Repo.all(Splurty.Quote)) |> render("index.html") end def new(conn, _params) do render conn, "new.html" end def create(conn, %{"quote" => %{"saying" => saying, "author" => author}}) do q = %Splurty.Quote{saying: saying, author: author} Repo.insert(q) redirect conn, to: quote_path(conn, :index) end def show(conn, %{"id" => id}) do {id, _} = Integer.parse(id) conn |> assign(:quote, Repo.get(Splurty.Quote, id)) |> render("show.html") end def edit(conn, %{"id" => id}) do {id, _} = Integer.parse(id) conn |> assign(:quote, Repo.get(Splurty.Quote, id)) |> render("edit.html") end def update(conn, %{"id" => id, "quote" => %{"saying" => saying, "author" => author}}) do {id, _} = Integer.parse(id) q = Repo.get(Splurty.Quote, id) q = %{q | saying: saying, author: author } Repo.update(q) redirect conn, to: quote_path(conn, :show, q.id) end def destroy(conn, %{"id" => id}) do {id, _} = Integer.parse(id) q = Repo.get(Splurty.Quote, id) Repo.delete(q) redirect conn, to: quote_path(conn, :index) end end

Save the file.

When you navigate to localhost:4000, you'll notice a random quote is randomly shown. Sweet!

We should cleanup after ourselves and delete the file homepage.html.eex, since we're no longer using it. Run this command:

$ rm web/templates/quote/homepage.html.eex

Your application should look just like this at this step.

Next Lesson