I’m not sure how it would interact with the prepare_query plumbing, but making the validation match [:field_that_should_be_unique, :org_id] (where org_id is the tenant FK) seems like it would get the right query.

Thanks, Matt! Yea, the plumbing is actually the issue. In general, the validation works, but the query doesn’t get executed because it needs the org_id added as an option to the query. The options I can provide to unsafe_validate_query/4 are used by that function and not being passed down to the query - unless you do multi-tenancy with prefixes. And this is what I just ended up doing: switched the app from foreign key to prefix-based multi-tenancy.

While the former appealed to me at first by being straightforward to implement, it needs little tweaks and changes all over the place, and sometimes I’d run into a wall, as the example above shows.

Thanks again!

I think, in theory, you have to pass your :skip_person_id to the :repo_opts as per this notation:

"""
  * `:repo_opts` - the options to pass to the `Ecto.Repo` call.
"""

It would appear the :repo_opts are not getting passed down to the prepare_query/3 function. But, I don’t know.

Oh, interesting! As part of the changeset… I might try this, but to be honest, that was the point when I switched to prefixes Too many things that kept popping up.. But I still have the old branch and see if I can get it to work that way. Thank you!

Just for reference I ran into the same problem today and found that a fix for this should be coming in the next Ecto release. unsafe_validate_unique/4 has been updated to accept a keyword list of repo_opts.

See the change here: ecto/lib/ecto/changeset.ex at 0b990278ca6b418c0137dd0bdf9f0c5003062629 · elixir-ecto/ecto · GitHub

Oh that’s good news, Andy! Thanks for letting us know!

I ran into the same problem and could fix it based on your post:

unsafe_validate_unique(:email, MyApp.Repo, repo_opts: [skip_org_id: true])

Thx andyjones11!