Ecto: or_where seems to be very strange in master

[philipgiuliani]

Environment

• Elixir version (elixir -v): 1.3.4
• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): Postgres 9.5
• Ecto version (mix deps): 3afe68832a17fe056bac2da75e43f2ff26b6d88b
• Database adapter and version (mix deps): postgrex 1.0-rc1
• Operating system: OSX / Linux

Current behavior

|> or_where(account_id: ^account.id, partner_id: ^account.id)
|> Repo.all()

SELECT c0."id", c0."account_id", c0."partner_id", c0."last_message_id", c0."inserted_at", c0."updated_at" FROM "chat_conversations" AS c0 WHERE ((c0."account_id" = $1) AND (c0."partner_id" = $2)) [7, 7]

Expected behavior

As you can see in the current behavior, the conditions are merged with AND. I do not understand how the or_where should work. But currently it was perfect for my use case.

Complete query:

|> where([c], not is_nil(c.last_message_id))
|> or_where(account_id: ^account.id, partner_id: ^account.id)
|> Repo.all()

What I expected here, is show all Conversations where the last_message_id is not null, and the account.id is the partner_id OR account_id.

The commit that breaks this is: 3afe68832a17fe056bac2da75e43f2ff26b6d88b

@josevalim How should i build the query I want now?

[josevalim]

@josevalim How should i build the query I want now?

It is explained in the commit itself. For convenience:

Conversation
|> where([c], not is_nil(c.last_message_id))
|> or_where(account_id: ^account.id)
|> or_where(partner_id: ^account.id)
|> Repo.all()

:)

[bigcurl]

Hi @josevalim ,

I just stumbled over the same point that was raised by @philipgiuliani. Unfortunately the commit message in 3afe688 did not give a clear reason why the or_where part is build with and instead of or when given a list.

If there is no clear reason I would propose to change the current behaviour as it is quite confusing and leads to more code as the list items for the or_where clause have to packaged separately before the query itself.

If the number of list items is static it is easy to hardcode them but what about dynamically build list?

Example to clarify more:

city_names = [name: "Darmstadt", name: "Berlin"]
Repo.all(from c in "cities", select: c.name, or_where: ^city_names)

Current result is:

SELECT c0.nameFROMcitiesAS c0 WHERE ((c0.name= ?) AND (c0.name= ?)) ["Darmstadt", "Berlin"]

Expected result is:

SELECT c0.nameFROMcitiesAS c0 WHERE ((c0.name= ?) OR (c0.name= ?)) ["Darmstadt", "Berlin"]

Note the and and or in the results.