Ecto query with dynamic with left join “where left join value is nil”

Questions / Help
1

Hi,

I have a dynamic query which works perfectly in case of inner join where the value for association is mandatory.

In cases of left join where the value for the association id can be nil, the query throws error for nil check whenever the value is not populated.

In the code below, compose_my_entities_query functions works fine in case of “zc” with inner join but throws error in case of “dt” where the value is not present.

I need help in fixing this. Thanks.

def compose_my_entities_query(%Ecto.Query{} = query, user_id, ["zc" | tail], dynamic) do
  dynamic = dynamic([zone: zone], zone.zc_id == ^user_id or ^dynamic)

  query
  |> ensure_zone()
  |> compose_my_entities_query(user_id, tail, dynamic)
end

def compose_my_entities_query(%Ecto.Query{} = query, user_id, ["dt" | tail], dynamic) do
  dynamic =
    dynamic(
      [designated_trainer: designated_trainer],
      designated_trainer.user_id == ^user_id or ^dynamic
    )

  query
  |> ensure_candidate_designated_trainer()
  |> compose_my_entities_query(user_id, tail, dynamic)
end

def ensure_zone(query) do
  if has_named_binding?(query, :zone) do
    query
  else
    query
    |> join(:inner, [candidate: candidate], zone in assoc(candidate, :zone), as: :zone)
  end
end

def ensure_candidate_designated_trainer(query) do
  if has_named_binding?(query, :designated_trainer) do
    query
  else
    query
    |> join(
      :left,
      [candidate: candidate],
      designated_trainer in assoc(candidate, :designated_trainer),
      as: :designated_trainer
    )
  end
end
2

Is it user_id that can be nil? If so, try adding an extra function clause that uses is_nil(designated_trainer.user_id) instead of the == comparison.

There’s probably a more concise way to do this but that might get you up and running to start.

3

No, its the designated_trainer association that can be blank which leads to the possibility of:

designated_trainer.user_id == ^user_id or ^dynamic

designated_trainer.user_id being blank. ^user_id is always populated. It was relatively straight forward to handle if the user_id param value was nil

4

What is the error message you are seeing? Is it from Ecto or is it from the underlying database?

5

It is Ecto error. Excerpts from Rollbar error log:

{
          "method": "Ecto.Query.Builder.not_nil!/1 (ecto)", 
          "lineno": 1030, 
          "filename": "lib/ecto/query/builder.ex"
        }, 
        {
          "method": "anonymous fn/3 in Pcms.Participants.compose_my_entities_query/4 (pcms)", 
          "lineno": 1554, 
          "filename": "lib/pcms/participants.ex"
        }, 
...
"exception": {
        "message": "comparison with nil is forbidden as it is unsafe. If you want to check if a value is nil, use is_nil/1 instead", 
        "class": "ArgumentError"
      }
6

This means that one of the interpolated values is nil. There’s no way for Ecto to know what designated_trainer.user_id is while the query is being performed.

Can you ensure that the ^user_id is indeed always non-nil?

7

user_id being passed in the query is current user, which is always populated. so nil is not from there.

Candidates who have designated trainers associated, the query is working fine. Its only for candidates where the association is nil, this error is thrown.

8

Edit:

not(is_nil(designated_trainer.user_id)) and (designated_trainer.user_id == ^user_id or ^dynamic)

Or vice versa (not .. and .. ) or ^dynamic depending on the logic.

9

Adding a not(is_nil(designated_trainer.user_id)) will change the result set to behave as inner join rather than left join. It will filter out all candidates where designated_trainer is not associated. Thats not the intended result here.

The query picks up candidates where the current user has some relation through a role. There are multiple roles through which the current user can be linked to the candidates like mentor, co-ordinator or designated trainer. Candidate association for other roles are mandatory and non-null column in the candidate table. Hence, the query was running fine all along. Designated Trainer was introduced recently, which is optional. Since then, this issue started cropping up.

So the current query, for easy understanding, is something like:
Select all candidates where candidate.zc.user_id == ^user_id or candidate.mentor.user_id == ^user_id or candidate.designated_trainer.user_id == ^user_id

user_id being current_user_id in this context. zc_user_id and mentor.user_id is always available since its mandatory on candidate record but designated_trainer is optional.

Hope that helps understand the context better :slight_smile: