Correct way to filter with an inner join using Ash.Query

Thanks, this works perfectly!

Tag
|> Ash.Query.filter(exists(device_tags, true))
|> MyApi.read!(tenant: tenant)

Something like the above should do it

I’ve tried playing around in the Ash test suite and the only thing I gain from not having a composite foreign key is that I can do

User
|> Ash.Query.filter(not is_nil(posts))
|> Api.read!()

to obtain the users that have at least one post (without having to do, e.g., posts.title like I have to do in my application).

Still, this is doing an outer join, not an inner join:

[debug] QUERY OK source="tags" db=1.5ms queue=0.5ms idle=1346.0ms
SELECT DISTINCT ON (t0."id") t0."id", t0."name", t0."inserted_at", t0."updated_at", t0."tenant_id" FROM "tags" AS t0 LEFT OUTER JOIN "public"."devices_tags" AS d1 ON t0."id" = d1."tag_id" WHERE (NOT (d1."tag_id"::bigint IS NULL)) AND (t0."tenant_id"::bigint = $1::bigint) [1] module=Ecto.Adapters.SQL function=log/4

This fails in the same way the first two fail:

** (Ash.Error.Unknown) Unknown Error

* filter: Invalid reference device_tags when hydrating relationship ref for [:device_tags]. Require single attribute primary key.

If I pass another field from the relationships

Tag
|> Ash.Query.filter(exists(:device_tags.device_id))
|> MyApi.read!(tenant: tenant)

it fails with

** (Ash.Error.Invalid) Input Invalid

* got KeyError with message "key :resource not found in: %Ash.Error.Query.NoSuchFunction{\n  name: :exists,\n  arity: nil,\n  changeset: nil,\n  query: nil,\n  error_context: [],\n  vars: [],\n  path: [:filter],\n  stacktrace: #Stacktrace<>,\n  class: :invalid\n}" while retrieving Exception.message/1 for %Ash.Error.Query.NoSuchFunction{name: :exists, arity: nil, changeset: nil, query: nil, error_context: [], vars: [], path: [:filter], stacktrace: #Stacktrace<>, class: :invalid}.

Since I get a lot of errors on the composite foreign key, I’m trying out in a fresh repo with “normal” primary keys to see if that’s the problem.

I wonder if using exists here might help: Ash.Query.Exists — ash v3.29.3

Something maybe like this? (sorry, not in a place where I can try this out myself)

Tag
|> Ash.Query.filter(exists(:device_tags))
|> Ash.Query.set_tenant(tenant_id)
|> MyApi.read!()