Hi, I’m trying to create an ecto fragment to update a set of records but I’m running into some issues, would be very thankful if someone can explain me what I’m doing wrong here.
I have a schema for a user resource:
defmodule User.Scroll do
use Ecto.Schema
schema("user_scrolls") do
belongs_to :user, User
field :scroll_id, :integer
field :from_type, User.Scroll.FromType
field :from_id, :id
field :only_owner, :boolean, default: false
field :locked, :boolean, default: false
field :locked_by, {:array, :string}, default: []
field :locked_duel, :id
field :locked_chaos_brawl, :id
field :locked_tournament, :id
field :locked_open, :binary_id
field :locked_until, :utc_datetime_usec
field :borrowed, :boolean, default: false
field :borrowed_from, :id
timestamps(type: :utc_datetime_usec)
end
end
This record is updated at some point and ends up getting in the following state:
iex(59244833@o.local)1> [s] = User.Scroll |> where([s], s.locked) |> Db.Repo.all
[debug] QUERY OK source="user_scrolls" db=1.1ms queue=0.7ms idle=1752.8ms
SELECT u0."id", u0."user_id", u0."scroll_id", u0."from_type", u0."from_id", u0."only_owner", u0."locked", u0."locked_by", u0."locked_duel", u0."locked_chaos_brawl", u0."locked_tournament", u0."locked_open", u0."locked_until", u0."borrowed", u0."borrowed_from", u0."inserted_at", u0."updated_at" FROM "user_scrolls" AS u0 WHERE (u0."locked") []
[
%User.Scroll{
__meta__: #Ecto.Schema.Metadata<:loaded, "user_scrolls">,
borrowed: false,
borrowed_from: nil,
from_id: 1,
from_type: :regular,
id: 2,
inserted_at: ~U[2020-08-17 10:23:27.469625Z],
locked: true,
locked_by: ["open-duel-5ade3b99-5e50-4069-bb08-ddf4a8bf9d97::1"],
locked_chaos_brawl: nil,
locked_duel: nil,
locked_open: "5ade3b99-5e50-4069-bb08-ddf4a8bf9d97",
locked_tournament: nil,
locked_until: nil,
only_owner: true,
scroll_id: 28,
updated_at: ~U[2020-08-17 10:23:27.469625Z],
user: #Ecto.Association.NotLoaded<association :user is not loaded>,
user_id: 1
}
]
The relevant pieces here are the locked_by
and locked_open
fields.
The string in the locked_by
array field is generated through:
defmodule Shared.Locks do
def generate_locked_by(:open_duel, od_id, user_id) do
"open-duel-#{od_id}::#{user_id}"
end
end
I then have a function that runs an update query using a macro:
def unblock_players_resources(players_ids, %Open{id: od_id, type: :PLAYGROUND}) do
User.Scroll
|> where([s], s.user_id in ^players_ids and s.locked and s.locked_open == ^od_id)
|> update([s], set: [locked_by: remove_from_blocked_by(field(s, :locked_by), :open_duel, players_ids, od_id), locked_open: nil])
|> Db.Repo.update_all([])
end
And the remove_from_blocked_by
macro is defined as:
defmodule Macros.Resources do
defmacro remove_from_blocked_by(field, type, player_1, od_id) do
quote do
fragment("array_remove(?, ?)", unquote(field), ^Shared.Locks.generate_locked_by(unquote(type), unquote(od_id), unquote(player_1)))
end
end
end
This generates the following query:
[debug] QUERY OK source="user_scrolls" db=1.1ms queue=0.9ms idle=1898.3ms
UPDATE "user_scrolls" AS u0 SET "locked_by" = array_remove(u0."locked_by", $1), "locked_open" = NULL WHERE ((u0."user_id" = ANY($2) AND u0."locked") AND (u0."locked_open" = $3)) [<<111, 112, 101, 110, 45, 100, 117, 101, 108, 45, 53, 97, 100, 101, 51, 98, 57, 57, 45, 53, 101, 53, 48, 45, 52, 48, 54, 57, 45, 98, 98, 48, 56, 45, 100, 100, 102, 52, 97, 56, 98, 102, 57, 100, 57, 55, 58, 58, 1>>, [1], <<90, 222, 59, 153, 94, 80, 64, 105, 187, 8, 221, 244, 168, 191, 157, 151>>]
So it seems the first argument ($1
) is being passed as binary
where I would expect it to be a string. I’ve tried to use also type(^Shared.Locks.generate_locked_by....., :string)
but it ends up in the same form.
If from psql I execute this query it correctly removes the element from the array:
awars_dev=# SELECT * from user_scrolls AS s WHERE s.locked;
id | user_id | scroll_id | from_type | from_id | locked | locked_by | locked_duel | locked_chaos_brawl | locked_tournament | locked_open | locked_until | borrowed | borrowed_from | only_owner | inserted_at | updated_at
----+---------+-----------+-----------+---------+--------+-----------------------------------------------------+-------------+--------------------+-------------------+-------------+--------------+----------+---------------+------------+----------------------------+----------------------------
2 | 1 | 28 | regular | 1 | t | {open-duel-5ade3b99-5e50-4069-bb08-ddf4a8bf9d97::1} | | | | | | f | | t | 2020-08-17 10:23:27.469625 | 2020-08-17 10:23:27.469625
(1 row)
awars_dev=# UPDATE user_scrolls AS s SET locked_by = array_remove(locked_by, 'open-duel-5ade3b99-5e50-4069-bb08-ddf4a8bf9d97::1') WHERE s.locked;
UPDATE 1
The %Open{}
struct id
is a binary id, it’s correctly cast in the unblock_players_resources()
query, as it finds a record and updates correctly the locked_open
field, but somehow I can’t get the macro fragment for the array_remove
to be a varchar/string of the form "open-duel-f184133f-a996-4883-a129-8b307c6a6634::1"
instead it’s always being set as a binary.
I’ve also tried in the fragment to use type(^Shared.Locks.generate_locked_by(unquote(type), unquote(od_id), unquote(player_1)), :string)
to no avail, the result is the same query.
Does anyone have any idea on how I can get this working or what I’m doing wrong? Thanks