Searching for "set intersection" in arrays

[Wojtek Mach]

Hi all,

First of all, hats off to the Ecto team and all contributors for building such a great library.

I was really happy to see that Ecto supports syntax like:

where: "bug" in i.labels # which generates for Postgres: WHERE ('bug' = ANY("labels"))

Now, my question is if there's a convenient to do a "set intersection" in where clauses, e.g.: we want to find all issues which have labels: "kind:bug" and "level:advanced".

Today we can write it like this:

where: "kind:bug" in i.labels and "level:advanced" in i.labels # somewhat ugly, probably slow

or

where: fragment("labels @> ?", ["kind:bug", "level:advanced"] # postgres specific

Of course we can (and sometimes should) implement this with associations but let's ignore that for now.

Any other ideas how to achieve this in Ecto with today's or some imaginary syntax (that is not yet implemented)? Btw, how would this look like in pure Elixir? I could think of this so far:

Enum.all?(["kind:bug", "level:advanced"], &(&1 in labels))

MapSet.subset?(MapSet.new(["kind:bug", "level:advanced"]), labels)

Thanks,

Wojtek

[Ben Wilson]

Hey!

I guess I'm a bit confused about what the question is. As you've observed, ecto already has in syntax for generically handling this, and it also gives you the power to use platform specific syntax through fragments. The various JOIN directions also serve as set intersections across associations.

What is it that you want to achieve that can't be achieved right now?

- Ben

[Wojtek Mach]

Hey, sorry if I wasn't clear. I guess my question was if there's a way to have a generic "short" (avoiding the "and") syntax that the adapters could then provide platform specific implementation. 

I don't even know how it could like like, perhaps something like (which isn't valid Elixir syntax, unfortunately):

where: i.labels & ["kind:bug", "level:advanced"]

Suggestions for alternative syntax are welcome. Again, for all practical intents and purposes what we have right now in Ecto is more than enough. Disclaimer: I'm writing a toy adapter that could use this feature.

[Wojtek Mach]

I take that back, I just realized it's actually a valid syntax:

iex(4)> quote do: from i in Issue, where: i.labels & ["kind:bug", "level:advanced"]
{:from, [],
 [{:in, [context: Elixir, import: Kernel],
   [{:i, [], Elixir}, {:__aliases__, [alias: false], [:Issue]}]},
  [where: {{:., [], [{:i, [], Elixir}, :labels]}, [],
    [{:&, [], [["kind:bug", "level:advanced"]]}]}]]}