Create query to add to filter controller

gilbertosj

I currently have an index on the controller, as follows

  def index(conn, params) do
    cdr = Structure.list_cdr()

    page = Structure.list_cdr
        |> order_by([c], desc: c.calldate)
        |> Repo.paginate(params)

    filter = Structure.list_cdr()
      |> where([c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
      |> order_by([c], desc: c.calldate)
      |> Repo.paginate(params)
      
    render(conn, "index.html", cdr: page.entries, page: page, filter: page)
  end

Notice that I added ^ date_start is ^ date_end

I want to create a

  defp date_start do
    
  end

  defp date_end do
    
  end

In the same controller.
Can someone help me with this?

This date_start is date_end would be to pick up from
“Template”


<%= form_for @conn, Routes.ecdr_path(@conn, :index), [method: :get], fn f -> %>
  <%= date_input(:calldate, :date_start, class: "form-control", id: "inlineFormInputGroupDatestart", placeholder: "Data") %>
  <%= date_input(:calldate, :date_end, class: "form-control", id: "inlineFormInputGroupDateend", placeholder: "Data") %>
<% end %>

6 comments

/phoenix #ecto

0 1594 6

2019-06-18 02:13:34 UTC

Showing Posts 1 to 6

Show Best Posts
Show All (oldest first)
Show All (newest first)

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

Those will come in from the params, you need to grab those values out of the params and parse them into date times. Try doing IO.inspect(params) to see what it is.

Post #1

NobbZ

Please keep this discussion in Recognize new params in the template, it seems to be the same problem you have there but phrased differently.

Post #2

gilbertosj

For the sake of simplicity, the question has been rephrased.

If anyone can help me, I’ll be grateful.

Post #3

gilbertosj

I made the following modification to accept the parameters …

When I pass no value, it returns me the following error

[debug] Processing with GcallWeb.EcdrController.index/2
Parameters: %{}
Pipelines: [:browser]
[debug] QUERY OK source=“cdr” db=0.2ms
SELECT count(‘*’) FROM cdr AS c0
[debug] QUERY OK source=“cdr” db=0.2ms
SELECT c0.calldate, c0.accountcode, c0.amaflags, c0.billsec, c0.channel, c0.clid, c0.dcontext, c0.disposition, c0.dst, c0.dstchannel, c0.duration, c0.lastapp, c0.lastdata, c0.src, c0.uniqueid, c0.userfield FROM cdr AS c0 ORDER BY c0.calldate DESC LIMIT ? OFFSET ? [10, 0]
[info] Sent 500 in 8ms
[error] pid<0.437.0> running GcallWeb.Endpoint (connection pid<0.436.0>, stream id 1) terminated
Server: 172.25.8.12:4000 (http)
Request: GET /relatorios
** (exit) an exception was raised:
** (Protocol.UndefinedError) protocol Enumerable not implemented for #Ecto.Query<from e0 in Gcall.Structure.Ecdr, where: fragment(“? BETWEEN ? AND ?”, e0.calldate, ^[1], ^[2]), order_by: [desc: e0.calldate], select: %{calldate: e0.calldate, accountcode: e0.accountcode, amaflags: e0.amaflags, billsec: e0.billsec, channel: e0.channel, clid: e0.clid, dcontext: e0.dcontext, disposition: e0.disposition, dst: e0.dst, dstchannel: e0.dstchannel, duration: e0.duration, lastapp: e0.lastapp, lastdata: e0.lastdata, src: e0.src, uniqueid: e0.uniqueid, userfield: e0.userfield}>. This protocol is implemented for: Ecto.Adapters.SQL.Stream, DBConnection.PrepareStream, DBConnection.Stream, Scrivener.Page, Function, Map, File.Stream, HashSet, Range, IO.Stream, HashDict, Stream, MapSet, List, Date.Range, GenEvent.Stream
(elixir) /home/build/elixir/lib/elixir/lib/enum.ex:1: Enumerable.impl_for!/1
(elixir) /home/build/elixir/lib/elixir/lib/enum.ex:141: Enumerable.reduce/3
(elixir) lib/enum.ex:3015: Enum.reduce/3
(gcall) lib/gcall_web/templates/ecdr/index.html.eex:21: GcallWeb.EcdrView.“index.html”/1
(gcall) lib/gcall_web/templates/layout/app.html.eex:21: GcallWeb.LayoutView.“app.html”/1
(phoenix) lib/phoenix/view.ex:399: Phoenix.View.render_to_iodata/3
(phoenix) lib/phoenix/controller.ex:729: Phoenix.Controller.put_render/5
(phoenix) lib/phoenix/controller.ex:746: Phoenix.Controller.instrument_render_and_send/4
(gcall) lib/gcall_web/controllers/ecdr_controller.ex:1: GcallWeb.EcdrController.action/2
(gcall) lib/gcall_web/controllers/ecdr_controller.ex:1: GcallWeb.EcdrController.phoenix_controller_pipeline/2
(gcall) lib/gcall_web/endpoint.ex:1: GcallWeb.Endpoint.instrument/4
(phoenix) lib/phoenix/router.ex:275: Phoenix.Router.call/1
(gcall) lib/gcall_web/endpoint.ex:1: GcallWeb.Endpoint.plug_builder_call/2
(gcall) lib/plug/debugger.ex:122: GcallWeb.Endpoint.“call (overridable 3)”/2
(gcall) lib/gcall_web/endpoint.ex:1: GcallWeb.Endpoint.call/2
(phoenix) lib/phoenix/endpoint/cowboy2_handler.ex:33: Phoenix.Endpoint.Cowboy2Handler.init/2
(cowboy) /home/gilberto/Testes/Elixir/gcall/deps/cowboy/src/cowboy_handler.erl:41: :cowboy_handler.execute/2
(cowboy) /home/gilberto/Testes/Elixir/gcall/deps/cowboy/src/cowboy_stream_h.erl:296: :cowboy_stream_h.execute/3
(cowboy) /home/gilberto/Testes/Elixir/gcall/deps/cowboy/src/cowboy_stream_h.erl:274: :cowboy_stream_h.request_process/3
(stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3

controller:

  def index(conn, params) do
    cdr = Structure.list_cdr()

    page = Structure.list_cdr
        |> order_by([c], desc: c.calldate)
        |> Repo.paginate(params)

    date_start = [params["date_start"]]
    date_end = [params["date_end"]]
    filter = Structure.list_cdr()

     |> where([c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
      |> order_by([c], desc: c.calldate)
#      |> Repo.paginate(params)

    render(conn, "index.html", cdr: page.entries, page: page, cdr: filter)
  end

nil ↩︎
nil ↩︎

Post #4

NobbZ

The error suggest that you never query the database, but instead try to iterate over the query, probably in the template you want to render.

Post #5

gilbertosj

I managed to do, to query database perfectly.

More when I execute “submit the template”, it does not load my filtered information.

I see that it is a small detail that I am letting go …
can you help me?

Controller:

  def index(conn, params) do
    
    cdr = Structure.list_cdr()

    page = Structure.list_cdr
        |> order_by([c], desc: c.calldate)
        |> Repo.paginate(params)

    date_start = get_in(params, ["date_start"])
    date_end = get_in(params, ["date_end"])
    
    filter = Structure.list_cdr()
 
     |> where([c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
     |> order_by([c], desc: c.calldate)
     |> Repo.paginate(params)
      
    render(conn, "index.html", cdr: cdr, cdr: page.entries, page: page, filter: filter)
  end

Template
index.html

<%= render "filter.html", Map.put(assigns, :action, Routes.ecdr_path(@conn, :index)) %>

Template
filter.html

<%= form_for @conn, Routes.ecdr_path(@conn, :index), [method: :get], fn f -> %>
<%= date_input(f, :date_start, class: "form-control", id: "inlineFormInputGroupDatestart", placeholder: "Data") %>
<%= date_input(f, :date_end, class: "form-control", id: "inlineFormInputGroupDateend", placeholder: "Data") %>
<% end %>

Debug:

[info] GET /relatorios
[debug] Processing with GcallWeb.EcdrController.index/2
Parameters: %{“_utf8” => “✓”, “date_end” => “2019-06-08”, “date_start” => “2019-06-01”, “dcontext” => %{“contexto” => “”}, “disposition” => %{“status” => “Todas”}, “dst” => %{“destino” => “”}, “src” => %{“origem” => “”}}
Pipelines: [:browser]
[debug] QUERY OK source=“cdr” db=12.1ms
SELECT count(‘‘) FROM cdr AS c0 []
[debug] QUERY OK source=“cdr” db=0.3ms
SELECT c0.calldate, c0.accountcode, c0.amaflags, c0.billsec, c0.channel, c0.clid, c0.dcontext, c0.disposition, c0.dst, c0.dstchannel, c0.duration, c0.lastapp, c0.lastdata, c0.src, c0.uniqueid, c0.userfield FROM cdr AS c0 ORDER BY c0.calldate DESC LIMIT ? OFFSET ? [10, 0]
[debug] QUERY OK source=“cdr” db=0.5ms
SELECT count(’’) FROM cdr AS c0 WHERE (c0.calldate BETWEEN ? AND ?) [“2019-06-01”, “2019-06-08”]
[debug] QUERY OK source=“cdr” db=0.5ms
SELECT c0.calldate, c0.accountcode, c0.amaflags, c0.billsec, c0.channel, c0.clid, c0.dcontext, c0.disposition, c0.dst, c0.dstchannel, c0.duration, c0.lastapp, c0.lastdata, c0.src, c0.uniqueid, c0.userfield FROM cdr AS c0 WHERE (c0.calldate BETWEEN ? AND ?) ORDER BY c0.calldate DESC LIMIT ? OFFSET ? [“2019-06-01”, “2019-06-08”, 10, 0]
[info] Sent 200 in 15ms
[info] Replied phoenix:live_reload :ok