How to make my custom mySQL query in rails 3?

Question

Im trying to display recently added comments from tattoos posted by the user. So, if I posted a tattoo and then user_b sent “hey, I like your tattoo”, then I am trying to get only a comment.

First of all, I use the action_as_commentable_with_threading gem, which does not create a foreign key for the im table trying to join. Therefore, my controller cannot search for tattoo_id, it must search for commentable_id

In the controller, I would have to call the Comment model and then pass some SQL materials to it, but apparently I don’t know how to pass custom SQL queries to ruby, because even my query string works in the terminal, I get all kinds of nonsense when trying to use it on rails.

Im basically trying to do this:

SELECT comments.id FROM comments,tattoos WHERE commentable_id = tattoos.id AND tattoos.member_id = #{current_user} 

where # {current_user} will be passed to current_user.

Answer 1

I think Ben's approach is best, but for future reference, if you come across something more complex, you can always use sql, for example:

 Comment.find_by_sql("SELECT comments.* FROM comments,tattoos WHERE commentable_id = tattoos.id AND tattoos.member_id = ?", current_user) 

Answer 2

You do not need to jump through so many hoops to accomplish this. acts_as_commentable assigns a polymorphic association, so you should configure it like this:

 class Comment < ActiveRecord::Base belongs_to :user belongs_to :commentable, :polymorphic => true end class Tattoo < ActiveRecord::Base has_many :comments, :as => :commentable end class User has_many comments end 

Then you can access the association as usual:

 Tattoo.where(:member_id => current_user).first.comments 

See http://railscasts.com/episodes/154-polymorphic-association for a general tutorial on how polymorphic associations work. It so happens that this railscast uses for sure: commentary as a polymorphic example, so you should be able to follow along if you want.