Dynamic field source for order_by

Hi,
How can I insert dynamic source for order_by in ecto query in the case like below?

pos = case Repo.all(
  from p in PO, 
  where: p.is_active == true,
  join: c in assoc(p, :customer),
  limit: ^10,
  offset: ^((String.to_integer(page)-1) * 10),
  order_by: [{^sortBy, ^orderBy}],
  select: %{
    id: p.id, 
    number: p.number, 
    notes: p.notes, 
    customer_name: c.name, 
    customer_id: p.customer_id, 
    po_date: p.po_date
  }) do
    nil ->
      "Data not found"
    [] -> 
      "Data not found"
    p ->
      p
    _ -> 
      "Data not found"
  end

I want to dynamically change ^orderBy to be p.number or c.name. I only know it can do like this field(p, :number)
We can change :number to be like ^number to get outside variable, but I have no idea how to dynamically change source (p) to (c)

1 Like

Compose your query dynamically:

iex(1)> alias MusicDB.{Album,Track}
[MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> album_id = 2
2
iex(4)> query = from(a in Album, [
...(4)>   join: t in Track, on: a.id == t.album_id,
...(4)>   where: a.id == ^album_id,
...(4)>   select: %{
...(4)>    a: a.id,
...(4)>    t: t.id
...(4)>   }
...(4)> ])
#Ecto.Query<from a in MusicDB.Album, join: t in MusicDB.Track,
 on: a.id == t.album_id, where: a.id == ^2, select: %{a: a.id, t: t.id}>
iex(5)> query1 = order_by(query, [a,t], asc: a.id, desc: t.id) 
#Ecto.Query<from a in MusicDB.Album, join: t in MusicDB.Track,
 on: a.id == t.album_id, where: a.id == ^2, order_by: [asc: a.id, desc: t.id],
 select: %{a: a.id, t: t.id}>
iex(6)> query2 = order_by(query, [a,t], asc: t.id) 
#Ecto.Query<from a in MusicDB.Album, join: t in MusicDB.Track,
 on: a.id == t.album_id, where: a.id == ^2, order_by: [asc: t.id],
 select: %{a: a.id, t: t.id}>
iex(7)> Repo.all(query1)

12:52:16.623 [debug] QUERY OK source="albums" db=2.2ms
SELECT a0."id", t1."id" FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON a0."id" = t1."album_id" WHERE (a0."id" = $1) ORDER BY a0."id", t1."id" DESC [2]
[%{a: 2, t: 10}, %{a: 2, t: 9}, %{a: 2, t: 8}, %{a: 2, t: 7}, %{a: 2, t: 6}]
iex(8)> Repo.all(query2)

12:52:18.568 [debug] QUERY OK source="albums" db=3.3ms
SELECT a0."id", t1."id" FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON a0."id" = t1."album_id" WHERE (a0."id" = $1) ORDER BY t1."id" [2]
[%{a: 2, t: 6}, %{a: 2, t: 7}, %{a: 2, t: 8}, %{a: 2, t: 9}, %{a: 2, t: 10}]
iex(9)> 
3 Likes

Hi Peerreynders,

Yes we can create couple of similar query and change this where: a.id == ^album_id to other parameter.

What I am looking for a simpler / smarter way that can only make the variable a.id to be dynamic. For example t.name (from table Track).

So instead of where: a.id == ^album_id we may only need to write where: ^sParam == ^sValue

Here, sParam can be a.id or t.name
Then, sValue can be ‘My favorite track’ for t.name or 23 for a.id

Is this possible to make it simpler like this?

To reiterate - the typical solution is to compose your query from its various parts, e.g.:

query = from(a in Album, [
  join: t in Track, on: a.id == t.album_id,
  select: %{
    a: a.id,
    t: t.id
  }
])

new_query = if(some_predicate) do
    where(query, [a,t], a.id == ^album_id)
  else
    where(query, [a,t], t.name == ^track_name)
  end

Repo.all(new_query)

In what way isn’t that “simple” or “smart”?

You aren’t as such creating multiple queries - you are building your final query step by step.

1 Like

Yes, this is smarter and simpler because we only need to make one line dynamic parameter. At first I thought I need to rewrite all “query” variable and change a little parameter inside.

I have tried your solution like below
qr = from p in PO,
join: c in assoc(p, :customer),
limit: ^10,
offset: ^((String.to_integer(page)-1) * 10),
order_by: [{^sortBy, field(p, ^orderBy)}],
select: %{id: p.id,
number: p.number,
notes: p.notes,
customer_name: c.name,
customer_id: p.customer_id,
po_date: p.po_date
}

      new_query = where(qr,[p, c], p.is_active == true)

      pos = case Repo.all(new_query) do
          nil -> "Data not found"
          [] -> "Data not found"
          p -> p
          _ -> "Data not found"
      end

But I got undefined function c/0. Am I missing something?

I suspect you aren’t showing enough of the code/error to see what the actual problem is.

For example limit: ^10, should be limit: 10, - the ^ is only used in front of identifier names to pin the value, i.e. prevent Elixir from rebinding a new value to it (the pin operator).

I also think that

 offset: ^((String.to_integer(page)-1) * 10),

should be

 offset: ((String.to_integer(^page)-1) * 10),

personally would I prefer

 offset = ((String.to_integer(page)-1) * 10)
...
 offset: ^offset,

Also given that macros are involved don’t skip the parenthesis for from

qr = from( p in PO,
  join: c in assoc(p, :customer),
...
  })

