ActiveRecord::StatementInvalid: PG::UndefinedTable in many to many relation but table exists

Question

I have an easy many to many relation and It doesn't work and I cannot understand why. I'm sure that is something so obvious... but..

class Content < ApplicationRecord
  has_many                :content_brands
  has_many                :brands,                  through:    :content_brands
end

class ContentBrand < ApplicationRecord
  belongs_to :content
  belongs_to :brand
end

class Brand < ApplicationRecord
  establish_connection Rails.application.config.brands_database_configuration

  has_many :content_brands
  has_many :contents, through: :content_brands
end

But

irb(main):002:0> Content.first.brands
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERRORE:  la relazione "content_brands" non esiste
LINE 1: SELECT  "brands".* FROM "brands" INNER JOIN "content_brands"...
                                                    ^
: SELECT  "brands".* FROM "brands" INNER JOIN "content_brands" ON "brands"."id" = "content_brands"."brand_id" WHERE "content_brands"."content_id" = $1 ORDER BY "brands"."name" ASC LIMIT $2

The table exists, I can query it

irb(main):006:0> ContentBrand.first.brand
  ContentBrand Load (0.5ms)  SELECT  "content_brands".* FROM "content_brands" ORDER BY "content_brands"."id" ASC LIMIT $1  [["LIMIT", 1]]
  Brand Load (27.4ms)  SELECT  "brands".* FROM "brands" WHERE "brands"."id" = $1 ORDER BY "brands"."name" ASC LIMIT $2  [["id", 1], ["LIMIT", 1]]
=> #<Brand id: 1, name: "Nokia", logo: "nokia.jpeg", created_at: "2016-12-08 15:50:48", updated_at: "2017-02-02 15:51:43", web_site: "http://www.nokia.it">

Why?

I'm getting crazy because the inverse relation works

Brand.first.contents
  Brand Load (25.8ms)  SELECT  "brands".* FROM "brands" ORDER BY "brands"."name" ASC LIMIT $1  [["LIMIT", 1]]
  Content Load (0.7ms)  SELECT  "contents".* FROM "contents" INNER JOIN "content_brands" ON "contents"."id" = "content_brands"."content_id" WHERE "content_brands"."brand_id" = $1 ORDER BY "contents"."published_at" DESC LIMIT $2  [["brand_id", 391], ["LIMIT", 11]]
=> #<ActiveRecord::Associations::CollectionProxy []>
irb(main):011:0>

Update: I forgot to tell you that Brand is on another database...

max · Answer 1 · 2019-04-29T15:34:14.290

You can't setup associations to a model that is stored in another database in ActiveRecord. Which makes sense since you can't join another database in a single query in Postgres without jumping through some pretty serious hoops (Postgres_FDW). And with the polyglot nature of ActiveRecord this would just be too much complexity for a very limited use case.

If its in any way possible I would switch to a single database setup even if it means that you have to duplicate data.

If you look at the "inverse query" you can see that it works because its not a single query:

  # queries the "brands" database
  Brand Load (25.8ms)  SELECT  "brands".* FROM "brands" ORDER BY "brands"."name" ASC LIMIT $1  [["LIMIT", 1]]

  # queries your main database
  Content Load (0.7ms)  SELECT  "contents".* FROM "contents" INNER JOIN "content_brands" ON "contents"."id" = "content_brands"."content_id" WHERE "content_brands"."brand_id" = $1 ORDER BY "contents"."published_at" DESC LIMIT $2  [["brand_id", 391], ["LIMIT", 11]]

However this does not mean that the concept is feasible.

ActiveRecord::StatementInvalid: PG::UndefinedTable in many to many relation but table exists

1 Answers1