query_controller.ex

defmodule MyApp.EventController do
  use MyApp.Web, :controller
  import Ecto.Query

  alias MyApp.Event
  alias MyApp.EventSerializer

  plug :scrub_params, "event" when action in [:create, :update]

  def index(conn, params) do
    events = create_query(params)
    |> Repo.all
    |> EventSerializer.format(conn)

    json(conn, events)
  end

  def show(conn, %{"id" => id}) do
    event = Repo.get!(Event, id)
    |> EventSerializer.format(conn)

    json(conn, event)
  end

  defp create_query(params) do
    q = from e in Event, select: e
    list = params
    |> Map.delete("format")
    |> Map.to_list
    create_query(q, list)
  end

  defp create_query(q, []), do: q

  defp create_query(q, [{attr, value}|tail]) when attr == "month" or attr == "year" do
    q = from e in q, where: fragment("date_part(?, ?) = ?", ^attr, e.start_time, type(^value, :integer))
    create_query(q, tail)
  end

  defp create_query(q, [{"related_event_id", event_id}|tail]) do
    q = from e in q,
      where: e.id != ^event_id and fragment("""
        ? in (
          select event_goals.event_id
          from event_goals
          where event_goals.goal_id in (
            select event_goals.goal_id
            from event_goals
            where event_goals.event_id = ?
          ))
      """, e.id, type(^event_id, :integer))
    create_query(q, tail)
  end

  defp create_query(q, [{"limit", limit}|tail]) do
    q = from q, limit: ^limit
    create_query(q, tail)
  end

  defp create_query(q, [{"tags", tags}|tail]) do
    q = from e in q,
      join: g in assoc(e, :goals),
      where: g.type in ^String.split(tags, ",")
    create_query(q, tail)
  end

  defp create_query(q, [{attr, value}|tail]) do
    q = from e in q, where: field(e, ^String.to_atom(attr)) == ^value
    create_query(q, tail)
  end
end