Dynamically building an AND expression with Ash.Query

Ash Forum Ash Questions / Help
,
1

I’m trying to query members that have all the given tag IDs. The code needs to dynamically build an expression like:

member.member_tags.tag_id = tag_id1
AND member.member_tags.tag_id = tag_id2
AND member.member_tags.tag_id = tag_id3

where tag_id1, tag_id2, and tag_id3 come in as a list.

I tried this:

Enum.reduce(tag_ids, query, fn id, query ->
  Ash.Query.filter(query, exists(member_tags.tag_id == ^id))
end)

That results in:

[warning] `1b6d55b5-7d6a-478d-9872-86153c141ff0`: AshGraphql.Error not implemented for error:

** (Ash.Error.Query.NoSuchFunction) No such function exists for resource GF.Members.Member2
    (elixir 1.16.1) lib/process.ex:860: Process.info/2
    (ash 3.1.3) lib/ash/error/query/no_such_function.ex:5: Ash.Error.Query.NoSuchFunction.exception/1
    (ash 3.1.3) lib/ash/filter/filter.ex:3311: Ash.Filter.resolve_call/2
    (ash 3.1.3) lib/ash/filter/filter.ex:2513: Ash.Filter.add_expression_part/3
    (ash 3.1.3) lib/ash/filter/filter.ex:2451: anonymous fn/3 in Ash.Filter.parse_expression/2

I tried playing with Ash.Expr.expr():

expression = Ash.Expr.expr(true)

Enum.reduce(tag_ids, expression, fn id, expression ->
  Ash.Expr.expr(^expression and member_tags.tag_id == ^id)
end)

query = Ash.Query.filter(query, ^expression)

But the filter part of the query always came back as true.

2

I think your first attempt was closer to the mark, but the syntax isn’t quite right.

The docs are a bit sparse, but looking at Aggregates — ash v3.2.2, I think something like exists(member_tags, query: [filter: tag_id == ^id]) might work?

3

Exists is a special case because it was a calculation before the exist aggregate was a thing.


exists(member_tags, tag_id == ^id)

Example here: Expressions — ash v3.2.2

4

I tried:

exists(member_tags, query: [filter: tag_id == ^id])

Which produced:

Compiling 95 files (.ex)
     warning: variable "query" is unused (if the variable is not meant to be used, prefix it with an underscore)
     │
 124 │   def members_query(query, {:tag_ids, tag_ids}) do
     │                     ~~~~~
     │
     └─ lib/gf/members/members2_query.ex:124:21: GF.Members.Members2Query.members_query/2

     warning: variable "tag_ids" is unused (if the variable is not meant to be used, prefix it with an underscore)
     │
 124 │   def members_query(query, {:tag_ids, tag_ids}) do
     │                                       ~~~~~~~
     │
     └─ lib/gf/members/members2_query.ex:124:39: GF.Members.Members2Query.members_query/2

     error: undefined function exists/2 (expected GF.Members.Members2Query to define such a function or for it to be imported, but none are available)
     │
 140 │     exists(member_tags, query: [filter: tag_id == ^id])
     │     ^^^^^^
     │
     └─ lib/gf/members/members2_query.ex:140:5: GF.Members.Members2Query.members_query/2

     error: misplaced operator ^id

     The pin operator ^ is supported only inside matches or inside custom macros. Make sure you are inside a match or all necessary macros have been required
     │
 140 │     exists(member_tags, query: [filter: tag_id == ^id])
     │                                                   ^
     │
     └─ lib/gf/members/members2_query.ex:140:51: GF.Members.Members2Query.members_query/2

     error: undefined variable "tag_id"
     │
 140 │     exists(member_tags, query: [filter: tag_id == ^id])
     │                                         ^^^^^^
     │
     └─ lib/gf/members/members2_query.ex:140:41: GF.Members.Members2Query.members_query/2

     error: undefined variable "member_tags"
     │
 140 │     exists(member_tags, query: [filter: tag_id == ^id])
     │            ^^^^^^^^^^^
     │
     └─ lib/gf/members/members2_query.ex:140:12: GF.Members.Members2Query.members_query/2

I tried:

    Enum.reduce(tag_ids, query, fn id, query ->
      Ash.Query.filter(query, exists(member_tags, query: [filter: tag_id == ^id]))
    end)

It produced:

05:47:15.598 request_id=F-NPcmVbW3yUlOAAAd4B [warning] `87826e8d-913e-4847-88c9-d26d287f24ce`: AshGraphql.Error not implemented for error:

** (Ash.Error.Query.NoSuchField) No such field query for resource GF.Members.MemberTag2
    (elixir 1.16.1) lib/process.ex:860: Process.info/2
    (ash 3.1.3) lib/ash/error/query/no_such_field.ex:5: Ash.Error.Query.NoSuchField.exception/1
    (ash 3.1.3) lib/ash/filter/filter.ex:2857: Ash.Filter.add_expression_part/3

The documentation that @zachdaniel pointed to doesn’t cover the case where the record’s has_many relationship needs to match every value in a list.

Is there a solution for this?

5

The query: [filter: ....] variation won’t work. That is the syntax for inline aggregates, whereas exists is special. The snippet I posed should work.

exists(member_tags, tag_id == ^id)

The kind of query you are writing is, in general, not a very easy query to write. There are a few ways you could do it. Your first example was very close to correct

Using exists

# where a member_tag exists for each tag_id
Enum.reduce(tag_ids, query, fn id, query ->
  Ash.Query.filter(query, exists(member_tags, tag_id == ^id))
end)

Would do what you want. This will result in a rather large SQL query w/ an EXISTS per tag_id you’re searching for (and would probably be fine in general).

Using count/2

However, you could also do something like this which would result in joining once. Only works if your join resource is unique on member_id and tag_id.

# where the number of tags that exist with these tag_ids 
# equals the number of tag_ids I asked for
Ash.Query.filter(
  count(member_tags, query: [filter: tag_id in ^tag_ids]) == ^Enum.count(tag_ids)
)

And here you can see the syntax @sevenseacat was getting at in her first response. That syntax just happens to not apply to exists/2

You may want to profile/try each out to see how it performs in your use case.

6

They both worked!

I had to modify the second one, to add the query first argument

# where the number of tags that exist with these tag_ids 
# equals the number of tag_ids I asked for
Ash.Query.filter(
  query,
  count(member_tags, query: [filter: tag_id in ^tag_ids]) == ^Enum.count(tag_ids)
)

Indeed, the join table should be unique on Member and Tag, so this solution is probably better.

3 Likes