SpaceRocket |> Blog

Think, Learn, Share

Michael Chavez
Michael Chavez

Order Post by Date with Elixir and Ecto

Posted by Michael Chavez on October 6, 2019

Table of contents:

  1. Create Example Application
  2. Add dependencies
  3. Create posts context model
  4. Seed Database
  5. Remove posts from database
  6. Customize iex
  7. Raw SQL using Ecto.Adapters
  8. RAW SQL using Ecto shorthand
  9. Query by date using Ecto Schemas
  10. Add Order By Date Function to Context
  11. Add to controller
  12. Thats all folks!

Create Example Application

  1. mix phx.new order_post_by_date_example
  2. cd order_post_by_date_example && mix ecto.create
  3. git init && git add --all && git commit -m "initial commit"

Add dependencies

mix.exs

  def application do
    [
      ...
      extra_applications: [..., :timex]
    ]
  end

  defp deps do
    [
      ...
      {:timex, "~> 3.5"}
    ]
  end
  • mix.deps get

Create posts context model

  1. mix phx.gen.html PostTypes Post posts title:string date:utc_datetime
  2. Add:resources "/posts", PostController to lib/order_post_by_date_example_web/router.ex
  3. mix ecto.migrate

https://hexdocs.pm/phoenix/Mix.Tasks.Phx.Gen.Html.html#content

Seed Database

priv/repo/seeds.exs

alias OrderPostByDateExample.Repo

Repo.insert_all("posts",
    [
        [title: "Latest Update", date: DateTime.utc_now(), inserted_at: DateTime.utc_now(), updated_at: DateTime.utc_now()],
        [title: "Hello World", date: Timex.shift(DateTime.utc_now(), months: -14, hours: 2, minutes: 13), inserted_at: DateTime.utc_now(), updated_at: DateTime.utc_now()],
        [title: "A Post from 2 Hours Ago...", date: Timex.shift(DateTime.utc_now(), hours: 2, minutes: 13), inserted_at: DateTime.utc_now(), updated_at: DateTime.utc_now()],   
        [title: "Last Month's Post", date: Timex.shift(DateTime.utc_now(), months: -1, hours: 2, minutes: 13), inserted_at: DateTime.utc_now(), updated_at: DateTime.utc_now()],
        [title: "A Couple Days Ago Post", date: Timex.shift(DateTime.utc_now(), days: -2, hours: 2, minutes: 13), inserted_at: DateTime.utc_now(), updated_at: DateTime.utc_now()]
    ])

Or we can do it using map:

Repo.insert_all("posts", [
    %{title: "Latest Post", date: DateTime.utc_now(), inserted_at: DateTime.utc_now(), updated_at: DateTime.utc_now()}
])

Or if we don’t want to have to deal with adding the defaults, updated_at, and inserted_at, we can import the PostTypes context and call create_post

alias OrderPostByDateExample.PostTypes



post_data = [
  %{
    title: "Post 2",
    date: Timex.shift(DateTime.utc_now(), months: -15, hours: 2, minutes: 13)
  },
  %{
    title: "Post 1",
    date: Timex.shift(DateTime.utc_now(), months: -14, hours: 2, minutes: 13)
  },
  %{
    title: "Post 5",
    date: Timex.shift(DateTime.utc_now(), months: -18, hours: 2, minutes: 13)
  },
  %{
    title: "Post 4",
    date: Timex.shift(DateTime.utc_now(), months: -17, hours: 2, minutes: 13)
  },
  %{
    title: "Post 3",
    date: Timex.shift(DateTime.utc_now(), months: -16, hours: 2, minutes: 13)
  },
  %{
    title: "Post 6",
    date: Timex.shift(DateTime.utc_now(), months: -20, hours: 2, minutes: 13)
  }
]

Enum.each(post_data, fn(data) ->
    PostTypes.create_post(data)
end)

More information:

Remove posts from database

iex -S mix

alias OrderPostByDateExample.{Repo, PostTypes}

Repo.delete_all("posts")

Now we have our database populated with posts, we can now order them by date.

Customize iex

.iex.exs

import_if_available Ecto.Query

alias OrderPostByDateExample.{
    Repo,
    PostTypes,
    PostTypes.Post
}

Now we don’t have to alias our modules everytime we use iex.😸

Raw SQL using Ecto.Adapters

Ecto.Adapters.SQL.query(Repo, "SELECT * FROM posts ORDER BY date ASC")

RAW SQL using Ecto shorthand

Repo.query("SELECT * FROM posts ORDER BY date ASC")

With schema-less queries, we manually need to select all the fieldss you want returned since Ecto favors explicitness over implicitness.

Query by date using Keyword binding

query = from(p in Post, order_by: [asc: :date])

Repo.all(query)

Add Order By Date Function to Context

The default list post function looks like this:

  def list_posts do
    Repo.all(Post)
  end

We can use that as an idea on how we are going to do the order_by_date function. We are over half way there!

Using query:

  def order_by_desc_date do
    query = from(p in Post, order_by: [asc: :date])
    Repo.all(query)
  end

Or the cool kid Elixir way, using pipes

  def order_by_desc_date do
    Post 
    |> order_by(desc: :date) 
    |> Repo.all()
  end

Lets call it in IEX:

iex -S mix

PostTypes.order_by_desc_date()

Add to controller

Last step is to add to our post controller index function:

  def index(conn, _params) do
    posts = PostTypes.order_by_desc_date() 
    render(conn, "index.html", posts: posts)
  end

Thats all folks!

That was fun and easy. We did some SQL queries and turned them into Ecto Queries. For improvements we could introduce Phoenix LiveView to sort by ascending or descending in a real time UI. Will save that for a later day!