Problem in paging, table without id

Phoenix Forum Questions / Help
, ,
1

Sirs I’m trying to paginate, but I’m getting the following error.
A small note: There is no id column in the database.
Because this is an external query, I can not give mix ecto.create

Below I will show my files.

Error:

[info] Sent 500 in 1315ms
[error] #PID<0.447.0> running GcallWeb.Endpoint (connection #PID<0.433.0>, stream id 4) terminated
Server: localhost:4000 (http)
Request: GET /relatorios
** (exit) an exception was raised:
** (Protocol.UndefinedError) protocol Scrivener.Paginater not implemented for [%{accountcode: “”, amaflags: 3, …

struture

  def list_cdr do
  query_list_cdr = from e in Ecdr,
  select: %{calldate: e.calldate, accountcode: e.accountcode, amaflags: e.amaflags, billsec: e.billsec, channel: e.channel, clid: e.clid, dcontext: e.dcontext, disposition: e.disposition, dst: e.dst, dstchannel: e.dstchannel, duration: e.duration, lastapp: e.lastapp, lastdata: e.lastdata, src: e.src, uniqueid: e.uniqueid, userfield: e.userfield}

  Repo.all(query_list_cdr)
  end

view

defmodule GcallWeb.EcdrView do
  use GcallWeb, :view
  import Scrivener.HTML
end

controller

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

    page = Structure.list_cdr
        |> Repo.paginate(params)

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

  end

config

config :scrivener_html,
  routes_helper: GcallWeb.Router.Helpers

Template
<%= pagination_links @page, view_style: :bootstrap_v4, next: "Avançar", previous: "Voltar" %>

The only problem that happened was in this page, all the other pages that consult another table, which contains “id” works perfectly.

2

The error message doesn’t seem to relate to whether or not there is an :id column.

You would need to put the call to paginate before Repo.all I think since the pagination for an ecto query is implemented as a sub-query.

If you want to paginate after the query has returned - which what you are currently doing - then you would use the Scrivener.List package.

Just remove the call to Repo.all/1 and you should be good to go.

1 Like
3

Using your code:

def list_cdr do
  from e in Ecdr,
  select: %{
     calldate: e.calldate, accountcode: e.accountcode, amaflags: e.amaflags, 
     billsec: e.billsec, channel: e.channel, clid: e.clid, dcontext: e.dcontext, 
     disposition: e.disposition, dst: e.dst, dstchannel: e.dstchannel, 
     duration: e.duration, lastapp: e.lastapp, lastdata: e.lastdata, 
     src: e.src, uniqueid: e.uniqueid, userfield: e.userfield
  }

# Remove this line
# Repo.all(query_list_cdr)
end
1 Like
4

One final thought. Your query has no :order expression. Therefore the rows will be returned in a non-guaranteed order. Which will affect your pagination.

Also note that Scrivener does an offset/limit query which has its own challenges, especially on large tables in Postgres. It also can create confusing results if rows are inserted ahead of the current “page”.

You might consider reading the following for ideas and alternatives:

2 Likes
5

…, which also have their tradeoffs just like offset/limit based pagination :slight_smile:

6

Agreed- each strategy has its pros and cons.

I usually prefer, for forward pagination, a simple where unique_sequence_id > id_of_last_row_of_current_page limit page_size but there isn’t an obvious candidate in the provided query (as the author noted)

2 Likes
7

sorry for the delay,
everything went well.
Thank you