Try to gradually put together a working query in IEx (like I did above) one little bit at a time.

we only need to make one line dynamic parameter.

Ecto makes heavy use of macros which are resolved at compile time - so bindings against the (static) schema are usually fixed at compile time. What is typically resolved at runtime are the values that are used in the query. Dynamically varying bindings against the schema are typically implemented by dynamically varying the query composition.

2 Likes

Hi Peerreynders

Yes, I change some code based on your suggestion (about offset and limit), and below is the complete code
def getListPO(conn, params) do
page = params[“page”]

  orderBy = case params["orderBy"] do
    "Keterangan" -> :notes
    "Tanggal PO" -> :po_date
  end

  sortBy = if params["sortBy"] == "desc", do: :desc, else: :asc
  offset = ((String.to_integer(page)-1) * 10)
  pos = case Repo.all(from p in PO, where: p.is_active == true,
            join: c in assoc(p, :customer),
            limit: 10,
            offset: ^offset,
            order_by: [{^sortBy, field(p, ^orderBy)}],
            select: %{id: p.id, 
            number: p.number, 
            notes: p.notes, 
            customer_name: c.name, 
            customer_id: p.customer_id, 
            po_date: p.po_date
          }) do
          nil -> "Data not found"
          [] -> "Data not found"
          p -> p
          _ -> "Data not found"
  end
  
  render(conn, "pos.json", pos: pos)

end

Using above code is work but I tried to put where: p.is_active == true like you mentioned below it cause and error

qr =  from (p in PO,
            join: c in assoc(p, :customer),
            limit: 10,
            offset: offset,
            order_by: [{^sortBy, field(p, ^orderBy)}],
            select: %{id: p.id, 
              number: p.number, 
              notes: p.notes, 
              customer_name: c.name, 
              customer_id: p.customer_id, 
              po_date: p.po_date
            })
      
      new_query = where(qr,[p, c], p.is_active == true)

      pos = case Repo.all(new_query) do
          nil -> "Data not found"
          [] -> "Data not found"
          p -> p
          _ -> "Data not found"
      end

Below is the error message produced

    Compiling 1 file (.ex)
warning: variable "p" does not exist and is being expanded to "p()", please use parentheses to remove the ambiguity or change the variable name
  lib/sgm_web/controllers/po_controller.ex:118

warning: variable "c" does not exist and is being expanded to "c()", please use parentheses to remove the ambiguity or change the variable name
  lib/sgm_web/controllers/po_controller.ex:118

