Writing query to join three tables

Questions / Help
, , , , ,
1

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

Screen Shot 2021-08-19 at 9.00.56 PM
Screen Shot 2021-08-19 at 9.00.56 PM1394Ă—716 33.3 KB

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 =
    CredFlag
    |> 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?

2

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

3

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

4

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.

2 Likes
5

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)
  end

defp query_creds_by_flag([] = _creds, _flag_id) do
    []
  end

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

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

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

  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))
  end

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