How to create this kind of or_where query in Ecto?

Questions / Help
1

Here’s the query:

    completed_visits =
      from(v in Visit,
        select: count("*"),
        where: v.scheduled_for >= ^sixty_days_ago,
        where: v.pal_id == ^pal.id,
        where: v.state in ["completed", "reviewed"]
      )
      |> Repo.one()

    future_visits =
      from(v in Visit,
        select: count("*"),
        where: v.scheduled_for >= ^Timex.now(),
        where: v.pal_id == ^pal.id
      )
      |> Repo.one()

The meat of this query is: they have a visit in either state OR they have a visit scheduled for the future.

How could I combine this into a single SQL query using Ecto? Found some documentation here, but having some trouble understand how to fit it into my current needs. https://hexdocs.pm/ecto/Ecto.Query.html#or_where/3-keywords-example

2

Does it work if you combine the clauses with or, like:

      from(v in Visit,
        select: count("*"),
        where: (v.scheduled_for >= ^sixty_days_ago and v.pal_id == ^pal.id and v.state in ["completed", "reviewed"]) or (v.scheduled_for >= ^Timex.now() and v.pal_id == ^pal.id)
      )
      |> Repo.one()
2 Likes
3

Yeah this works:

    matching_visits =
      from(v in Visit,
        select: count("*"),
        where: v.pal_id == ^pal.id,
        where:
          (v.scheduled_for >= ^sixty_days_ago and
             v.state in ["completed", "reviewed"]) or
            v.scheduled_for >= ^Timex.now()
      )
      |> Repo.one()

I guess where_or is just the wrong fit here.

4

Actually, I don’t see a reason that or_where won’t work here. You’d want to put the 2 clauses fully together. So 1 where and 1 or_where.

Forgot about that one :smile: