AE
Ash Elixir•3y ago
Alan Heywood

Multiple filters giving unexpected result

Say I have a data model similar to this: 
defmodule Ht.Org do
  has_many :accounts, Ht.Account   
end

defmodule Ht.User do
  has_many :accounts, Ht.Account   
end

defmodule Ht.Account do
  belongs_to :org, Ht.Org
  belongs_to :user, Ht.User
end
defmodule Ht.Org do
  has_many :accounts, Ht.Account   
end

defmodule Ht.User do
  has_many :accounts, Ht.Account   
end

defmodule Ht.Account do
  belongs_to :org, Ht.Org
  belongs_to :user, Ht.User
end
I create some records and perform this query (note that create! is my own simple factory helper) 
org = create!(:org)

user_1 = create!(:user)
user_1_account = create!(:account, user_id: user_1.id, org_id: org.id)

user_2 = create!(:user)
user_2_account = create!(:account, user_id: user_2.id, org_id: org.id)

Ht.User
|> Ash.Query.filter(id == ^user_1.id)
|> Ash.Query.filter(id == ^user_2.id or accounts.org.id == ^org.id)
|> Api.read(authorize?: false)
|> IO.inspect()
org = create!(:org)

user_1 = create!(:user)
user_1_account = create!(:account, user_id: user_1.id, org_id: org.id)

user_2 = create!(:user)
user_2_account = create!(:account, user_id: user_2.id, org_id: org.id)

Ht.User
|> Ash.Query.filter(id == ^user_1.id)
|> Ash.Query.filter(id == ^user_2.id or accounts.org.id == ^org.id)
|> Api.read(authorize?: false)
|> IO.inspect()
This is returning both user records. My understanding is that multiple Ash.Query.filter calls are combined with and, so the first filter should constrain the results to 1 record at most. Is there anything I am doing wrong in the way my filters are defined?
4 Replies
Alan Heywood
Alan HeywoodOP•3y ago
Here is the SQL generated, it seems like the id = user_1.id and id = user_2.id are incorrectly being combined with AND. 
SELECT DISTINCT ON (u0."id") u0."id"
FROM "users" AS u0
LEFT OUTER JOIN "public"."accounts" AS a1 ON u0."id" = a1."user_id"
LEFT OUTER JOIN "public"."orgs" AS o2 ON a1."org_id" = o2."id"
WHERE (
  (
    (u0."id"::uuid = $1::uuid)
    AND (u0."id"::uuid = $2::uuid)
  )
  OR (o2."id"::uuid = $3::uuid)
)
SELECT DISTINCT ON (u0."id") u0."id"
FROM "users" AS u0
LEFT OUTER JOIN "public"."accounts" AS a1 ON u0."id" = a1."user_id"
LEFT OUTER JOIN "public"."orgs" AS o2 ON a1."org_id" = o2."id"
WHERE (
  (
    (u0."id"::uuid = $1::uuid)
    AND (u0."id"::uuid = $2::uuid)
  )
  OR (o2."id"::uuid = $3::uuid)
)
ZachDaniel
ZachDaniel•3y ago
🤔 🤔 🤔 I feel like that can't be true because someone would have noticed 😆 but maybe this is that Yep I see the same thing I'm fixing this immediately fixed in 2.7.0 that was very embarrassing, sorry about that
Alan Heywood
Alan HeywoodOP•3y ago
Thank you for jumping on this, much appreciated! I'll verify it in my codebase now. Works perfectly now 🥳
ZachDaniel
ZachDaniel•3y ago
👍 excellent

Did you find this page helpful?