warning: variable "p" does not exist and is being expanded to "p()", please use parentheses to remove the ambiguity or change the variable name
  lib/sgm_web/controllers/po_controller.ex:118

== Compilation error in file lib/sgm_web/controllers/po_controller.ex ==
** (CompileError) lib/sgm_web/controllers/po_controller.ex:118: undefined function c/0
    (stdlib) lists.erl:1338: :lists.foreach/2
    (stdlib) erl_eval.erl:670: :erl_eval.do_apply/6
    (elixir) lib/kernel/parallel_compiler.ex:198: anonymous fn/4 in Kernel.ParallelCompiler.spawn_workers/6

Am I missing something here?

Well, see this example:

iex(1)> alias MusicDB.{Album,Track}
[MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> album_id = 2
2
iex(4)> order_by = [asc: :duration] # atom OK for first source (Track)
[asc: :duration]
iex(5)> query = from(t in Track, [
...(5)>   join: a in Album, on: t.album_id == a.id,
...(5)>   select: %{
...(5)>     a: a.id,
...(5)>     t: t.id,
...(5)>     d: t.duration
...(5)>   },
...(5)>   limit: 3,
...(5)>   order_by: ^order_by
...(5)>   ])
#Ecto.Query<from t in MusicDB.Track, join: a in MusicDB.Album,
 on: t.album_id == a.id, order_by: [asc: t.duration], limit: 3,
 select: %{a: a.id, t: t.id, d: t.duration}>
iex(6)> query = Ecto.Query.where(query, [_y,z],  z.id == ^album_id)
#Ecto.Query<from t in MusicDB.Track, join: a in MusicDB.Album,
 on: t.album_id == a.id, where: a.id == ^2, order_by: [asc: t.duration],
 limit: 3, select: %{a: a.id, t: t.id, d: t.duration}>
iex(7)> Repo.all(query)

00:19:54.808 [debug] QUERY OK source="tracks" db=3.0ms
SELECT a1."id", t0."id", t0."duration" FROM "tracks" AS t0 INNER JOIN "albums" AS a1 ON t0."album_id" = a1."id" WHERE (a1."id" = $1) ORDER BY t0."duration" LIMIT 3 [2]
[%{a: 2, d: 754, t: 9}, %{a: 2, d: 774, t: 7}, %{a: 2, d: 896, t: 8}]
iex(8)> 

i.e.

iex(6)> query = Ecto.Query.where(query, [_y,z],  z.id == ^album_id)

So there must be something else in your code interfering with it.

In desperation I’d try

new_query = Ecto.Query.where(qr, [y, _z], y.is_active == true)

and see if that changes the error.

They are still the same :frowning:

More out of curiosity what versions of Ecto and Elixir are you running?

$ cat mix.exs
...

  defp deps do
    [
      {:postgrex, ">= 0.0.0"},
      {:ecto, "~> 2.2"},
      {:poison, "~> 3.1"}
    ]
  end

...

end
$ elixir --version
Erlang/OTP 21 [erts-10.0.7] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [hipe] [dtrace]

Elixir 1.7.3 (compiled with Erlang/OTP 21)
$ 

Hi Peerreynders,

Finally I came out with below code which is work

def getListPO(conn, params) do
  page = params["page"]

  posCount = case Repo.all(from p in PO, where: p.is_active == true,
            join: c in assoc(p, :customer),
            select: count(p.id)
            ) do
          nil -> 0
          [] -> 0
          p -> hd(p)
          _ -> 0
    end
  
  sortBy = if params["sortBy"] == "desc", do: :desc, else: :asc
  offset = ((String.to_integer(page)-1) * 10)
  
  qr_where = from p in PO, where: p.is_active == true, join: c in assoc(p, :customer)
  
  qr_sortBy = case params["orderBy"] do
    "Keterangan" -> from([p, c] in qr_where, order_by: [{^sortBy, p.notes}])
    "Tanggal PO" -> from([p, c] in qr_where, order_by: [{^sortBy, p.po_date}])
    "Kustomer" -> from([p, c] in qr_where, order_by: [{^sortBy, c.name}])
  end
  
  qr =  from([_p, _c] in qr_sortBy,
        limit: 10,
        offset: ^offset,
        select: %{id: _p.id, 
          number: _p.number, 
          notes: _p.notes, 
          customer_name: _c.name, 
          customer_id: _p.customer_id, 
          po_date: _p.po_date
        })
  
  pos = case Repo.all(qr) do
      nil -> "Data not found"
      [] -> "Data not found"
      p -> p
      _ -> "Data not found"
  end

  render(conn, "pos.json", pos: pos, posCount: posCount)
end

And thanks for your guidance :slight_smile:

1 Like

Great!

By the way: identifier names with a leading underscore (like _p) are meant to be ignored - it is an indicator that they are not being used. Naming Conventions - Underscore

And now have a look at this version (i.e. using Ecto to assemble the query in bits and pieces):

def getListPO(conn, params) do
  results =
    params
    |> extract_pos_params()
    |> list_pos_paginated()
    |> to_pos_result()

  render(conn, "pos.json", results)
end

# ==== Request/Response

@pos_page_length 10
@order_by_map %{
  "keterangan" => :keterangan,
  "tanggal po" => :tanggal_po,
  "kustomer" => :kustomer
}

defp extract_pos_params(request_params) do
  start =
    request_params
    |> Map.get("page")
    |> String.to_integer()
    |> page_start(@pos_page_length)

    add_order_params(
      %{
        start: start,
        max_count: @pos_page_length
      },
      request_params
    )
end

defp page_start(current_page, page_length),
  do: (current_page - 1) * page_length

defp add_order_params(params, request_params) do
  with {:ok, value} <- Map.fetch(request_params, "orderBy"),
       order_by_string <- String.downcase(value),
       {:ok, order_by} <- Map.fetch(@order_by_map, order_by_string) do
    add_order_params(params, order_by, extract_direction(request_params))
  else
    _ ->
      params
  end
end

defp add_order_params(params, order_by, direction) do
  params
  |> Map.put(:order_by, order_by)
  |> Map.put(:direction, direction)
end

defp extract_direction(request_params) do
  case request_params["sortBy"] do
    "desc" ->
      :desc

    _ ->
      :asc
  end
end

# [_|_] is list with at least one element
defp to_pos_result({[_ | _] = pos, pos_count}),
  do: [pos: pos, posCount: pos_count]

defp to_pos_result({_, pos_count}),
  do: [pos: "Data not found", posCount: pos_count]

# === Ecto/DB

defp list_pos_paginated(params) do
  query = pos_query()
  count = Repo.aggregate(query, :count, :id)

  pos =
    query
    |> order_query(params)
    |> offset_query(params.start, params.max_count)
    |> fields_query()
    |> Repo.all()

  {pos, count}
end

defp pos_query do
  from(p in PO,
    join: c in assoc(p, :customer),
    where: p.is_active == true
  )
end

defp order_query(query, params) do
  if Map.has_key?(params, :direction) do
    order_query(query, params.order_by, params.direction)
  else
    query
  end
end

defp order_query(query, :keterangan, direction),
  do: order_by(query, [p, _], [{^direction, p.notes}])

defp order_query(query, :tanggal_po, direction),
  do: order_by(query, [p, _], [{^direction, p.po_date}])

defp order_query(query, :kustomer, direction),
  do: order_by(query, [_, c], [{^direction, c.name}])

defp order_query(query, _, _),
  do: query

def offset_query(query, start, max_count) do
  query
  |> limit(^max_count)
  |> offset(^start)
end

def fields_query(query) do
  select(query, [p, c], %{
    id: p.id,
    number: p.number,
    notes: p.notes,
    customer_name: c.name,
    customer_id: p.customer_id,
    po_date: p.po_date
  })
end

Verified in principle with:

# file: code/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
# code/priv/repo/playground.exs
#
# pg_ctl -D /usr/local/var/postgres start
# mix run ./priv/repo/playground.exs
#
defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Track}

  def play do
    # params = %{"page" => "3", "sortBy" => "desc"}
    params = %{"page" => "2", "sortBy" => "desc", "orderBy" => "Track Title"}
    # params = %{"page" => "2", "sortBy" => "asc", "orderBy" => "Album Title"}
    # params = %{"page" => "2", "sortBy" => "desc", "orderBy" => "Updated At"}
    # params = %{"page" => "999", "sortBy" => "asc"}

    params
    |> extract_tracks_params()
    |> list_first_tracks_paginated()
    |> to_tracks_result()
  end

  # ==== Request/Response

  @first_tracks_page_length 2
  @order_by_map %{
    "track title" => :track_title,
    "updated at" => :updated_at,
    "album title" => :album_title
  }

  defp extract_tracks_params(request_params) do
    start =
      request_params
      |> Map.get("page")
      |> String.to_integer()
      |> page_start(@first_tracks_page_length)

    [start: start, max_count: @first_tracks_page_length]
    |> Map.new()
    |> add_order_params(request_params)
  end

  defp page_start(current_page, page_length),
    do: (current_page - 1) * page_length

  defp add_order_params(params, request_params) do
    with {:ok, value} <- Map.fetch(request_params, "orderBy"),
         order_by_string <- String.downcase(value),
         {:ok, order_by} <- Map.fetch(@order_by_map, order_by_string) do
      add_order_params(params, order_by, extract_direction(request_params))
    else
      _ ->
        params
    end
  end

  defp add_order_params(params, order_by, direction) do
    params
    |> Map.put(:order_by, order_by)
    |> Map.put(:direction, direction)
  end

  defp extract_direction(request_params) do
    case request_params["sortBy"] do
      "desc" ->
        :desc

      _ ->
        :asc
    end
  end

  # [_|_] is list with at least one element
  defp to_tracks_result({[_ | _] = tracks, count}),
    do: [tracks: tracks, count: count]

  defp to_tracks_result({_, count}),
    do: [tracks: "Data not found", count: count]

  # === Ecto/DB

  defp list_first_tracks_paginated(params) do
    query = first_tracks_query()
    count = Repo.aggregate(query, :count, :id)

    tracks =
      query
      |> order_query(params)
      |> offset_query(params.start, params.max_count)
      |> fields_query()
      |> Repo.all()

    {tracks, count}
  end

  defp first_tracks_query do
    from(t in Track,
      join: a in assoc(t, :album),
      where: t.index == 1
    )
  end

  defp order_query(query, params) do
    if Map.has_key?(params, :direction) do
      order_query(query, params.order_by, params.direction)
    else
      query
    end
  end

  defp order_query(query, :track_title, direction),
    do: order_by(query, [t, _], [{^direction, t.title}])

  defp order_query(query, :updated_at, direction),
    do: order_by(query, [t, _], [{^direction, t.updated_at}])

  defp order_query(query, :album_title, direction),
    do: order_by(query, [_, a], [{^direction, a.title}])

  defp order_query(query, _, _),
    do: query

  def offset_query(query, start, max_count) do
    query
    |> limit(^max_count)
    |> offset(^start)
  end

  def fields_query(query) do
    select(query, [t, a], %{
      id: t.id,
      title: t.title,
      duration: t.duration,
      album_title: a.title,
      album_id: t.album_id,
      updated_at: t.updated_at
    })
  end
