Multiple routes within the same controller

Questions / Help
1

I’m not really sure how I should be thinking about this but here is what I have and want to achieve:
A page listing the State for each occurrence in the database where each occurence is a link to a page listing the cities which match the state which link to a list of the stores in that city which link to the data within each store.
So you click on a button, Idaho, which takes you to a button, Boise, which takes you to a button, Store, which will link you to the information of that given store.

schema "stores" do
  field :city, :string
  field :name, :string
  field :state, :string
end

def list_store_state do
  query = Ecto.Query.from(s in Store, select: s.state, distinct: true)
  Repo.all(query)
end

def list_store_city(id) do
  query = Ecto.Query.from(s in Store, where: s.state == ^id, select: s.city, distinct: true)
  Repo.all(query)
end

def list_city_store(id) do
  query = Ecto.Query.from(s in Store, where: [s.city] == ^id, select: s.name)
  Repo.all(query)
end

def index(conn, _params) do
  stores = Stores.list_store_state()
  render(conn, "index.html", stores: stores)
end

<%= for store <- @stores do %>
  <%= link store, to: Routes.page_path(@conn, :show, store) %>
<% end %>

def show(conn, %{"id" => id}) do
  stores = Stores.list_store_city(id)
  render(conn, "show.html", stores: stores)
end

is the idea for the flow but I’m not sure how to define another :show function within the same controller. Is there a better way to be passing this along?

2

I’ve gone with some nested associations to just show everything on the index page:

def index(conn, _params) do
  stores = Stores.list_store_state()
  cities = Stores.list_store_city(stores)
  store = Stores.list_city_store(cities)
  render(conn, "index.html", stores: stores, store: store, cities: cities)
end

<%= for stores <- @stores do %>
  <%= stores %>
    <%= for city <- @cities do %>
      <%= city %>
        <%= for store <- @store do %>
          <%= link store, to: Routes.page_path(@conn, :show, store) %>
        <% end %>
    <% end %>
<% end %>

and

def show(conn, %{"id" => id}) do
  store = Repo.get_by!(Store, name: id) |> Repo.preload([:siteinfo, :internetconnections])
  render(conn, "show.html", store: store)
end
3

It really shouldn’t be necessary to hit the database three times. Example:

# file: music_db/priv/repo/playground.exs
#
# http://www.pragmaticprogrammer.com/titles/wmecto
# https://pragprog.com/titles/wmecto/source_code
# http://media.pragprog.com/titles/wmecto/code/wmecto-code.zip
#
# pg_ctl -D /usr/local/var/postgres start
# mix format ./priv/repo/playground.exs
# mix run ./priv/repo/playground.exs
#

defmodule AppInfo do
  def string() do
    Application.loaded_applications()
    |> Enum.map(&to_app_keyword/1)
    |> Enum.sort_by(&map_app_name/1)
    |> Enum.map_join(", ", &app_keyword_to_string/1)
  end

  defp to_app_keyword({app, _, vsn}),
    do: {app, vsn}

  defp app_keyword_to_string({app, vsn}),
    do: Atom.to_string(app) <> ": " <> to_string(vsn)

  defp map_app_name({app, _}),
    do: app
