CODEWITHSUNDEEP
phoenix-frameworkecto
steel
steel

Reputation: 12570

Ecto: Where - Or

It looks like a simple feature is coming for this issue, but Phoenix Ecto doesn't have it yet. What is a workaround in the meantime for a where-or query in Ecto?

For example:

from(u in User,
    left_join: up in assoc(u, :user_projects),
    # does not work
    where: up.project_id != ^project.id OR up.project_id IS NULL,
    select: {u.id, u.username})

Upvotes: 7

Views: 7946

Answers (2)

Dogbert
Dogbert

Reputation: 222388

I believe you've misunderstood what or_where will do -- what you want can be done with a simple or and is_nil:

where: up.project_id != ^project.id or is_nil(up.project_id)

What or_where does is enable you to join multiple where: expr in an Ecto query with an OR instead of AND. In the current version of Ecto (2.0), there is no straightforward way to do that -- Ecto joins all where expressions with an AND.

Upvotes: 10

Alex Antonov
Alex Antonov

Reputation: 15216

Just replace OR by and with not 

from(u in User,
left_join: up in assoc(u, :user_projects),
where: up.project_id != ^project.id AND not(is_nil(up.project_id)),
select: {u.id, u.username})

This is more ecto way, by the way

Upvotes: -2

Related Questions