end

IO.inspect(Playground.play())
$ mix run priv/repo/playground.exs

18:31:04.110 [debug] QUERY OK source="tracks" db=2.1ms
SELECT count(t0."id") FROM "tracks" AS t0 INNER JOIN "albums" AS a1 ON a1."id" = t0."album_id" WHERE (t0."index" = 1) []

18:31:04.115 [debug] QUERY OK source="tracks" db=1.9ms
SELECT t0."id", t0."title", t0."duration", a1."title", t0."album_id", t0."updated_at" FROM "tracks" AS t0 INNER JOIN "albums" AS a1 ON a1."id" = t0."album_id" WHERE (t0."index" = 1) ORDER BY t0."title" DESC LIMIT $1 OFFSET $2 [2, 2]
[
  tracks: [
    %{
      album_id: 4,
      album_title: "Portrait In Jazz",
      duration: 204,
      id: 21,
      title: "Come Rain Or Come Shine",
      updated_at: ~N[2018-06-16 20:29:41.494546]
    },
    %{
      album_id: 3,
      album_title: "You Must Believe In Spring",
      duration: 192,
      id: 11,
      title: "B Minor Waltz (for Ellaine)",
      updated_at: ~N[2018-06-16 20:29:41.486320]
    }
  ],
  count: 5
]
$

