0

Wonder if someone can help me with an array aggregator issue

I’ve got a query that does a join using a joining table then it filters down all values that are inside a given array and filters out values that are in another array.

The code looks like this:

      Product
      |> join(:inner, [j], jt in "job_tech", on: j.id == jt.product_id)
      |> join(:inner, [j, jt], t in Tech, on: jt.ingredient_id == t.id)
      |> group_by([j], j.id)
      |> having_good_ingredients(good_ingredients)
      |> not_having_bad_ingredients(bad_ingredients)

With having_good_ingredients look like this:

def having_good_ingredients(query, good_ingredients) do
    if Enum.count(good_ingredients) > 0 do
      query
      |> having(fragment("array_agg(t2.name) && (?)::varchar[]", ^good_ingredients))
    else
      query
    end
  end

This works but it’ll grab all values that satisfy any of the values in the good_stacks array where I want them to only satisfy if all of the stacks work i.e. if I’ve got [A, C] in my array, I want to return values that have A AND C, not just A and not just C.

Anyone have any ideas?

Chris G
  • 449
  • 1
  • 5
  • 19

1 Answers1

0

I believe you want to use the @> operator, instead of the overlap && operator:

having(fragment("array_agg(t2.name) @> (?)::varchar[]", ^good_ingredients))

Reference: https://www.postgresql.org/docs/current/functions-array.html#ARRAY-OPERATORS-TABLE

José Valim
  • 50,409
  • 12
  • 130
  • 115