Using `fragment` with an `in` clause

Questions & Help Questions
1

I’ve got a dynamic ecto query builder that dynamically builds queries based on JSON filters. Apart from static values included in the filters, filters can also include special values, like __CURRENT_USER__, which is converted by the filter generator to a fragment calling a database function—specifically, a postgres function called app_public.current_user_id(). So, for example, a filter condition that looks like this:

%{
    value: ["__CURRENT_USER__"],
    metadata: %{},
    field: "__createdBy__",
    keyword: "in",
   }

Generates a query that looks like this:

#Ecto.Query<from o0 in AboardEx.Repo.Object, prefix: "app_public",
 where: o0.created_by in ^[dynamic([], fragment("app_public.current_user_id()"))]>

Which is exactly what I want it to look like. But when I run the query w/Repo.all(), I get the following error:

** (Ecto.Query.CastError) lib/aboard_ex/repo/filter.ex:262: value `[dynamic([], fragment("app_public.current_user_id()"))]` in `where` cannot be cast to type {:in, :binary_id} in query:

from o0 in AboardEx.Repo.Object,
  prefix: "app_public",
  where: o0.created_by in ^[dynamic([], fragment("app_public.current_user_id()"))],
  select: o0

    (elixir 1.16.1) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir 1.16.1) lib/enum.ex:1826: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir 1.16.1) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.11.1) lib/ecto/repo/queryable.ex:214: Ecto.Repo.Queryable.execute/4
    (ecto 3.11.1) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    iex:29: (file)

I understand the error, but I’m wondering if there’s anyway to work around this—short of converting the __CURRENT_USER__ string to it’s ID in the elixir code rather than using the fragment. In postgres, as far as I can tell, this would be perfectly valid code. E.g.:

SELECT * into _objects
  FROM app_public.objects 
  WHERE created_by IN (app_public.current_user_id(), 'SOME-OTHER-ID');

Is it possible to achieve this with Ecto?

2

Does this work?

where: o0.created_by in ^dynamic([], fragment("(?)", "app_public.current_user_id()"))
3

Try wrapping things in type(…, :binary_id) to provide some context for ecto.

4

Same issue, unfortunately.

5

Huh, I’d never seen that.

If I’m using it correctly (which possibly I’m not…), it doesn’t seem to solve the problem, either. Here’s what I’ve tried:

  def maybe_convert_value("__CURRENT_USER__", _, _),
    do: dynamic(type(fragment("app_public.current_user_id()"), :binary_id))

Which returns:

(Ecto.Query.CastError) lib/aboard_ex/repo/filter.ex:118: value `[dynamic([], type(fragment("app_public.current_user_id()"), :binary_id))]` in `where` cannot be cast to type {:in, :binary_id} in query

It certainly looks like it would accomplish the goal, but doesn’t seem to do it.

6

You could also try type([…], {:array, :binary_id}) and have the type enforcement one more layer out.

7

Looks like Ecto is having trouble with the in macro probably expecting an Elixir list. Could the type{:array, :binary_id} work?

The right side may either be a list, a literal list or even a column in the database with array type:
Ecto.Query.API — Ecto v3.14.0

Or alternatively shoving the whole thing in the fragment to match your example Postgres query?

where: ^[dynamic([], fragment("? IN (app_public.current_user_id())", o0.created_by))]
8

Hm. Really appreciate the suggestions! Unfortunately still running into the same issue. It seems like Ecto is trying to cast the value regardless of how or whether or not I type it.

9

Unfortunately I don’t think I can dynamically achieve my goal here, since the values referenced in the in clause need to be able to include my dynamic value and/or static values.

10

For more context, when I do that, the query looks like this:

#Ecto.Query<from o0 in AboardEx.Repo.Object, prefix: "app_public",
 where: o0.created_by in type(
       ^[dynamic([], fragment("app_public.current_user_id()"))],
       {:array, :binary_id}
     )>

Which seems promising! But then the executed result gives me:

** (Ecto.Query.CastError) lib/aboard_ex/repo/filter.ex:118: value `[dynamic([], fragment("app_public.current_user_id()"))]` in `where` cannot be cast to type {:array, :binary_id} in query:

from o0 in AboardEx.Repo.Object,
  prefix: "app_public",
  where: o0.created_by in type(
       ^[dynamic([], fragment("app_public.current_user_id()"))],
       {:array, :binary_id}
     ),
  select: o0
11

Hang on, dynamic is being wrapped in a list and then interpolated into the type function. Try this?

where: o0.created_by in type(
       ^dynamic([], fragment("app_public.current_user_id()")),
       {:array, :binary_id}
     )
12

Right, but the goal is for this dynamic element to be wrapped in a list. It’s one possible value in a list of possible values. In SQL, what I’m trying to achieve is this:

SELECT * into _objects
FROM app_public.objects 
WHERE created_by IN (app_public.current_user_id(), 'SOME_OTHER_ID');

Which works fine there.

13

try to rewrite the query to the following SQL

WHERE 
  created_by = app_public.current_user_id() OR
  created_by IN('SOME_OTHER_ID1', 'SOME_OTHER_ID2')
14

Yes, but the type macro is telling ecto to expect the output of dynamic to already be an array of ids, so interpolating a list shouldn’t be necessary if the postgres function is already returning an array.

Sorry if I’m missing the mark and misunderstanding things.

15

The postgres function is returning a single ID value, not an array of values.

16

Ah, that I think can work. Not my ideal but definitely better than not working!!

17

I try to reproduce the error locally.
So, if you remove the square brackets, then the error is different

** (Ecto.QueryError) iex:4: invalid dynamic expression in query:
...

dynamic expressions can only be interpolated at the top level of where, having, group_by, order_by, select, update or a join's on

I think this is the core issue. You just can’t use dynamic as a value for ==,in etc

However, this approach should work:

where: o0.created_by in fragment("ARRAY_APPEND(?, app_public.current_user_id())", ^["id1", "id2"])
18

Seems like you can use dynamic in == — I’ve done it. But def doesn’t seem to work in in.

That absolutely does work! Seems like a nice middle ground, thank you for the suggestion!

19

Right, my bad. I did miss this part of the code:

(app_public.current_user_id(), 'SOME_OTHER_ID')

Is SOME_OTHER_ID coming from the filter and you also want to interpolate it with the pg function?

I’ve never used this but splice/1 might help if you have a variable number of ids coming from the filter?

filter_ids = ["ANY_1", "ANY_2"]

dyn_in =
  dynamic([o], fragment("? in (?)", o.created_by, splice(^[fragment("app_public.current_user_id()") | filter_ids])))

from(o in Object, where: ^dyn_in, prefix: :app_public, select: o)
20 
"mytable" |> where(my_field: ^dynamic(false)) |> MyApp.Repo.exists?()
** (Ecto.QueryError) iex:10: invalid dynamic expression in query:

from m0 in "mytable",
  where: m0.my_field == ^dynamic([], false),
  limit: 1,
  select: 1

dynamic expressions can only be interpolated at the top level of where, having, group_by, order_by, select, update or a join's on