Code formatted with https://elixirformatter.com/

Hi Peerreynders,

I am trying to understand your code. But it comeback the error like before

    warning: variable "p" does not exist and is being expanded to "p()", please use parentheses to remove the ambiguity or change the variable name
  lib/sgm_web/controllers/po_controller.ex:182


== Compilation error in file lib/sgm_web/controllers/po_controller.ex ==
** (CompileError) lib/sgm_web/controllers/po_controller.ex:182: invalid use of _. "_" represents a value to be ignored in a pattern and cannot be used in expressions
    (stdlib) lists.erl:1354: :lists.mapfoldl/3
    (stdlib) lists.erl:1355: :lists.mapfoldl/3
    lib/sgm_web/controllers/po_controller.ex:181: (module)
    (stdlib) erl_eval.erl:670: :erl_eval.do_apply/6

The error is come from here do: order_by(query, [p, _], [{^direction, p.notes}])

Oh yes, thank you for telling me about the underscore variable :blush:

Btw, here are the versions of my environment:
Ecto: 2.2.11
Phoenix: 1.3.4
Phoenix_ecto: 3.6.0
Elixir: 1.6.5 upgraded to 1.7.4 are the same :sweat:

  1. That error still doesn’t make any sense to me - it makes me wonder what else is going on in po_controller.ex.
  2. The one difference I can notice is that my code isn’t in a controller. Now I’m not sure why that would matter but it would motivate me to put the “Ecto/DB” functionality into its own module - for example:
