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

Ash Forum Ash Questions / Help
, ,
1

In my application I want to retrieve all tags that are currently assigned to at least a device.

In Ecto I did this with:

  def list_device_tags do
    query =
      from t in Tag,
        distinct: true,
        join: dt in DeviceTag,
        on: t.id == dt.tag_id

    Repo.all(query)
  end

What is the correct way to do the same thing with Ash.Query?

Additional info

DeviceTag is the join table between Device and Tag, and it has a composite primary key of tenant_id + device_id + tag_id (I’m using attribute-based multitenancy).

In the Ash version I’ve also added a has_many :device_tags relationship on the Tag (which was needed before) to use it in filters.

What I tried

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

Results in:

** (Ash.Error.Unknown) Unknown Error

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

Tag
|> Ash.Query.filter(length(device_tags) > 1)
|> Ash.read!(tenant: tenant)

Results in the same error as above

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

Results in:

[debug] QUERY ERROR source="tags" db=0.0ms queue=17.2ms idle=1659.4ms
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 (d1."device_id"::bigint) AND (t0."tenant_id"::bigint = $1::bigint) [1] module=Ecto.Adapters.SQL function=log/4
** (Ash.Error.Unknown) Unknown Error

* ** (Postgrex.Error) ERROR 42804 (datatype_mismatch) argument of AND must be type boolean, not type bigint

Tag
|> Ash.Query.filter(not is_nil(device_tags.device_id))
|> Ash.read!(tenant: 1)

Seems to do what I want, but the check on device_id seems redundant.

Is this actually the way to go or am I missing something?

2

I wonder if using exists here might help: Ash.Query.Exists — ash v2.21.2

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!()
3

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.

4

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
5 
Tag
|> Ash.Query.filter(exists(device_tags, true))
|> MyApi.read!(tenant: tenant)

Something like the above should do it :slight_smile:

1 Like
6

Thanks, this works perfectly!