Conrad, this is why: I have a Story model which works something like
'first_object_type first_object_id did some_interesting_thing to
second_object_type second_object_id'.
Users have many :groups and :contacts (other Users). Both Users and
Groups have many :stories_as_first_object
and :stories_as_second_object.
I want to get all the stories that might interest a User. At the
moment I do this:
class User < ActiveRecord::Base
def relevant_stories
s =
s += stories_as_first_object
s += stories_as_second_object
contacts.each { |contact|
s += contact.stories_as_first_object
s += contact.stories_as_second_object
}
groups.each { |group|
s += group.stories_as_first_object
s += group.stories_as_second_object
}
s.uniq.sort_by { |story| story.created_at }.reverse
end
end
...which works fine but issues a lot of queries. I would prefer to do
something like this:
So really what I want is a nice way of performing UNIONs in AR. But I
can't find anything to that effect. If you're wondering why I can't
just write the full query myself, it is that I have presented a
simplified example - in reality I have many more (and more complex)
associations so that writing the full query would be a pain, not to
mention not very DRY.
A while ago, I had a similar need. I wanted to get the sql back but
wanted it to execute as well. I came up with this:
# get the query back from ActiveRecord
class ActiveRecord::Base
def self.find_by_and_return_sql(sql)
sanitized_sql = sanitize_sql(sql)
return find_by_sql(sanitized_sql), sanitized_sql
end
end
This is just for find_by_sql, but you might be able to adapt it for the
other finders.
Just put it in a file that gets loaded. I have a custom_extensions.rb in
lib that I use for stuff like this.
Phillip, I don't think this is quite the same thing... am I right that
in your function I still have to provide the sql statement myself?
Rather, I am looking for a way of getting the sql generated by an
ActiveRecord statement..
Do something like this, but not so hackish, and that takes into
account associations and joins...
config/environment.rb:
class ActiveRecord::Base
def self.return_sql(options)
construct_finder_sql(options)
end
end
Then...
$ ./script/runner "puts Occupation.return_sql(:conditions => 'id IN
(1,2,3)', :order => 'title')"
SELECT * FROM "occupations" WHERE (id IN (1,2,3)) ORDER BY title
Take a look at rails/activerecord/lib/active_record/base.rb line 1427
"def find_every" and just follow it through. Also line 558 "def
construct_finder_sql(options)" is relevant.
Phillip, I don't think this is quite the same thing... am I right that
in your function I still have to provide the sql statement myself?
Rather, I am looking for a way of getting the sql generated by an
ActiveRecord statement..
On Sep 17, 11:45�pm, Phillip Koebbe <rails-mailing-l...@andreas-s.net>
Well, sort of. As the name implies, I created it as a replacement for
find_by_sql. When I went back to find a use case, I couldn't. So I
apparently never actually used it. But if I remember correctly, it was
for situations in which I created the base SQL statement myself but
iterated over user input to create WHERE conditions. I didn't want to
have to build custom WHERE clauses myself when Rails does a perfectly
good job of it. The fact that I used it with find_by_sql is why I said
that you might be able to adapt it. I was thinking that if you saw how
easy it was to use the sanitize_sql method and return the result, that
maybe you'd have a light bulb go off. But as I looked at the other
Philip's solution, that construct_finder_sql approach looks pretty
promising. If I ever need to do this again, I'm going to look into that
route.
Actually Phillip, having looked at it carefully, your solution is an
excellent one - it works with no modification for association
collections. I settled on this:
class ActiveRecord::Base
def self.to_sql(options={})
construct_finder_sql(options)
end
end
User.to_sql
=> "SELECT * FROM `users` "
User.to_sql(:conditions => {:id => 1})
=> "SELECT * FROM `users` WHERE (`users`.`id` = 1) "
User.find(1).groups.to_sql
=> "SELECT `groups`.* FROM `groups` INNER JOIN memberships ON
groups.id = memberships.group_id WHERE ((`memberships`.user_id =
1)) "
class ActiveRecord::Base
def self.to_sql(options={})
scope = scope(:find)
if scope and scope[:conditions].is_a?(String) and
scope[:conditions].starts_with?('SELECT')
# this is actually finder_sql, not a conditions string! Rails
bugfix?
scope[:conditions]
else
construct_finder_sql(options)
end
end
end