# file: lib/sgm/pos/pos.ex
defmodule Sgm.Pos do
  alias Sgm.Repo
  # i.e. add the correct alias here
  alias Whatever.{PO, Customer}
  import Ecto.Query

  def list_pos_paginated(params) do
    query = pos_query()
    count = Repo.aggregate(query, :count, :id)

    pos =
      query
      |> order_query(params)
      |> offset_query(params.start, params.max_count)
      |> fields_query()
      |> Repo.all()

    {pos, count}
  end

  defp pos_query do
    from(p in PO,
      join: c in assoc(p, :customer),
      where: p.is_active == true
    )
  end

  defp order_query(query, params) do
    if Map.has_key?(params, :direction) do
      order_query(query, params.order_by, params.direction)
    else
      query
    end
  end

  defp order_query(query, :keterangan, direction),
    do: order_by(query, [p, _], [{^direction, p.notes}])

  defp order_query(query, :tanggal_po, direction),
    do: order_by(query, [p, _], [{^direction, p.po_date}])

  defp order_query(query, :kustomer, direction),
    do: order_by(query, [_, c], [{^direction, c.name}])

  defp order_query(query, _, _),
    do: query

  defp offset_query(query, start, max_count) do
    query
    |> limit(^max_count)
    |> offset(^start)
  end

  defp fields_query(query) do
    select(query, [p, c], %{
      id: p.id,
      number: p.number,
      notes: p.notes,
      customer_name: c.name,
      customer_id: p.customer_id,
      po_date: p.po_date
    })
  end
end

