This is just a PSA just in case it can save somebody else the tedium
of sorting it out for themself.
Quick version:
If block_sql is a subquery, this works on sqlite3 (dev) but not
postgresql (Heroku)
where("contacts.id NOT IN (?)",block_sql)
This is because single quotes are added around the subquery and
postgresql sees it as a string. The quotes don't seem to bother
sqlite3 (though one could argue they should).
where("contacts.id NOT IN (#{block_sql})")
works with either. (But you'll have to screen the block_sql yourself
to avoid sql injections)
Backstory:
using ideas from:
http://stackoverflow.com/questions/1021018/complex-rails-query-unions-sub-select-can-i-still-used-named-scope
in Rails 2 I had
named_scope :excluding_sql, lambda { |sql| {:conditions =>
"contacts.id NOT IN (#{sql})"}}
def usable_as_alt(check_district)
contacts.usable.excluding_sql([bad scope chain
here].send(:construct_finder_sql,{:select => 'contacts.id'}))
end
Rails 3 cleaned this up nicely, leaving
def self.excluding_sql(block_sql)
where("contacts.id NOT IN (?)",block_sql) #doesn't work with
postgresql/heroku
end
def usable_as_alt(check_district)
contacts.usable.excluding_sql([scope chain].to_sql)
end
But when I deployed to Heroku I got 'invalid input syntax for integer'
for the reason detailed above.
--
You received this message because you are subscribed to the Google Groups
"Heroku" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/heroku?hl=en.
