Ecto: How to access field of a preloaded association

Question

I am trying to get users who have no association with friend_referral_code or their code's status is false and they haven't used any code too. But I am unable to access the field status of the preloaded association friend_referral_code. Here is how I have done this:

def get_first_free_invite_users() do
    users =
      list_users()
      |> Repo.preload([:friend_referral_code])
      |> Enum.filter(
        fn u ->
          u.friend_referral_code == [] or u.friend_referral_code["status"] == false and
          Repo.all(from ref in FriendReferralCode,
            where: ref.receiver_id == ^u.id and ref.status == true) == []
        end)
    users
  end

It throws error on u.friend_referral_code.status == false. The reason I cannot go for join is that there might be no record of a particular user in FriendReferralCode and I want to capture those users. This is how the association is loaded:

friend_referral_code: [
      %MyApp.Accounts.FriendReferralCode{
        __meta__: #Ecto.Schema.Metadata<:loaded, "friend_referral_code">,
        challenge: #Ecto.Association.NotLoaded<association :challenge is not loaded>,
        challenge_id: nil,
        code: "RFQTS",
        expiry: 1,
        id: 16021,
        inserted_at: ~N[2021-02-01 11:55:00],
        order: 1,
        prize: #Decimal<3>,
        receiver: #Ecto.Association.NotLoaded<association :receiver is not loaded>,
        receiver_id: 15002,
        state: "create_competition",
        status: false,
        updated_at: ~N[2021-02-01 11:57:20],
        user: #Ecto.Association.NotLoaded<association :user is not loaded>,
        user_id: 15001
      }
    ],

Answer 1

It throws error on u.friend_referral_code.status == false.

That's because u.friend_referral_code is a list in your example. You need to enumerate over u.friend_referral_code and check the status on each one.

Try something like this.

def get_ref_codes_by_user(id) do
  Repo.all(from ref in FriendReferralCode,
    where: ref.receiver_id == ^u.id and ref.status == true)
end

def filter_referrals([], user_id, acc), do: acc
def filter_referrals([ref, rest], user_id, acc) do
  is_used = 
    ref == [] || ref.status == false && get_ref_codes_by_user(id) == []
  
  new_acc = 
    case is_used do
      true -> acc
      false -> [ref | acc]
    end

  filter_referrals(rest, user_id, new_acc)
end

def get_first_free_invite_users() do
  list_users()
  |> Repo.preload([:friend_referral_code])
  |> Enum.map(
    fn u ->
      filter_referrals(u.friend_referral_code, u.id, [])
    end)
end

This will get you a lot closer and give you a list of list of FriendReferralCodes. Double check that the logic is what you want here. All that said, you should be able to do this with a join. Check the docs here and this other SO answer about join types.

Try

Repo.all(from ref in FriendReferralCode,
  right_join users u on ref.user_id =  u.id
    where: ref.status == true)