This has been tremendously frustrating. I'm trying to get a has_many through working, and I think I'm just too close to this to see something super obvious. Each step works correctly, and the SQL that Rails is generating works, but together in the console it's not.
The one weird thing about this whole setup is that there are a couple of tables in a salesforce schema, and the tablename and primary key aren't standard. Here's the basic structure:
class Contact self.table_name = 'salesforce.contact' self.primary_key = 'sfid' has_many :content_accesses has_many :inventories, through: :content_accesses # I've tried inventory and inventorys, just to ensure it's not Rails magic end class ContentAccess belongs_to :inventory belongs_to :contact end class Inventory self.table_name = 'salesforce.inventory__c' self.primary_key = 'sfid' has_many :content_accesses, foreign_key: 'inventory_id' end Works:
c = Contact.first c.content_accesses # works, gives the related items c.content_accesses.first.inventory # works, gives the related Inventory item Error:
c.inventories # Gives: # ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: relation "content_accesses" does not exist) # LINE 1: ..._c".* FROM "salesforce"."inventory__c" INNER JOIN "content_a... # ^ # : SELECT "salesforce"."inventory__c".* FROM "salesforce"."inventory__c" INNER JOIN "content_accesses" ON "salesforce"."inventory__c"."sfid" = "content_accesses"."inventory_id" WHERE "content_accesses"."contact_id" = $1 LIMIT $2 When I run that query through Postico, though, it works fine. 🤬
Edited to add:
- I moved content_accesses into the salesforce schema, and set self.table_name on the model correctly, but the problem still happens. As such, I don't think this is related to being cross-schema.
- That only makes this problem weirder to me. :(
DDL for the tables:
CREATE TABLE salesforce.inventory__c ( createddate timestamp without time zone, isdeleted boolean, name character varying(80), systemmodstamp timestamp without time zone, inventory_unique_name__c character varying(255), sfid character varying(18), id integer DEFAULT nextval('salesforce.inventory__c_id_seq'::regclass) PRIMARY KEY, _hc_lastop character varying(32), _hc_err text ); CREATE UNIQUE INDEX inventory__c_pkey ON salesforce.inventory__c(id int4_ops); CREATE INDEX hc_idx_inventory__c_systemmodstamp ON salesforce.inventory__c(systemmodstamp timestamp_ops); CREATE UNIQUE INDEX hcu_idx_inventory__c_sfid ON salesforce.inventory__c(sfid text_ops); CREATE TABLE salesforce.contact ( lastname character varying(80), mailingpostalcode character varying(20), accountid character varying(18), assistantname character varying(40), name character varying(121), mobilephone character varying(40), birthdate date, phone character varying(40), mailingstreet character varying(255), isdeleted boolean, assistantphone character varying(40), systemmodstamp timestamp without time zone, mailingstatecode character varying(10), createddate timestamp without time zone, mailingcity character varying(40), salutation character varying(40), title character varying(128), mailingcountrycode character varying(10), firstname character varying(40), email character varying(80), sfid character varying(18), id integer DEFAULT nextval('salesforce.contact_id_seq'::regclass) PRIMARY KEY, _hc_lastop character varying(32), _hc_err text ); CREATE UNIQUE INDEX contact_pkey ON salesforce.contact(id int4_ops); CREATE INDEX hc_idx_contact_systemmodstamp ON salesforce.contact(systemmodstamp timestamp_ops); CREATE UNIQUE INDEX hcu_idx_contact_sfid ON salesforce.contact(sfid text_ops); CREATE TABLE content_accesses ( id BIGSERIAL PRIMARY KEY, inventory_id character varying(20), contact_id character varying(20), created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL ); CREATE UNIQUE INDEX content_accesses_pkey ON content_accesses(id int8_ops); Edit 2: As part of debugging, I've tried running the generated query in the console:
- If I run the generated query using
ActiveRecord::Base.connection.executethe query works. - If I run it through
Contact.connection.executeit gives the same error.
It feels like Rails is not figuring something out, but I can't figure out where or why or what.
Edit 3: As requested, the framework trace:
activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:669:in `prepare' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:669:in `block in prepare_statement' /Users/timsullivan/.rvm/rubies/ruby-2.5.1/lib/ruby/2.5.0/monitor.rb:226:in `mon_synchronize' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:664:in `prepare_statement' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:609:in `exec_cache' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `execute_and_clear' activerecord (5.2.0) lib/active_record/connection_adapters/postgresql/database_statements.rb:81:in `exec_query' activerecord (5.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:469:in `select_prepared' activerecord (5.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:55:in `select_all' activerecord (5.2.0) lib/active_record/connection_adapters/abstract/query_cache.rb:101:in `select_all' activerecord (5.2.0) lib/active_record/querying.rb:41:in `find_by_sql' activerecord (5.2.0) lib/active_record/relation.rb:554:in `block in exec_queries' activerecord (5.2.0) lib/active_record/relation.rb:578:in `skip_query_cache_if_necessary' activerecord (5.2.0) lib/active_record/relation.rb:542:in `exec_queries' activerecord (5.2.0) lib/active_record/association_relation.rb:34:in `exec_queries' activerecord (5.2.0) lib/active_record/relation.rb:414:in `load' activerecord (5.2.0) lib/active_record/relation.rb:200:in `records' activerecord (5.2.0) lib/active_record/relation.rb:195:in `to_ary' activerecord (5.2.0) lib/active_record/relation/finder_methods.rb:530:in `find_nth_with_limit' activerecord (5.2.0) lib/active_record/associations/collection_proxy.rb:1136:in `find_nth_with_limit' activerecord (5.2.0) lib/active_record/relation/finder_methods.rb:515:in `find_nth' activerecord (5.2.0) lib/active_record/relation/finder_methods.rb:125:in `first' actionview (5.2.0) lib/action_view/template.rb:159:in `block in render' activesupport (5.2.0) lib/active_support/notifications.rb:170:in `instrument' actionview (5.2.0) lib/action_view/template.rb:354:in `instrument_render_template' actionview (5.2.0) lib/action_view/template.rb:157:in `render' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:54:in `block (2 levels) in render_template' actionview (5.2.0) lib/action_view/renderer/abstract_renderer.rb:44:in `block in instrument' activesupport (5.2.0) lib/active_support/notifications.rb:168:in `block in instrument' activesupport (5.2.0) lib/active_support/notifications/instrumenter.rb:23:in `instrument' activesupport (5.2.0) lib/active_support/notifications.rb:168:in `instrument' actionview (5.2.0) lib/action_view/renderer/abstract_renderer.rb:43:in `instrument' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:53:in `block in render_template' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:61:in `render_with_layout' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:52:in `render_template' actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:16:in `render' actionview (5.2.0) lib/action_view/renderer/renderer.rb:44:in `render_template' actionview (5.2.0) lib/action_view/renderer/renderer.rb:25:in `render' actionview (5.2.0) lib/action_view/rendering.rb:103:in `_render_template' actionpack (5.2.0) lib/action_controller/metal/streaming.rb:219:in `_render_template' actionview (5.2.0) lib/action_view/rendering.rb:84:in `render_to_body' actionpack (5.2.0) lib/action_controller/metal/rendering.rb:52:in `render_to_body' actionpack (5.2.0) lib/action_controller/metal/renderers.rb:142:in `render_to_body' actionpack (5.2.0) lib/abstract_controller/rendering.rb:25:in `render' actionpack (5.2.0) lib/action_controller/metal/rendering.rb:36:in `render' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:46:in `block (2 levels) in render' activesupport (5.2.0) lib/active_support/core_ext/benchmark.rb:14:in `block in ms' /Users/timsullivan/.rvm/rubies/ruby-2.5.1/lib/ruby/2.5.0/benchmark.rb:308:in `realtime' activesupport (5.2.0) lib/active_support/core_ext/benchmark.rb:14:in `ms' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:46:in `block in render' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:87:in `cleanup_view_runtime' activerecord (5.2.0) lib/active_record/railties/controller_runtime.rb:31:in `cleanup_view_runtime' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:45:in `render' actionpack (5.2.0) lib/action_controller/metal/implicit_render.rb:35:in `default_render' actionpack (5.2.0) lib/action_controller/metal/basic_implicit_render.rb:6:in `block in send_action' actionpack (5.2.0) lib/action_controller/metal/basic_implicit_render.rb:6:in `tap' actionpack (5.2.0) lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action' actionpack (5.2.0) lib/abstract_controller/base.rb:194:in `process_action' actionpack (5.2.0) lib/action_controller/metal/rendering.rb:30:in `process_action' actionpack (5.2.0) lib/abstract_controller/callbacks.rb:42:in `block in process_action' activesupport (5.2.0) lib/active_support/callbacks.rb:132:in `run_callbacks' actionpack (5.2.0) lib/abstract_controller/callbacks.rb:41:in `process_action' actionpack (5.2.0) lib/action_controller/metal/rescue.rb:22:in `process_action' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:34:in `block in process_action' activesupport (5.2.0) lib/active_support/notifications.rb:168:in `block in instrument' activesupport (5.2.0) lib/active_support/notifications/instrumenter.rb:23:in `instrument' activesupport (5.2.0) lib/active_support/notifications.rb:168:in `instrument' actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:32:in `process_action' actionpack (5.2.0) lib/action_controller/metal/params_wrapper.rb:256:in `process_action' activerecord (5.2.0) lib/active_record/railties/controller_runtime.rb:24:in `process_action' actionpack (5.2.0) lib/abstract_controller/base.rb:134:in `process' actionview (5.2.0) lib/action_view/rendering.rb:32:in `process' actionpack (5.2.0) lib/action_controller/metal.rb:191:in `dispatch' actionpack (5.2.0) lib/action_controller/metal.rb:252:in `dispatch' actionpack (5.2.0) lib/action_dispatch/routing/route_set.rb:52:in `dispatch' actionpack (5.2.0) lib/action_dispatch/routing/route_set.rb:34:in `serve' actionpack (5.2.0) lib/action_dispatch/journey/router.rb:52:in `block in serve' actionpack (5.2.0) lib/action_dispatch/journey/router.rb:35:in `each' actionpack (5.2.0) lib/action_dispatch/journey/router.rb:35:in `serve' actionpack (5.2.0) lib/action_dispatch/routing/route_set.rb:840:in `call' warden (1.2.7) lib/warden/manager.rb:36:in `block in call' warden (1.2.7) lib/warden/manager.rb:35:in `catch' warden (1.2.7) lib/warden/manager.rb:35:in `call' rack (2.0.5) lib/rack/tempfile_reaper.rb:15:in `call' rack (2.0.5) lib/rack/etag.rb:25:in `call' rack (2.0.5) lib/rack/conditional_get.rb:25:in `call' rack (2.0.5) lib/rack/head.rb:12:in `call' actionpack (5.2.0) lib/action_dispatch/http/content_security_policy.rb:18:in `call' rack (2.0.5) lib/rack/session/abstract/id.rb:232:in `context' rack (2.0.5) lib/rack/session/abstract/id.rb:226:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/cookies.rb:670:in `call' activerecord (5.2.0) lib/active_record/migration.rb:559:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/callbacks.rb:28:in `block in call' activesupport (5.2.0) lib/active_support/callbacks.rb:98:in `run_callbacks' actionpack (5.2.0) lib/action_dispatch/middleware/callbacks.rb:26:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/executor.rb:14:in `call' airbrake (7.2.1) lib/airbrake/rack/middleware.rb:52:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/debug_exceptions.rb:61:in `call' web-console (3.6.1) lib/web_console/middleware.rb:135:in `call_app' web-console (3.6.1) lib/web_console/middleware.rb:30:in `block in call' web-console (3.6.1) lib/web_console/middleware.rb:20:in `catch' web-console (3.6.1) lib/web_console/middleware.rb:20:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/show_exceptions.rb:33:in `call' railties (5.2.0) lib/rails/rack/logger.rb:38:in `call_app' railties (5.2.0) lib/rails/rack/logger.rb:26:in `block in call' activesupport (5.2.0) lib/active_support/tagged_logging.rb:71:in `block in tagged' activesupport (5.2.0) lib/active_support/tagged_logging.rb:28:in `tagged' activesupport (5.2.0) lib/active_support/tagged_logging.rb:71:in `tagged' railties (5.2.0) lib/rails/rack/logger.rb:26:in `call' sprockets-rails (3.2.1) lib/sprockets/rails/quiet_assets.rb:13:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/remote_ip.rb:81:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/request_id.rb:27:in `call' rack (2.0.5) lib/rack/method_override.rb:22:in `call' rack (2.0.5) lib/rack/runtime.rb:22:in `call' activesupport (5.2.0) lib/active_support/cache/strategy/local_cache_middleware.rb:29:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/executor.rb:14:in `call' actionpack (5.2.0) lib/action_dispatch/middleware/static.rb:127:in `call' rack (2.0.5) lib/rack/sendfile.rb:111:in `call' webpacker (3.4.3) lib/webpacker/dev_server_proxy.rb:18:in `perform_request' rack-proxy (0.6.4) lib/rack/proxy.rb:57:in `call' railties (5.2.0) lib/rails/engine.rb:524:in `call' puma (3.11.4) lib/puma/configuration.rb:225:in `call' puma (3.11.4) lib/puma/server.rb:632:in `handle_request' puma (3.11.4) lib/puma/server.rb:446:in `process_client' puma (3.11.4) lib/puma/server.rb:306:in `block in run' puma (3.11.4) lib/puma/thread_pool.rb:120:in `block in spawn_thread' 1 Answers
Answers 1
Since you say, the generated SQL works when you directly invoke it the problem root lies somewhere in the process of mapping the returned data back to Objects. Even though your setup looks fine, it seems pretty non standard, so I would try giving rails more hints on how the associations belong together.
To start with you should set a source for your through relation (docs):
has_many :inventories, through: :content_accesses, source: :inventory If that still does not give rails the right clue, you can try setting inverse_of, foreign_key, primary_key and even class_name on the other belongs_to and has_many associations, to give rails the required hints. It is hard to tell what could possibly help, but in non standard setups you sometimes experience certain problems with automatically inferred names.
0 comments:
Post a Comment