Leaving in po_controller.ex:

 # in lib/sgm_web/controllers/po_controller.ex

  alias Sgm.Pos

  def getListPO(conn, params) do
    results =
      params
      |> extract_pos_params()
      |> Pos.list_pos_paginated()
      |> to_pos_result()

    render(conn, "pos.json", results)
  end

  @pos_page_length 10
  @order_by_map %{
    "keterangan" => :keterangan,
    "tanggal po" => :tanggal_po,
    "kustomer" => :kustomer
  }

  defp extract_pos_params(request_params) do
    start =
      request_params
      |> Map.get("page")
      |> String.to_integer()
      |> page_start(@pos_page_length)

      add_order_params(
        %{
          start: start,
          max_count: @pos_page_length
        },
        request_params
      )
  end

  defp page_start(current_page, page_length),
    do: (current_page - 1) * page_length

  defp add_order_params(params, request_params) do
    with {:ok, value} <- Map.fetch(request_params, "orderBy"),
         order_by_string <- String.downcase(value),
         {:ok, order_by} <- Map.fetch(@order_by_map, order_by_string),
         direction <- extract_direction(request_params) do
      params
      |> Map.put(:order_by, order_by)
      |> Map.put(:direction, direction)
    else
      _ ->
        params
    end
  end

  defp extract_direction(request_params) do
    case request_params["sortBy"] do
      "desc" ->
        :desc

      _ ->
        :asc
    end
  end

  # [_|_] is list with at least one element
  defp to_pos_result({[_ | _] = pos, pos_count}),
    do: [pos: pos, posCount: pos_count]

  defp to_pos_result({_, pos_count}),
    do: [pos: "Data not found", posCount: pos_count]
1 Like

Hi Peerreynders,

After you post all codes, finally I know what is the problem with my code. The issue I mentioned before about below

    warning: variable "p" does not exist and is being expanded to "p()", please use parentheses to remove the ambiguity or change the variable name
lib/sgm_web/controllers/po_controller.ex:182

== Compilation error in file lib/sgm_web/controllers/po_controller.ex ==
** (CompileError) lib/sgm_web/controllers/po_controller.ex:182: invalid use of _. "_" represents a value to be ignored in a pattern and cannot be used in expressions
(stdlib) lists.erl:1354: :lists.mapfoldl/3
(stdlib) lists.erl:1355: :lists.mapfoldl/3
lib/sgm_web/controllers/po_controller.ex:181: (module)
(stdlib) erl_eval.erl:670: :erl_eval.do_apply/6

This is due to I didn’t have this statement import Ecto.Query
After I import this, your code works like a charm (yes, I also put the codes inside pos.ex for it’s part)

Thanks again for your kindly help. Still a lot of things I need to learn and understand in Elixir.
Now I also need to figure out what your code does :smiley:

1 Like

Ask away …

Hi Peerreynders,

I can understand most of your code now, but I do have two questions
1.

# [_|_] is list with at least one element
defp to_pos_result({[_ | _] = pos, pos_count}),
  do: [pos: pos, posCount: pos_count]

I don’t understand what that [_ | _] for. What is the different with if we only write like this {pos, pos_count}? Oh yes what this {} means here?

  1. When we split the codes to some sub query. Does it mean Ecto query to database multiple times?

def list_pos_paginated(params) do
query = pos_query()
count = Repo.aggregate(query, :count, :id)

pos =
query
|> order_query(params)
|> offset_query(params.start, params.max_count)
|> fields_query()
|> Repo.all()

{pos, count}
end

When this query = pos_query() happened, does it mean it will query all row first? Because I see it will count the row number from this, so I assume yes it’s. My point here is that, because we only need to take 10 rows, but this query will return all rows (assuming we have 1000 rows)

  # [_|_] is list with at least one element
  defp to_pos_result({[_ | _] = pos, pos_count}),
    do: [pos: pos, posCount: pos_count]

  defp to_pos_result({_, pos_count}),
    do: [pos: "Data not found", posCount: pos_count]

