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
end
end
# 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
end
end
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?