Using custom SELECT results in invalid SQL #11

Closed
yholkamp opened this Issue Sep 26, 2009 · 3 comments

Projects

None yet

2 participants

@yholkamp

I'm using a custom SELECT to calculate a difference between to datetimes, which I want to use as a sort/filter condition using Will_Paginate. However, Will_Paginate doesn't account for a custom SELECT field when running the COUNT query, resulting in invalid SQL.

The code I'm using is the following;
if RAILS_ENV != "production"
select = ", strftime("%s",COALESCE(activities.stop, 'now')) - strftime("%s",activities.start) as duration"
else
select = "
, UNIX_TIMESTAMP(COALESCE(activities.stop, now())) - UNIX_TIMESTAMP(activities.start) as duration"
end

condition = self.conditions(filter)
paginate(
  :select => select,
  :order => PageMethods.sort_order(%w{projects.name start stop duration}, sort ? sort : 'start_reverse', 'start'),
  :conditions => condition,
  :include => [:project, {:project => :client}],
  :per_page => limit,
  :page => page)

And the error generated SQL/error;

SQLite3::SQLException: no such column: duration: SELECT count(*) AS count_all FROM "activities" WHERE (duration < '5') AND ("activities".user_id = 1)

D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract_adapter.rb:212:in log' D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/sqlite_adapter.rb:160:inexecute'
D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/sqlite_adapter.rb:405:in catch_schema_changes' D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/sqlite_adapter.rb:160:inexecute'
D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/sqlite_adapter.rb:308:in select' D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/database_statements.rb:7:inselect_all_without_query_cache'
D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in select_all' D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/query_cache.rb:81:incache_sql'
D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in select_all' D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/database_statements.rb:13:inselect_one'
D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/database_statements.rb:19:in select_value' D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/calculations.rb:235:inexecute_simple_calculation'
D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/calculations.rb:134:in calculate' D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/calculations.rb:130:incatch'
D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/calculations.rb:130:in calculate' D:/software/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/calculations.rb:48:incount'
D:/software/ruby/lib/ruby/gems/1.8/gems/mislav-will_paginate-2.3.11/lib/will_paginate/finder.rb:223:in wp_count' D:/software/ruby/lib/ruby/gems/1.8/gems/mislav-will_paginate-2.3.11/lib/will_paginate/finder.rb:235:incall'
D:/software/ruby/lib/ruby/gems/1.8/gems/mislav-will_paginate-2.3.11/lib/will_paginate/finder.rb:235:in wp_count' D:/software/ruby/lib/ruby/gems/1.8/gems/mislav-will_paginate-2.3.11/lib/will_paginate/finder.rb:85:inpaginate'
D:/software/ruby/lib/ruby/gems/1.8/gems/mislav-will_paginate-2.3.11/lib/will_paginate/collection.rb:87:in create' D:/software/ruby/lib/ruby/gems/1.8/gems/mislav-will_paginate-2.3.11/lib/will_paginate/finder.rb:76:inpaginate'
D:/web/Another/app/models/activity.rb:68:in search' D:/web/Another/app/controllers/activities_controller.rb:126:infind_activities'

@mislav
Owner
mislav commented Sep 26, 2009

Try this as a workaround:

paginate(:select => select, :count => {:select => select}, ...)
@yholkamp

Thanks a lot for the quick reply and your workaround will probably work in a lot of cases, but since I was trying to build invalid SQL here; you can't use 'foo as bar' and later use 'bar = 1' in one query, it's in fact not a bug but my fault.

Workaround for me was to just do the duration calculation in my 'WHERE' clause, which also makes proper SQL. Thanks once again for the great work and sorry for the trouble I've been causing ;)

@mislav
Owner
mislav commented Sep 27, 2009

No prob

This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment