Writing query to join three tables

I am trying to work between two schemas - public and private schema.

I have two schemas Cred and Flag in public schema. Both are not directly connected. There is CredFlag in private schema which I believe can be connected through tenant with Cred. Also CredFlag belongs to Flag in public schema.

I need to write a query to get all flags of a cred.

def get_flags_for_cred((%Cred{} = cred) do
credflags =
    |> where([cf], cf.tenant == ^cred.schema)
    |> select([:flag_id])

Not sure how to join the above query with Flag. How to write query for this case that deals with public and private schema? Also is my query above right?

Could you please elaborate on the “tenant” part? How CredFlag is associated with Cred?

I think the tenant called “Gary” lives in schema called “Gary”

You cannot write it like that, as AFAIK schema name cannot be dynamic. What you need to do is:

prefix = Repo.one!(from c in Cred, where: c.id == ^tennant_id, select: c.tennant)
cred_flags = put_query_prefix(CredFlag, prefix)

from cred_flag in cred_flags,
  inner_join: flag in Flag,
  on: cred_flag.flag_id == flag.id

Alternatively (additionally) you can set @schema_prefix in Flag to make it a little bit easier.


This is what I do for the opposite, ie getting creds for flag

def get_creds_for_flag(%Flag{} = flag) do
    list_creds() |> query_creds_by_flag(flag.id)

defp query_creds_by_flag([] = _creds, _flag_id) do

  defp query_creds_by_flag(creds, flag_id) do
    cred_queries_union =
      query_cred_flags(creds, flag_id) |> subquery()

    |> join(:inner, [c], cf in ^cred_queries_union, on: cf.tenant == c.tenant)
    |> Repo.all()

  defp query_cred_flags([next_cred], flag_id) do
    query_cred_flags(next_cred, flag_id)

  defp query_cred_flags([next_cred | rest_creds], flag_id) do
    query_cred_flags(next_cred, flag_id)
    |> union(^query_cred_flags(rest_creds, flag_id))

  defp query_cred_flags(%Cred{} = cred, flag_id) do
    |> select([cf], %{
      tenant: type(^cred.tenant, :string),
      flag_id: cf.flag_id
    |> where(flag_id: ^flag_id)
    |> put_query_prefix(cred.tenant)