Help with passing an array (charlist) to a fragment

Questions / Help
,
1

I’m trying to order by a specific list of IDs (dynamicall). I went down the route of trying to build up a case statement but found that difficult, and then I found this stackoverflow which suggested using ORDER BY array_position(ARRAY['f', 'p', 'i', 'a']::varchar[], x_field)

So I tried that in my fragment: |> order_by([p, pt], fragment("array_position(ARRAY[?]::int[], ?)", ^ids, p.id)) but my ids are being interpreted as a charlist [88], and I imagine this isn’t a valid way to pass data around perhaps? I also tried building up a string of “id,id” and using string_to_array but didn’t have luck there either.

Is there something I’m missing…or is this approach just flawed from the start.

2

I believe you need to do array_position(?::int[]…. Currently you’re nesting a list inside an array and of course Ecto will treat your ids as a list of characters.

3

Yip thanks - that was it…

fragment("array_position(?::int[], ?)", ^ids, p.id)

I’d had a horrible hack of parsing it into a string and unpacking it (don’t ask, just wanted to get it working…)

id_string = Enum.join(Enum.uniq(ids), ",")
#....
fragment("array_position(string_to_array(?, ',')::int[], ?)", ^id_string, p.id)

but your way is definitely nicer - thanks :slight_smile: