Hi again!
Since I haven’t got a solution with building my queries from dynamic request maps, I’ve decided to try to build a string with query and a list with binding params and use Ecto.Adapters.SQL.query to do DB requests.
But here I got another rock
Here is a number of subsequent queries with count():
iex(12)> Ecto.Adapters.SQL.query(Repo, "select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486, 730000])", [])
[debug] QUERY OK db=2.9ms
select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486, 730000]) []
{:ok,
%Postgrex.Result{columns: ["count"], command: :select, connection_id: 18519,
num_rows: 1, rows: ['8']}}
iex(13)> Ecto.Adapters.SQL.query(Repo, "select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[730000])", [])
[debug] QUERY OK db=2.5ms
select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[730000]) []
{:ok,
%Postgrex.Result{columns: ["count"], command: :select, connection_id: 18517,
num_rows: 1, rows: [[1]]}}
iex(14)> Ecto.Adapters.SQL.query(Repo, "select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486])", [])
[debug] QUERY OK db=3.0ms
select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486]) []
{:ok,
%Postgrex.Result{columns: ["count"], command: :select, connection_id: 18520,
num_rows: 1, rows: ['7']}}
iex(15)> Ecto.Adapters.SQL.query(Repo, "select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[000000])", [])
[debug] QUERY OK db=2.8ms
select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[000000]) []
{:ok,
%Postgrex.Result{columns: ["count"], command: :select, connection_id: 18515,
num_rows: 1, rows: [[0]]}}
The same queries (copied one-to-one via Ctrl+C\Ctrl+V) in the same order to the psql:
[ db ] # select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486, 730000]);
count
-------
56
(1 row)
Time: 0,962 ms
[ db ] # select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[730000]);
count
-------
1
(1 row)
Time: 0,798 ms
[ db ] # select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[733486]);
count
-------
55
(1 row)
Time: 1,166 ms
[ db ] # select count(*) from nioktr as n LEFT OUTER JOIN nioktr_to_grnti as ng ON n.id = ng.nioktr_id LEFT OUTER JOIN spr_grnti as g ON g.id = ng.spr_grnti_id WHERE g.id = ANY(ARRAY[000000]);
count
-------
0
(1 row)
Time: 1,130 ms
So I don’t understand how to interpret SQL.query results. Can somebody explain me, please?