title: Rails ActiveRecord::StatementInvalid: PG::Error: ERROR: missing FROM-clause entry for table
body: <p>I've a complex ActiveRecord query that I'm building up with different scopes, depending on the user selection. I'm using 2 gems, which seems to be problematic together, but I cannot find who's the culprit between :
  Texticle (for Postgresql full search) (2.0.3)
  Squeel (for ruby syntax use in Active Record query) squeel (0.9.5)
  Arel or Active Record themselves</p>
  
  <p>Here are my classes definitions:</p>
  
  <pre><code>class Event &lt; ActiveRecord::Base
    belongs_to :entity, :class_name =&gt; "Entity", :foreign_key =&gt; :entity_id
    belongs_to :place, :class_name =&gt; "Entity", :foreign_key =&gt; :place_id
  
  class Entity &lt; ActiveRecord::Base
    has_many :events, :foreign_key =&gt; 'entity_id'
    has_many :events, :foreign_key =&gt; 'place_id'
  </code></pre>
  
  <p>And finally, my query :</p>
  
  <pre><code>Event.joins{entity.outer}.joins{place.outer}.includes(:place).includes(:entity).textsearch('anystring'.downcase)
  </code></pre>
  
  <p>*This is an ActiveRecord::Relation object, which crash when calling it with to_a</p>
  
  <p>This gives me the following error :</p>
  
  <pre><code>ActiveRecord::StatementInvalid: PG::Error: ERROR:  missing FROM-clause entry for table "entities_events"
  LINE 1: ... AS t1_r30, "entities"."metro_area_id" AS t1_r31, "entities_...
                                                               ^
  : SELECT "events"."id" AS t0_r0, "events"."name" AS t0_r1, "events"."start_at" AS t0_r2, "events"."end_at" AS t0_r3, "events"."created_at" AS t0_r4, "events"."updated_at" AS t0_r5, "events"."entity_id" AS t0_r6, "events"."calendar_id" AS t0_r7, "events"."location" AS t0_r8, "events"."facebook_id" AS t0_r9, "events"."updated_time" AS t0_r10, "events"."privacy" AS t0_r11, "events"."venue_id" AS t0_r12, "events"."description" AS t0_r13, "events"."venue_city" AS t0_r14, "events"."venue_country" AS t0_r15, "events"."venue_state" AS t0_r16, "events"."venue_longitude" AS t0_r17, "events"."venue_latitude" AS t0_r18, "events"."yahoo_city" AS t0_r19, "events"."yahoo_country" AS t0_r20, "events"."yahoo_state" AS t0_r21, "events"."yahoo_updated_at" AS t0_r22, "events"."fb_updated_at" AS t0_r23, "events"."source_id" AS t0_r24, "events"."source_type" AS t0_r25, "events"."source_url" AS t0_r26, "events"."status" AS t0_r27, "events"."category" AS t0_r28, "events"."place_id" AS t0_r29, "events"."metro_area_id" AS t0_r30, "entities"."id" AS t1_r0, "entities"."image_url" AS t1_r1, "entities"."created_at" AS t1_r2, "entities"."updated_at" AS t1_r3, "entities"."name" AS t1_r4, "entities"."facebook_id" AS t1_r5, "entities"."link" AS t1_r6, "entities"."website" AS t1_r7, "entities"."company_overview" AS t1_r8, "entities"."mission" AS t1_r9, "entities"."category" AS t1_r10, "entities"."picture" AS t1_r11, "entities"."city" AS t1_r12, "entities"."zip" AS t1_r13, "entities"."country" AS t1_r14, "entities"."street" AS t1_r15, "entities"."state" AS t1_r16, "entities"."events_updated_at" AS t1_r17, "entities"."fblikes_updated_at" AS t1_r18, "entities"."fb_updated_at" AS t1_r19, "entities"."source_url" AS t1_r20, "entities"."source_type" AS t1_r21, "entities"."source_id" AS t1_r22, "entities"."place" AS t1_r23, "entities"."latitude" AS t1_r24, "entities"."longitude" AS t1_r25, "entities"."yahoo_city" AS t1_r26, "entities"."yahoo_state" AS t1_r27, "entities"."yahoo_country" AS t1_r28, "entities"."yahoo_updated_at" AS t1_r29, "entities"."fetched_elements_at" AS t1_r30, "entities"."metro_area_id" AS t1_r31, "entities_events"."id" AS t2_r0, "entities_events"."image_url" AS t2_r1, "entities_events"."created_at" AS t2_r2, "entities_events"."updated_at" AS t2_r3, "entities_events"."name" AS t2_r4, "entities_events"."facebook_id" AS t2_r5, "entities_events"."link" AS t2_r6, "entities_events"."website" AS t2_r7, "entities_events"."company_overview" AS t2_r8, "entities_events"."mission" AS t2_r9, "entities_events"."category" AS t2_r10, "entities_events"."picture" AS t2_r11, "entities_events"."city" AS t2_r12, "entities_events"."zip" AS t2_r13, "entities_events"."country" AS t2_r14, "entities_events"."street" AS t2_r15, "entities_events"."state" AS t2_r16, "entities_events"."events_updated_at" AS t2_r17, "entities_events"."fblikes_updated_at" AS t2_r18, "entities_events"."fb_updated_at" AS t2_r19, "entities_events"."source_url" AS t2_r20, "entities_events"."source_type" AS t2_r21, "entities_events"."source_id" AS t2_r22, "entities_events"."place" AS t2_r23, "entities_events"."latitude" AS t2_r24, "entities_events"."longitude" AS t2_r25, "entities_events"."yahoo_city" AS t2_r26, "entities_events"."yahoo_state" AS t2_r27, "entities_events"."yahoo_country" AS t2_r28, "entities_events"."yahoo_updated_at" AS t2_r29, "entities_events"."fetched_elements_at" AS t2_r30, "entities_events"."metro_area_id" AS t2_r31 FROM "events" LEFT OUTER JOIN "entities" ON "entities"."id" = "events"."entity_id" LEFT OUTER JOIN "entities" "places_events" ON "places_events"."id" = "events"."place_id" WHERE (to_tsvector('english', "events"."name"::text) @@ to_tsquery('english', 'canadiens'::text))  ORDER BY "rank0.11630770538778923" DESC
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:1106:in `async_exec'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:1106:in `exec_no_cache'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:650:in `block in exec_query'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activesupport-3.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:649:in `exec_query'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:1201:in `select'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:16:in `select_all'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:211:in `find_with_associations'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/relation.rb:170:in `exec_queries'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/relation.rb:159:in `block in to_a'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/explain.rb:40:in `logging_query_plan'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/relation.rb:158:in `to_a'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.1/lib/active_record/relation.rb:495:in `inspect'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.1/lib/rails/commands/console.rb:47:in `start'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.1/lib/rails/commands/console.rb:8:in `start'
       from /Users/guillaumenm/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.1/lib/rails/commands.rb:41:in `&lt;top (required)&gt;'
       from ./script/rails:6:in `require'
  
  If I call the to_sql method on the query, I get a working SQL statement like this :
   =&gt; "SELECT \"events\".*, ts_rank(to_tsvector('english', \"events\".\"name\"::text), to_tsquery('english', 'canadiens'::text)) AS \"rank0.7148935848557265\" FROM \"events\" LEFT OUTER JOIN \"entities\" ON \"entities\".\"id\" = \"events\".\"entity_id\" WHERE (to_tsvector('english', \"events\".\"name\"::text) @@ to_tsquery('english', 'canadiens'::text)) AND ((\"events\".\"start_at\" &gt;= '2012-02-19T00:00:00+00:00' AND \"events\".\"start_at\" &lt;= '2012-02-25T23:59:59+00:00')) ORDER BY \"rank0.7148935848557265\" DESC"
  </code></pre>
  
  <ul>
  <li>Is Active Record and ARel fully responsible to implement the full sql statement? </li>
  <li>Why to_sql doesn't produce the same result?</li>
  <li><p>Any way to force (without giving the full OUTER JOIN syntax) the join name?</p></li>
  <li><p>How can I be sure to capture the problematic piece to fill in the proper bug report? I'm pretty lost in my debugger trying to find what piece of code writes the incorrect join statement || or the incorrect SELECT clause with the invalid join association name.</p></li>
  </ul>
  
  <p>Thanks</p>