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/
