postgresql - Ecto/Elixir/Phoenix - Fetch records with no associated record

Question

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

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 workarounds like this

query = from profile in Profile

query
|> Repo.all()
|> Enum.each(fn profile ->
  case get_settings(profile.player_id, profile.world_id) do
    nil ->
      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?

question from:https://stackoverflow.com/questions/65598226/ecto-elixir-phoenix-fetch-records-with-no-associated-record

Answer 1

Use Ecto.Query.join/5 and Ecto.Query.subquery/2 more or less like

subquery =
  from profile in Profile,
    join: player in Player,
    on: player.id == profile.player_id,
    join: world in World,
    on: world.id == profile.world_id

query =
  from setting in Settings,
    join: profile in subquery(subquery),
    on: setting.player_id == profile.player_id and
        setting.world_id == profile.world_id 

or, even easier, you might directly join settings to profiles on player_id and world_id.

The latter reveals that you actually have a design flaw. Settings and profiles are basically the same entity, represented twice. That’s why you have inconsistency. Enrich profiles with whatever fields in settings and get rid of settings at all.