mysql_array_in.exs

Mix.install([
  {:ecto_sql, "~> 3.11"},
  {:myxql, "~> 0.6.4"},
  {:testcontainers, "~> 1.8"}
])

alias Testcontainers.MySqlContainer

{:ok, _} = Testcontainers.start_link()
{:ok, container} = Testcontainers.start_container(MySqlContainer.with_image(MySqlContainer.new(), "mysql:8.4"))

Application.put_env(:example, Repo, MySqlContainer.connection_parameters(container))

ExUnit.start()
ExUnit.run()

defmodule Repo do
  use Ecto.Repo,
    otp_app: :example,
    adapter: Ecto.Adapters.MyXQL
end

defmodule Migration0 do
  use Ecto.Migration

  def change do
    create table("users") do
      add(:roles, :json)
    end
  end
end

defmodule User do
  use Ecto.Schema

  schema "users" do
    field(:roles, {:array, Ecto.Enum}, values: ~w[admin customer]a)
  end
end

defmodule Test do
  use ExUnit.Case, async: true
  import Ecto.Query

  setup :start_repo

  test "value in array" do
    user = user_fixture()

    assert user == Repo.one(where(User, [u], :admin in u.roles))
  end

  defp user_fixture do
    Repo.insert!(%User{roles: [:admin]})
  end

  defp start_repo(_context) do
    _ = Repo.__adapter__().storage_down(Repo.config())
    :ok = Repo.__adapter__().storage_up(Repo.config())

    {:ok, _} = Supervisor.start_link([Repo], strategy: :one_for_one)

    Ecto.Migrator.run(Repo, [{0, Migration0}], :up, all: true, log_migrations_sql: :debug)

    :ok
  end
end

  1) test value in array (Test)
     mysql_array_in.exs:47
     ** (MyXQL.Error) (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'u0.`roles`))' at line 1

         query: SELECT u0.`id`, u0.`roles` FROM `users` AS u0 WHERE ('admin' = ANY(u0.`roles`))
     code: assert user == Repo.one(where(User, [u], :admin in u.roles))
     stacktrace:
       (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
       (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:952: Ecto.Adapters.SQL.execute/6
       (ecto 3.11.2) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
       (ecto 3.11.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
       (ecto 3.11.2) lib/ecto/repo/queryable.ex:154: Ecto.Repo.Queryable.one/3
       mysql_array_in.exs:50: (test)