Many-to-many query without having a relation defined

I can execute many_to_many queries just fine when I have all the relationships/tables defined. However, I have a situation where one of the id columns in the “many to many table” is not part of my schema (it’s an external identifier). Here’s the detail:

defmodule Amplify.Models.AdPlatformAccount do
  schema "ad_platform_accounts" do
    field :access_token, :string

# this is the many to many model
defmodule Amplify.Models.AccountsAdPlatformAccounts do
  schema "accounts_ad_platform_accounts" do
    field :account_id, :string. # does not have its own table and is "external" to schema
    belongs_to :ad_platform_account, Amplify.Models.AdPlatformAccount

What I’m trying to do is execute the following query to get a list of AdPlatformAccount

select * 
from   ad_platform_accounts apc, accounts_ad_platform_accounts aapa
where  apc.ad_platform_id = aapa.ad_platform_account_id and 
       aapa.account_id = 'some_account_id'

How can I write an ecto query that accomplishes this?

Maybe the Schemaless queries can help you?

1 Like

Thank you! That makes complete sense and solves my problem.