Find data who has no existing associated record

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.

Assuming you have referential integrity between settings, worlds and players, you could test for count of settings for each profile versus count of worlds. So something like:

world_count = Repo.all(World) |> Enum.count() # Or use Ecto aggregation..

query = from(p in Player,
  left_join: s in Settings, on: p.id = s.player_id,
  select: p.id,
  group_by: p.id,
  having: count(s.id) < ^world_count)

list_of_players_with_incomplete_profiles = Repo.all(query)
#... do what you need to
1 Like

There’s a common approach with left joins for doing things like this:

from players in Player,
  left_join: settings in assoc(players, :profiles),
  on: settings.world_id == 1234,
  where: is_nil(settings.id)

The left_join guarantees that if there aren’t any matching rows in settings then a row with all NULLs for the columns from settings is generated, then the where grabs only those records.

4 Likes