Ok, so I have these schemas

  schema "players" do
    field :email, :string
    
    has_many :profiles, MyApp.Profile
    has_many :worlds, through: [:profiles, :world]
  end

  schema "worlds" do
    field :name, :string
    
    has_many :profiles, MyApp.Profile
    has_many :players, through: [:profiles, :player]
  end

  schema "settings" do
    field :mode, :string
    
    belongs_to :player, MyApp.Player
    belongs_to :world, MyApp.World
  end

(You might notice that this is a poorly designed database relationship, but this is really what I’m dealing with right now)

All players are supposed to have one settings in each world they create by default. But due to logical errors in our code, some players didn’t have settings in some world.

Now I’m trying to find those players who don’t have existing settings record in some world so I can create default settings for them using a seeder.

I’ve tried workaround like this

query = from profile in Profile

query
|> Repo.all()
|> Enum.each(fn profile ->
  case Settings.get_settings(profile.player_id, profile.world_id) do
    nil ->
      Settings.create_settings(profile.player_id, profile.world_id)

    _ ->
      :ok
  end
end)

It works but I want to avoid using the case statement. It costs a lot of database work.
Is there any way to fetch those players with no existing settings record in some worlds using a query?

I find this question quite similar to mine. I tried to write it in elixir and with ecto fragment but I can’t make it work.

Please help me.