These two clauses define one function to_pos_result/1. The pattern [_|_] matches any list with at least one element - the _ simply communicates that we aren’t interested in the actual value. A more verbose way of writing the pattern would be [_head|_rest] - _head matches the head value of the list if there is one while _rest matches the rest of the list.

iex(1)> [_|_] = [1]
[1]
iex(2)> [_|_] = [1,2]
[1, 2]
iex(3)> [_|_] = [1,2,3]
[1, 2, 3]
iex(4)> [_|_] = []     
** (MatchError) no match of right hand side value: []
    (stdlib) erl_eval.erl:450: :erl_eval.expr/5
    (iex) lib/iex/evaluator.ex:249: IEx.Evaluator.handle_eval/5
    (iex) lib/iex/evaluator.ex:229: IEx.Evaluator.do_eval/3
    (iex) lib/iex/evaluator.ex:207: IEx.Evaluator.eval/3
    (iex) lib/iex/evaluator.ex:94: IEx.Evaluator.loop/1
    (iex) lib/iex/evaluator.ex:24: IEx.Evaluator.init/4
iex(4)> [_|_] = 3 
** (MatchError) no match of right hand side value: 3
    (stdlib) erl_eval.erl:450: :erl_eval.expr/5
    (iex) lib/iex/evaluator.ex:249: IEx.Evaluator.handle_eval/5
    (iex) lib/iex/evaluator.ex:229: IEx.Evaluator.do_eval/3
    (iex) lib/iex/evaluator.ex:207: IEx.Evaluator.eval/3
    (iex) lib/iex/evaluator.ex:94: IEx.Evaluator.loop/1
    (iex) lib/iex/evaluator.ex:24: IEx.Evaluator.init/4
iex(4)> 
  • So the first clause will execute when pos is a list with at least one element.
  • The second clause will execute in all other cases like when pos is an empty list.

to_pos_result/1 actually only accepts one single argument, a tuple {elem1, elem2}.

  • The first element of the tuple is expected to be a list of some sort - in this case a list of PO results.
  • The second element of the tuple is the total number of POs.

So again, if list_pos_paginated returns:

  • {[po1,po2,...,poN],pos_count} then the first clause of to_pos_result/1 executes
  • otherwise for {whatever, pos_count} the second clause executes where whatever could be [], nil, etc.

In {[_|_] = pos, pos_count} I used [_|_] = pos because I do want the entire list, and I want to refer to the list as pos.

This way may be easier to understand:

  defp to_pos_result({[head | rest], pos_count}),
    do: [pos: [head | rest], posCount: pos_count]

but that “rebuilds” the list.

Meanwhile

  defp to_pos_result({[_ | _] = pos, pos_count}),
    do: [pos: pos, posCount: pos_count]

simply uses the list as is.


When this query = pos_query() happened, does it mean it will query all row first?

That particular statement does not access the database

  def list_pos_paginated(params) do
    query = pos_query()                         # Bind basic query to "query" name 
    count = Repo.aggregate(query, :count, :id)  # Use basic query to perform count against the database
                                                # and COUNT only returns the number of rows - not the rows themselves 
    pos =                                             # Add to basic query to retrieve PO records
      query                                           # Start with the basic query
      |> order_query(params)                          # Add the ORDER BY to the query
      |> offset_query(params.start, params.max_count) # Add the LIMIT/OFFSET to the query
      |> fields_query()                               # Add which fields to select to the query
      |> Repo.all()                                   # NOW use complete query to access the database

    {pos, count}
  end

Note that in your post you had two Repo.all, one for the count and one for the results.

Counting Records With Ecto:

> Repo.aggregate(from(p in "people"), :count, :id)

16:11:23.786 [debug] QUERY OK source="people" db=1.7ms
SELECT count(p0."id") FROM "people" AS p0 []
168
2 Likes

Hi Peerreynders, thanks for all explanation, now I understand more about it. Oh yes, I forgot about tuple until you mentioned it here. Now I also understand that only a statement that includes “Repo” which will access the database. It first I thought as long as we have “from …” means it will access the database.

1 Like