end

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Artist, Album, Track}

  def track_name(%{title: title}),
    do: "Track: " <> title

  def album_name(%{album_title: album_title}),
    do: "Album: " <> album_title

  def artist_name(%{name: name}),
    do: "Artist: " <> name

  def artist_close(names, nil),
    do: names

  def artist_close(names, name),
    do: ["End artist: " <> name | names]

  def album_close(names, nil),
    do: names

  def album_close(names, title),
    do: ["End album: " <> title | names]

  def to_name(
        %{artist_id: artist_id, album_id: album_id} = track_info,
        {{album_id, _} = last_album, {artist_id, _} = last_artist, names}
      ) do
    {
      last_album,
      last_artist,
      [track_name(track_info) | names]
    }
  end

  def to_name(
        %{artist_id: artist_id} = track_info,
        {{_, last_title}, {artist_id, _} = last_artist, names}
      ) do
    {
      {track_info.album_id, track_info.album_title},
      last_artist,
      [
        track_name(track_info),
        album_name(track_info)
        | album_close(names, last_title)
      ]
    }
  end

  def to_name(track_info, {{_, last_title}, {_, last_name}, names}) do
    closed_names =
      names
      |> album_close(last_title)
      |> artist_close(last_name)

    {
      {track_info.album_id, track_info.album_title},
      {track_info.artist_id, track_info.name},
      [
        track_name(track_info),
        album_name(track_info),
        artist_name(track_info)
        | closed_names
      ]
    }
  end

  def query,
    do:
      from(a in Artist,
        left_join: b in Album,
        on: a.id == b.artist_id,
        left_join: t in Track,
        on: b.id == t.album_id,
        order_by: [asc: a.name, asc: b.title, asc: t.index],
        select: %{
          artist_id: a.id,
          name: a.name,
          album_id: b.id,
          album_title: b.title,
          track_id: t.id,
          title: t.title
        }
      )

  def play do
    IO.puts(AppInfo.string())

    {{_, last_title}, {_, last_name}, results} =
      query()
      |> Repo.all()
      |> List.foldl({{nil, nil}, {nil, nil}, []}, &to_name/2)

    results
    |> album_close(last_title)
    |> artist_close(last_name)
    |> :lists.reverse()
  end
end

IO.inspect(Playground.play())

$ mix run ./priv/repo/playground.exs
asn1: 5.0.7, compiler: 7.2.7, connection: 1.0.4, crypto: 4.3.3, db_connection: 2.0.3, decimal: 1.6.0, ecto: 3.0.5, ecto_sql: 3.0.3, elixir: 1.7.4, hex: 0.18.2, inets: 7.0.2, kernel: 6.1.1, logger: 1.7.4, mix: 1.7.4, music_db: 0.1.0, poison: 3.1.0, postgrex: 0.14.1, public_key: 1.6.3, ssl: 9.0.3, stdlib: 3.6, telemetry: 0.2.0

17:56:53.364 [debug] QUERY OK source="artists" db=1.3ms decode=0.6ms queue=1.4ms
SELECT a0."id", a0."name", a1."id", a1."title", t2."id", t2."title" 
FROM "artists" AS a0 
  LEFT OUTER JOIN "albums" AS a1 ON a0."id" = a1."artist_id" 
  LEFT OUTER JOIN "tracks" AS t2 ON a1."id" = t2."album_id" 
  ORDER BY a0."name", a1."title", t2."index" []
["Artist: Bill Evans", "Album: Portrait In Jazz",
 "Track: Come Rain Or Come Shine", "Track: Autumn Leaves", "Track: Witchcraft",
 "Track: When I Fall In Love", "Track: Peri's Scope",
 "Track: What Is This Thing Called Love?", "Track: Spring Is Here",
 "Track: Someday My Prince Will Come", "Track: Blue In Green",
 "End album: Portrait In Jazz", "Album: You Must Believe In Spring",
 "Track: B Minor Waltz (for Ellaine)", "Track: You Must Believe In Spring",
 "Track: Gary's Theme", "Track: We Will Meet Again (for Harry)",
 "Track: The Peacocks", "Track: Sometime Ago",
 "Track: Theme From M*A*S*H (Suicide Is Painless)", "Track: Without a Song",
 "Track: Freddie Freeloader", "Track: All of You",
 "End album: You Must Believe In Spring", "End artist: Bill Evans",
 "Artist: Bobby Hutcherson", "Album: Live At Montreaux", "Track: Anton's Ball",
 "Track: The Moontrane", "Track: Farallone", "Track: Song Of Songs",
 "End album: Live At Montreaux", "End artist: Bobby Hutcherson",
 "Artist: Miles Davis", "Album: Cookin' At The Plugged Nickel",
 "Track: If I Were A Bell", "Track: Stella By Starlight", "Track: Walkin'",
 "Track: Miles", "Track: No Blues", "End album: Cookin' At The Plugged Nickel",
 "Album: Kind Of Blue", "Track: So What", "Track: Freddie Freeloader",
 "Track: Blue In Green", "Track: All Blues", "Track: Flamenco Sketches",
 "End album: Kind Of Blue", "End artist: Miles Davis"]
$