Rails的ActiveRecord :: StatementInvalid：PG ::错误：错误：缺少表的FROM-clause条目

问题描述：

我有一个复杂的ActiveRecord查询，我建立了不同的范围，这取决于用户的选择。我使用2个宝石，这似乎是一起问题，但我找不到谁是之间的罪魁祸首： Texticle（用于Postgresql完整搜索）（2.0.3） Squeel（用于在Active Record查询中使用ruby语法）squeel 0.9.5）阿雷尔或活动记录自己Rails的ActiveRecord :: StatementInvalid：PG ::错误：错误：缺少表的FROM-clause条目

这里是我的类定义：

class Event < ActiveRecord::Base 
    belongs_to :entity, :class_name => "Entity", :foreign_key => :entity_id 
    belongs_to :place, :class_name => "Entity", :foreign_key => :place_id 

class Entity < ActiveRecord::Base 
    has_many :events, :foreign_key => 'entity_id' 
    has_many :events, :foreign_key => 'place_id'

最后，我查询：

Event.joins{entity.outer}.joins{place.outer}.includes(:place).includes(:entity).textsearch('anystring'.downcase)

*这是一个ActiveRecord :: Relation对象，哪个CR灰时to_a

调用呢？这使我有以下错误：

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 `<top (required)>' 
    from ./script/rails:6:in `require' 

If I call the to_sql method on the query, I get a working SQL statement like this : 
=> "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\" >= '2012-02-19T00:00:00+00:00' AND \"events\".\"start_at\" <= '2012-02-25T23:59:59+00:00')) ORDER BY \"rank0.7148935848557265\" DESC"

是活动记录和AREL全面负责实施完整的SQL语句？
为什么to_sql不会产生相同的结果？
任何强制的方式（没有提供完整的OUTER JOIN语法）联接名称？
我该如何确定捕获有问题的作品以填写正确的错误报告？我很迷茫，在我的调试器中试图找到哪些代码写入不正确的连接语句||或者带有无效联接关联名称的错误SELECT子句。

感谢

答

多的调试之后，我发现这个问题是倍数:-(。

首先，加入顺序必须符合包括顺序，因此，2号线，没有按”牛逼产生相同的SQL查询。命名别名是不同的。我相信这是一个积极的记录\ AREL问题，我会标志。

Event.joins{entity.outer}.joins{place.outer}.includes(:place).includes(:entity)

比

不同

Event.joins{entity.outer}.joins{place.outer}.includes(:entity).includes(:place)

但是，这个问题是透明的，直到我为我的文本搜索添加了另一个范围。我的猜测是因为SQL语句更改为为每列添加列别名。该别名也使用了表别名，这与FROM和JOIN子句中的不同。

改变顺序，并没有解决我所有的问题。我最终发现另一个问题，即我的文本全文搜索的SELECT子句从查询转到数据库时被剥离。相反，我得到这个错误：

ActiveRecord::StatementInvalid: PG::Error: ERROR: column "rank0.8601929506100121" does not exist 
LINE 1: ...o_tsquery('english', 'canadiens'::text)) ORDER BY "rank0.860...

我也相信这是无论是texticle，squeel或ActiveRecord的/阿雷尔的错误，不知道哪一个尚未...

那么痛苦，我想写了我自己的SQL语句再一次手动...

这吓坏了丑陋。我遇到了类似的问题。 – jevy 2012-03-14 19:21:59

我所做的是取出包含部分。这是抛出定制选择语句的部分。我通过SQUEEL插件使用了左外连接，并为我的“包含”类手动编写了select语句。最后，我想限制选择的表现，所以我想，我正处在我想要的地步。所以这个bug最后是ActiveRecord :: includes，并且间接地，Texticle允许包含AR关系对象。 – xlash 2012-03-20 05:07:32

我们使用的是使用arel“where”方法的命名范围。我们改变它使用旧学校“条件”的方式，它运作良好。以某种方式看起来像一个方法错误。 – jevy 2012-03-22 19:53:14

答

我刚喝了同样的错误上来对我来说，与这行代码：

SampleItem.joins(:campaign_market).where(campaign_market: {market_id: current_admin_user.market_id})

我的事业远比上面的一个简单，joins(:campaign_market)是正确的，因为它是一个belongs_to /父记录然而：

.where(campaign_market: {market_id: current_admin_user.market_id})

应该是：

.where(campaign_markets: {market_id: current_admin_user.market_id})

.where()子句总是需要复数表名称，而不是关联名称。

Rails的ActiveRecord :: StatementInvalid：PG ::错误：错误：缺少表的FROM-clause条目

相关推荐